Excel VBA: General UserForm VBA Coding Practices Part 1. Excel VBA 2 Lesson 10

HOME | Back to index  <Previous | Next> Buy the complete Excel course, Including VBA & Workbook examples for only $9.95 | Ask a question?

General VBA Coding Practices

For a standalone copy of the following coding practices, open UserForms Lesson 5A 2007.htm

 Example Coding Standards for use with UserForm lessons.

1. Modules begin with Option Explicit

2. Unofficial Version Control

   1. Modules begin with the modified date in a comment

   2. Macros begin with a comment with version and modified date on the Sub declaration line if it is short

   3. Macros longer than a few lines have the macro name repeated in a comment on the End Sub line

3. Macro names begin with a lower case letter if parameters are required

4. Variables declared at the top of a module reduce passed parameters

5. Variable names preferably begin with a lower case letter and also have a number or capital letter, to help differentiate from Excel names

6. Indenting

   1. Subs, Functions, intentional code comments, and With and Loop control structures are indented 4

   2. "_" line extensions are indented 2

   3. Code statements temporarily commented out are not indented

   4. Debug.Print statements; Not are

7. Dim shorthand (helps avoid declaration and comparison problems)

   1. varName% same as varName As Integer

   2. varName& same as varName As Long

   3. varName$ same as varName As String

When coding, there are short and long term considerations.  The short term consideration is to get the project defined, coded, tested, and delivered.  Long term considerations include designing for re-use and for future code maintenance by another programmer.  While the project delivery is most important, some long term considerations can be included during normal programming without much additional effort.

All the good practices applicable to general VBA programming are also applicable to UserForm programming.  Many of these will be repeated here before covering UserForm specific guidance.

Many of the suggestions here are toward providing a consistent look to the code.  The inexperienced programmer often suffers additional debug time simply because sloppy formatting caused code that should have been inside a With, If, or Loop to end up outside it.

Dating Modules and Macros

Having debugged and solved a problem with the code, it is important to use the fixed version for ensuing work.  VBA does not have a built-in source code control mechanism, but it is still good on all but the simplest projects to have a method to identify which of many development versions are the newest.  This is true for both modules and macros.

A simple but effective tracking method is to put the edit date in a comment both at the top of each module and within each macro.  It does require discipline by the programmer to remember, when editing a module, to manually update the version dates with the first edit each day.

Exported Module Naming Convention

On larger projects where interim versions are delivered to the client for review, it is necessary to archive each delivery, to understand suggestions and problems.  While zipping and saving a copy of each workbook often is sufficient, many experienced programmers export modules into libraries of their code for future re-use.  The code they have written may be copyrighted and owned by their clients, so care needs to be taken to not to re-use copyrighted code in future projects.  The techniques and specific objects, properties, methods, and events used to accomplish a task are general and are always good for reference.

Each module can be exported to a file with File>Export File.  Standard modules export to the .bas extender, class modules to the .cls extender, and the UserForm exports to two files, .frx for the object and .frm for the code.  All but the .frx file are text files, easy to search for keywords and comments.

Since VBA defaults every module to similar names, it is important to rename each module.  A good module name describes both the project and the function within.  A module can then be exported with a file name made by the module name and the date from the comment at the top.  So the files sort easily, the date format should be yymmdd.  For example, exporting a standard module last edited on July 5, 2009:

Wrong: Module1 0975.bas, Module1 09075.bas, Module1 09705.bas

Correct : Module1 090709.bas

Exported modules may be brought into a workbook either with File>Import File or by drag-and-drop from Windows Explorer.

Option Explicit

A very difficult problem to find is the use of a variable name as two different types.  This can be minimized by putting the Option Explicit statement and the top of each module.  This forces all variables to be declared.

Variable Naming and Typing

It is useful to be able to tell if a variable is a system-wide keyword or is defined by the programmer.  System-wide keywords start with a capital, and if it is a word made up of two words, each is capitalized, as in SpinButton and ThisWorkbook.  Each programmer needs to find a comfortable naming convention and stay with it for an entire project.  One handy method is to define variables with a lower case first letter and a capital letter later in the word, such as fPath and dirName.  Longer variable names are better because they can describe its purpose.  Single letter variables such as x and y are quick to type but carry little information about purpose, and should be used sparingly, perhaps for loop and indexing variables such as:

For x = 1 To UBound(ary, 1)

    myAry(x, 1) = someValue

Next x

and   x = InStr(1, prevBase, " ")

Another place where capitalization can carry extra information is in the naming for procedures.  A useful convention is to begin subs and function names with a lower case letter and with a capital letter if none:

Function convertDate(ByVal str)

and   Sub ClearExtended()

Many problems can be avoided a variable name self-identifies what type it is.  There are a couple of different possibilities for associating a variable name with its type.  One is to prefix each variable name with the first letter of the type, such as i for integer, l for long, and s for Single.

iVarName

lVarName

sVarName

Another is to use type-declaration characters as a suffix where available.  There are six:

varName%      same as varName As Integer

varName&      same as varName As Long

varName!      same as varName As Single

varName#      same as varName As Double

varName@      same as varName As Currency

varName$      same as varName As String

Type-declaration characters can be in Dim statements in place of As Integer, As String, etc.  They can also be used throughout the code.

Dim x%

For x% = 1 To UBound(ary, 1)

    myAry(x%, 1) = someValue

Next x%

Where no type-declaration character is available, some naming abbreviation prefixes can still be used to include type in the variable name, such as:

b for As Boolean

byte for As Byte

day for As Date

obj or o for generic As Object

wkbk for As Workbook (specific object)

sht for As Worksheet (specific object)

Variant has no type-declaration character, and it is the default type, so these two Dims are the same:

Dim myVar

Dim myVar As Variant

Variable Default Values

When code execution begins, variables are assigned default values until they are set to a usable value by the code.

Declaring Variables and Constants

In addition to value and type, variables have two more important characteristics, scope, and lifetime.  Scope defines which procedures have access to the variable, and lifetime is the time between creation of the variable and when it ceases to have a value.

A variable can have scope at the project level, the module level, and the procedure level.  Procedure variables are used just within the procedure, are declared with the Dim statement, and are not visible outside the procedure.  Module level variables have the Dim statement at the top of the module.  Project variables are declared at the top of any module, with Public keyword instead of Dim.  Values for module and project level variables are retained in memory as long as code is running in any module.

A variable or constant declared within a procedure is not visible outside that procedure.  If not declared Static, its value is lost when the procedure completes, and is re-initialized the next time the procedure is called.

In addition to variables, procedures can be declared Static.  If they are, their variables are retained in memory as long as code is running in any module.

Constants can also be declared at procedure, module, and project levels.  Constants can be of any type.  Objects as constants are declared As New.

EXAMPLES

' top of module

' project level variables and constants

Public NumberOfEmployees As Integer

Public tmpWkSheet As Worksheet

Public Const spaces = "      "

' module level variables and constants

Dim arrayIndex&         ' Long

Dim fileName$           ' String

Const maxRetries = 3

Sub GetData()

    ' procedure level variables and constants

    Dim x%, y%

    Dim WorkBkToPrint As New Workbook

    Const arrayRows% = 5

    Static myArray(10)

    ' code goes here

End Sub

Dynamic and Fixed-Size Arrays

The variables discussed are all scalar variables, each capable of holding a single value.  Arrays of variables are also allowed, with any number of dimensions.  One and two dimension arrays are the most common.  The one dimension array is most commonly handled with a one-variable loop, though if it is defined as Variant, it can be easily set from a list with the Array function.  my2dimAry will have exactly the number of items in the list, with the first element numbered 0 and the last element number 1 less than the number of items.  This code will initialize an array and then print its lower and upper bounds (0, 2) in the Immediate Window.

Dim my1dimAry As Variant

my1dimAry = Array("a", 7, "7a")

Debug.Print LBound(my1dimAry), UBound(my1dimAry)

The Option Base statement controls the lower bound and defaults to 0.  Place

Option Base 1

at the top of the module, and the above code will print 1, 3 to the Immediate Window.

The two dimension array is very handy, since it directly sets and is set by a range.  The addressing inside the array is the same as in a range, where the first dimension is the row and the second dimension is the column.  An array initialized from a range will always have a lower bound of 1, even if Option Base 0 is specified.

Dim my2dimAry As Variant

my2dimAry = Range("A7:C10")

' change row 2, column 3, which corresponds to cell C8

my2dimAry(2, 3) = someOtherValue

Range("A7:C10") = my2dimAry

The above examples require a Variant array because the source list or range may have a mixture of text and numbers.  If an array is to be initialized and used some other way, then it can be identified as an array by parentheses and restricted to a single data type, as:

Dim myIntAry() As Integer

All the above array examples are dynamic arrays, where dimensions are not set at design time. The dimensions can be set by running code either by the above methods or by the ReDim statement.

ReDim myOneIntAry(3)

ReDim myTwoIntAry(3, 6)

ReDim normally causes any data in the array to be lost.  ReDim cannot change the number of dimensions or the type of the array.  The last dimension can be changed, and data can be preserved with the Preserve keyword..

ReDim Preserve myOneIntAry(4)

ReDim myTwoIntAry(3, 5)

If the array is a fixed array, the dimensions are set at design time.  A ReDim will cause an Array Already Dimensioned error as soon as execution is attempted.

Dim myIntAry(7) As Integer

The number supplied to the array Dim statement is the upper bound, so if the above statement has no Option Base 1 statement, the lower bound will be 0 and the upper bound will be 7, a total of 8 elements.  A good way to avoid confusion is to always specify both lower and upper bounds with

Dim myIntAry( 1 to 7) As Integer

Some fancy tricks are possible with this, as the following statement will have an array with lower and upper bounds of 7 and 9.

Dim myIntAry( 7 to 9) As Integer

If a use can be found for such an array, it is possible, but be sure to include good comments.

HOME | Back to index  <Previous | Next> Buy the complete Excel course, Including VBA & Workbook examples for only $9.95 | Ask a question?

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.