Declaring Variables with Public

I have just started on vba and at the age of 50, it is not easy :-(
But it's so exciting and I love it...... when it works.
I am not sure what i have done wrong in my kode, I have tried to make the code more simpel than first and now it dosent work.
Please can somebody have a look at it for me, it vil be much apriciated

Please Login or Register  to view this content.

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

Sorry - that still won't do. You need to give more detail in your title.

Thank you - that will do.

Hi,

Perhaps it would be best of you uploaded the workbook and explained in a narrative, with reference to sheets/cells where necessary, what the overal aim is and how you wish to interact with it. We're less interested in what your code is attempting to do so don't describe that. We can often suggest better and more efficient ways of achieving your goal.

Hi,

I think CPearson has lots of information to help with this topic. Read it at:

http://www.cpearson.com/excel/Scope.aspx

I also find MSDN a good source.

https://msdn.microsoft.com/en-us/vba...ring-variables

@Dal123, the significance of public/private is that Public variables, subs, functions are available to VBA calls from any Module, while Private ones are only available to the Module they reside in.

@nordicdust, You say the code is not working, but it is not clear to me what the actual problem is. I have never tried to use "Dim" outside of a module (right under "Public" section, you have 3 "Dim"). Depending on your issue, changing those Dim to Public COULD help...

Hi Dal

No, not my first, my 3rd workbook with macros. I have been at this for about 2-3 months.
There is at lot of help around and here, just by reading others problems and youtube is also great.

The code i put up is just a litle of it, the rest is mostly small macros, but it is in the start something is wrong, and i am sure it is with my declaring.
All sheets are locked so only the cells for input are unlocked. Workbook is also locked so the users can't ad sheets.
As most of my macros need to unlock and after lock, i have made the Public Sub "UnlockAndBoost" macro that the macros call to. There should be both unlock and lock in the macro.
I am not sure (of anything) but i think that declaring a sub with public makes it avalible for calling whithin the module as Excel dosent clear it from memory.
Again, i am not sure of anything :-)
But thanks for taking a lock :-)

Thanks Marvin & Arkadi.

Nordicdust by the way, VBA hasn't been developed in around 8 years and Microsoft have confirmed they are no longer developing it.
I found this out by another helpful member the other week but I've been looking through my previous posts and can't find it to link for your reference (apologies). So all the work we're putting in could be wasted. As I'm sure you've thought to yourself VBA can be quite a silly language sometimes and makes things really difficult for us and I've personally struggled with it's logic, sometimes it just makes no sense whatsoever !

The correct tool to solve a problem is the one that solves that problem.

Excel has survived because it has LOTS of tools built into it. Tools like: Sort, Filter, Conditional Formatting, AutoFilter, Format Cells, Formulas (hundreds of them), and many more. Excel even has old tools that might be useful like "Data Form Tool" (https://www.lifewire.com/excel-data-entry-form-3123427) which is hidden from normal use. VBA hasn't been improved for many years but is still a tool that I use, and has solved many of my problems.

The newer tool(s) in Excel come in the Data Tab and using "Get & Transform" (sometimes called Power Query) to combine large sets of data. With more data in the world, Excel is trying to keep up with tools that help solve this newer problem. Power Query is a different egg than VBA as it uses a scripting type language. I find many of my VBA tasks can be done easier using PQ, like combining all the files in a single folder into a single file. This used to be much harder using VBA.

Well put Marvin.

And on the 'old' tools front I still occasionally use the old Excel4 'macros' which I sometimes find more useful than the common or garden modern stuff we have.

Hi Richard
Thanks for your reply.
I have attached the file, I hope you can find the problem.
Hmmm i am not sure i know how to attach the file

I don't see the file.

The paperclip attach item doesn't work - no don't ask. Use the Go Advanced button on your post and then the 'Manage Attachments' option underneath the post area.

Originally Posted by Arkadi

[...]I have never tried to use "Dim" outside of a module (right under "Public" section, you have 3 "Dim"). Depending on your issue, changing those Dim to Public COULD help...

Any declarations using Dim inside a module before the first Sub or Function are global to the module and also static, but not visible outside the module. (I haven't analyzed whether such a change will help in this case.)

That is correct. You should remove that argument from the Workbook_Open code.