Naming Variables in VBA

[tcb]

1) When dimensioning variables in VB, is there a best practice as to
length of variable name? I tend to use long/descriptive names (see
below).

2) Is there a better way to empty variables than what I am doing below
(strVariable = "")? Is there a function to clear them all without
referring to them individually? My variables are delcared in
Declarations because I use them in many of the form's sub routines.

Option Compare Database
Option Explicit

Dim varItem As Variant
Dim booItemYes As Boolean
Dim dbAppendQueryTo As Database
Dim strSELECT As String
Dim strSELECTb As String
Dim strFROM As String
Dim strFROMb As String
Dim strWhere As String
Dim strWhereb As String
Dim strORDERBY As String
Dim strQueryName As String
Dim strSQL As String
Dim strSQLb As String
Dim strWhere1 As String
Dim strWhereSemYearEnt As String
Dim strWhereCurrentStatus As String
Dim strWhereDegreeDesired As String
Dim strWhereConcentration As String
Dim strWhereJointDegree As String
Dim strWhereEthnicity As String
Dim strWhereDegreeTracking As String
Dim strWhereDegProgStatus As String
Dim strWhereState As String
Dim strWhereDeceased As String
Dim strWhereDoNotContact As String
Dim strWhereINTL As String
Dim strWhereMinority As String
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.


Private Sub doCreateQuery()

strWhere = ""
strWhereb = ""
strFROM = ""
strSELECT = ""
strSELECTb = ""
strSQL = ""
strFROMb = ""
strSQLb = ""
strQueryName = ""
strWhereSemYearEnt = ""
strWhereCurrentStatus = ""
strWhereDegreeTracking = ""
strWhereDegProgStatus = ""
strWhereDegreeDesired = ""
strWhereConcentration = ""
strWhereJointDegree = ""
strWhereEthnicity = ""
strWhereState = ""
strWhereDeceased = ""
strWhereDoNotContact = ""
strWhereINTL = ""
strWhereMinority = ""
strORDERBY = ""

'then create query

 

[JK]

1) No, within reason, so long as it make sense to you when yoy are debugging
2) When You declare a variable as a (something) the default value is
already there, i.e
variant=Null
string=""
number/date=0
Boolean=False

Regards/JK

 

[Allen Browne]

Readable variable names make perfect sense.

As JK says, there is no need to initialize them (though variants initialize
to Empty, not Null.)

What does bother me is all those module-level variables, or even public
global variables if this is a standar module. Really bad idea. You will have
variables interfering with each other, and you will need to re-initialize
them each time as they have long lifetimes. It would be a *much* better idea
to declare them in each procedure that needs them, and pass them to child
proceedures when you need that.

There are very few variables that need to be module level, and even fewer
that need to be global. The database I'm working on at the moment has
thousands of variables across the routines in 50 modules, and only 4 of them
are public variables.

 

[tcb]

You are right, I looked at my code again. Most of these could be
declared directly within procedures. Thanks.

 

[tcb]

I read your advice again and have an additional question. How do you
pass a variable on to a child procedure?

 

[Allen Browne]

This simple example shows how to pass 2 variables from Main() to SumThem():

Function Main() As Long
Dim A as Long
Dim B as Long
Dim C as Long
A = 1
B = 2
C = SumThem(A, B)
Main = C
End Function

Function SumThem(A As Long, B As Long) As Long
SumThem = A + B
End Function

Of course, you don't need a child function to do that, but it illustrates
the technique.

This page has a more useful example of how to pass variables to an error
logger. The child function receives the error number, the error description,
the name of the calling procedure, optional paramters you may want to log,
and a flag to tell it whether to show the error message to the user or not:
Error Handling in VBA
at:
http://allenbrowne.com/ser-23a.html