User FAQs

If you have a question, please send it to us.

1) Should I prefix my variables with a data type indicator ?
Yes. All variables should be prefixed with an abbreviation that indicates their data type.
In VBA, type conversions can be implicit, so this reduces errors and improves readability.
For a list of all the prefixes, please refer to the Data Types page.

Dim sMyString As String 
Dim lMyNumber As Long 

2) Should I initialise my variables with a default value ?
It really depends because all data types do have a default value.
Initializing a variable shortly after it is declared helps to ensure that it always has a known value and reduces the likelihood of the code failing unexpectedly.

Dim lMyNumber As Long 
lMyNumber = 0 

3) When declaring variables should I use Private ?
For global level variables use "Public"
For module level variables use "Private"
For local level variables use "Dim"

Public sText As String 
Private sText As String 
Dim sText As String 

Avoid reusing the variable for something else later in the code since this makes the code more difficult to interpret for other developers.

4) What is a Variable Declaration Character ?
When you use the Variant data type the compiler is able to determine the correct data type for a numerical literal.
However there might be times when the default data type is different to the desired one.
It is possible to append a special character to the end of a literal constant to make it explicit.

Dim myVariant1 As Variant 
Dim myVariant2 As Variant 
myVariant1 = 100 
myVariant2 = 100# 

Debug.Print VBA.TypeName(myVariant1)  'Integer  
Debug.Print VBA.TypeName(myVariant2)  'Double  

5) What is a Static Variable ?
A static variable is a local variable whose value will be preserved within the subroutine or function.
A static variable has the scope of a local variable but the lifetime of a module level variable.
A static variable is only visible in the subroutine or function where it is declared.
You should always initialise a static variable before using it.

Public Sub MySubroutine() 
   Static iStatic As Integer 
End Sub 

6) How is a Static Variable different to a Module Level Variable ?
A static variable will preserve its value but will not be visible outside the subroutine or function.
A module level variable will preserve its value and will be visible to all the other subroutines and functions in that module.

Public sOutside1 As String 
Private sOutside2 As String 

Public Function MyFunction() As Integer 
   Static iStatic As Integer 
End Sub 

7) Can you explain the difference between the Stack and the Heap ?
The stack contains value data types on a first in, first out basis.
The stack stores items on a last in first out basis
The stack is where local variables are stored.
The heap contains reference data types.
The heap is where global variables are stored

8) What is the difference between Early Binding and Late Binding ?
Early binding allows for compile-time checking and requires you to explicitly declare the data type of the object.
Late binding relies on run-time checking and uses the generic Object data type.