Best practice for managing variables

[raven_squire]

Hello,

I am basically self taught with VBA and I am making more and more complex macros. I am finding that my variables are all over the place and I was wondering what the professionals do in regards to variables.

Do you declare all variables or just the significant ones?
With the insignificant variables (ones that are just used temperarily as part of a process) do you give them meaningful names? or just use random letters of the alphabet? X or Employee_Num_Int, Employee_Num_Int_Temp
Do you reuse insignificant variables e.g. I have used many "for x = 0 to FOO" type statements, do you reuse the same variable in the next statement or create a different variable for each one?
Do you declare all the variables at the top of the module or do you declare them at the start of the block of code that first uses it?

I feel like my inconsistency is making my code hard to follow making it harder to debug and expand on. The module that I am working on now has 20 variables declared at the start of the module. I have no idea how many insignificant variables I have used and reused. Then there is the other modules that I have written that used public variables to move data from one module to the next.

Any help or links to coding guidelines would be apreciated.

Thanks Steve

 

[MARK858]

Do you declare all variables or just the significant ones?

All variables (no point declaring any if you don't).

do you give them meaningful names?

When writing properly (i.e. I get a bit loose on a forum) I use something to show it's type and it's purpose. Makes it easier to debug,

do you reuse the same variable in the next statement or create a different variable for each one?

I only use the same variable if it is doing the exact same task i.e. looping through the same range.

Do you declare all the variables at the top of the module or do you declare them at the start of the block of code that first uses it?

Top.

Most importantly be consistent so that you understand it.

I agree it will be interesting to see what the professionals (as I am distinctly amateur) think.

 

[RoryA]

I am basically self taught with VBA

Ditto, so bear that in mind with what follows!

Do you declare all variables or just the significant ones?

All of them. I concur with what Mark said - why declare some and not others? Also, I use Option Explicit all the time simply because it makes typos easier to pickup.

With the insignificant variables (ones that are just used temperarily as part of a process) do you give them meaningful names? or just use random letters of the alphabet? X or Employee_Num_Int, Employee_Num_Int_Temp

For simple loop counters/objects I tend to use meaningless names like n, v or item.

Do you reuse insignificant variables e.g. I have used many "for x = 0 to FOO" type statements, do you reuse the same variable in the next statement or create a different variable for each one?

It's probably a bad habit but I do tend to reuse them if I know I don't need the old value.

Do you declare all the variables at the top of the module or do you declare them at the start of the block of code that first uses it?

I declare at the top, but I can see the appeal of declaring closer to where you first use them. Again, as Mark said, consistency is more important than which method you adopt.

I feel like my inconsistency is making my code hard to follow making it harder to debug and expand on. The module that I am working on now has 20 variables declared at the start of the module. I have no idea how many insignificant variables I have used and reused. Then there is the other modules that I have written that used public variables to move data from one module to the next.

I recommend avoiding public variables as much as possible - pass arguments directly between procedures when you can. If you do use Public variables (or constants), I personally like to put them all in one separate module so I know exactly where to go looking for them.

You might find this old article interesting: http://dailydoseofexcel.com/archives/2013/07/02/vba-best-practices-that-ill-never-do/

 

[raven_squire]

Thanks for that guys!!

I think I might have to review my code and make it all consistent. I think I will also look into using Option Explicit

 

[MARK858]

Thanks for that guys!!

I think I might have to review my code and make it all consistent. I think I will also look into using Option Explicit

Just to say the same as Rory I always use Option Explicit, it saves a lot of hassle.

 

[RoryA]

On a related note, the first things I do to the VBE on a new install are:
1. Turn off 'Auto syntax check'
2. Turn on 'Require variable declaration
3. Change the Error trapping setting to 'Break in Class Module'