Some advice from the forum about declaring Public variables

[JeffGrant]

Hi All,

my work book is some 45 sheets deep, 16 vba modules and some 20 odd userforms.

To clean up a lot of rubbish code and in attempt to avoid errors, I have created one module where all Public variables are declared in the same place. This has made life considerably easier to track because I dont have too many Module level or Sub level dim statements.

I have noticed a slowing in performance in doing this though.

In your opinion, is having variables listed in one spot like this a good programming practice?

I have also enlisted the help of the M-Z Tools application latetly, and that has surely cleaned up a lot of spagetti code mess.

Are there any adverse opionion to this application be fore I purchase it. I must admit that I do like what I have seen and used with it.

Thanks

 

[RoryA]

is having variables listed in one spot like this a good programming practice?

No, quite the opposite. Public variables should be used only when you have no other option. Variable scope should be the smallest possible.

 

[JeffGrant]

Hi Rory, many of my variables go from user forms to modules, hence why they are declared as Public.

Is there a better way to do this?

Also, because many variables across modules, would they better declared as Dims in a declaration area and loose the Public declaration?

Thanks

 

[RoryA]

Where possible, variables should be local to a routine, and passed between routines as arguments if necessary. If that's not possible, they should be given module-level scope. Only use Public when you have no other choice.

 

[cmowla]

I'm not sure how often these variables are used. If they are just used to configure userforms, for example (will be accessed/changed just once in running a subroutine), then you could (programmatically) assign named ranges to an xlVeryHidden worksheet for retrieval/updating. (That's what I did for my userforms. I have over 150 such variables! And it's hidden from the user.) Then you can call those named ranges from your UserForms by a meaningful name, not a cell address.

(And after you programmatically create all of these named ranges, you can programmatically hide them from the user's view so that they don't show up in the Name Manager under the Formula Ribbon.)

With them all in a spreadsheet, you can organize them in a meaningful way to keep track of all of them too, etc.