Correct declaration of variables

  • Thread starter Thread starter marko
  • Start date Start date
M

marko

Hi all,
Just a quick few Q's about good VBA programming practice.
When creating a vba function in a module that will be called repeatedly by a
macro, do the variable declarations go before the start of function or is it
better to declare them inside? Does it matter?
Are they created and destroyed everytime the function is run?
Is it good practice to set the variables to "" at the end of the function or
macro?

Marko
 
Marko,

It certainly does matter.

A variable declared outside of a function is available to every procedure in
that module (and if it is declared as Public, it can even be accessed from
outside of the module).

A variable declared inside a function is only available to that function.

Consider this code

Dim var1 As Integer


Sub Test1()
Dim var1 As Integer

var1 = 17
MsgBox "Test1 returns - " & var1

End Sub

Sub Test2()

MsgBox "Test2 returns - " & var1

End Sub

Run Test1 and the Msgbox declares a value of 17. Then run Test2. The MsgBox
returns 0, not 17.

This is what is happening. A module level variable called 'var1' is first
declared. When you run Test1, it declares a procedure level variable called
'var1' and sets its value to 17. MsgBox displays this, but the variable is
cleared upon exiting Test1. Then when you run Test2, it picks up the module
variable 'var1', which has not been set, so it displays the value 0.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top