7 Things You Should Be Doing in VBA

Lots of people use VBA, but not everyone uses it efficiently. Here are 7 things you should be doing when writing VBA code.

1. Use Option Explicit



I'm sure this feature gets a lot of people in trouble. Without it, you risk using undeclared or misspelled variables. These variables are typed as Object if you don't otherwise declare them.

When you enable the feature, any new modules get "Option Explicit" added at the top. This option is module-specific, so it will need to be present in every module where you want all variables to be declared. Problem is, any existing modules don't get updated. So you'll need to add it in manually.

Here's an example. Without Option Explicit, this code compiles:

Sub TestMe()
  i = 10
  MsgBox "The value of i is " & i
End Sub

As expected, the output of this procedure is "The value of i is 10".



Now here's the same procedure, except I've typed a lowercase L instead of the letter i.

Sub TestMe()
  i = 10
  MsgBox "The value of i is " & l
End Sub

The output of this procedure is "The value of i is ".



The error will be hard to spot because the code compiles without error (and because, in this font, the letter i looks like a lowercase L). You have to visually scan your code, sometimes very slowly, to determine the cause. If you have dozens or hundreds of lines of code spread across several modules, you'll go nuts looking for it. Then again, if you have that much code, you probably already know to use Option Explicit.

2. Limit user input



Let's say you have a user-defined function that takes a day of the week and returns a value depending on which day was passed to the function.

Function GetValue(inputValue As String) As Long
' inputValue must be a day of the week
  Select Case inputValue
    Case "Sunday"
      GetValue = 10
    Case "Monday"
      GetValue = 15
    Case "Tuesday"
      GetValue = 30
    Case "Wednesday"
      GetValue = 35
    Case "Thursday"
      GetValue = 40
    Case "Friday"
      GetValue = 45
    Case "Saturday"
      GetValue = 50
End Function

But what if someone passes a different value? Or no value at all?

As a programmer you want to have control over how a program executes, and the possible values available to a function. Since the days of the week are a known range and unlikely to change, I suggest an alternative process:

1. Write an Enum section to lay out the possible values for a variable
2. Write a function to take the Enum and convert it to a String or whatever type you need
3. Declare your main function to use the Enum instead of a generic type

Here's the Enum:

Public Enum DayOfWeek
  Sunday
  Monday
  Tuesday
  Wednesday
  Thursday
  Friday
  Saturday
End Enum

Here's the function that takes the enumeration and converts it to its String equivalent:

Function GetDayOfWeek(DOW As DayOfWeek) As String
  Select Case DOW
    Case 0 ' Sunday
      GetDayOfWeek = "Sunday"
    Case 1 ' Monday
      GetDayOfWeek = "Monday"
    Case 2 ' Tuesday
      GetDayOfWeek = "Tuesday"
    Case 3 ' Wednesday
      GetDayOfWeek = "Wednesday"
    Case 4 ' Thursday
      GetDayOfWeek = "Thursday"
    Case 5 ' Friday
      GetDayOfWeek = "Friday"
    Case 6 ' Saturday
      GetDayOfWeek = "Saturday"
  End Select
End Function

And here's how we (re-)declare the GetValue function:

Function GetValue(inputValue As DayOfWeek) As Long
' inputValue must be a day of the week
Dim tempValue As String

  ' convert to string
  tempValue = GetDayOfWeek(inputValue)

  Select Case tempValue
    Case "Sunday"
      GetValue = 10
    Case "Monday"
      GetValue = 15
    Case "Tuesday"
      GetValue = 30
    Case "Wednesday"
      GetValue = 35
    Case "Thursday"
      GetValue = 40
    Case "Friday"
      GetValue = 45
    Case "Saturday"
      GetValue = 50
End Function

We haven't shortened the amount of code we use; if anything, we used more, and the GetValue function is nearly identical. But now we can force our users to choose from only valid options, and get Intellisense to boot! Note that this approach only works when the range of input values is limited and known. If you are working with a very large set of numbers, or an API that can accept any length of text input, you may not be able to use this technique.

You can see this process in numerous places throughout this site, for example

• Customize the Task Pane
• Translation services using Bing API
• Get currency conversion rates with web services

3. Turn off screen updating



But only if you are interacting with the worksheet. See the speed difference in this article: Trimming cells, before and after.

4. Use VBA arrays

Also in Trimming cells, before and after, I posted a method for quickly trimming worksheet cells. This method uses arrays to minimize the contact between Excel and VBA.

Sub Trim_Cells_Array_Method()

Dim arrData() As Variant
Dim arrReturnData() As Variant
Dim rng As Excel.Range
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long

  lRows = Selection.Rows.count
  lCols = Selection.Columns.count

  ReDim arrData(1 To lRows, 1 To lCols)
  ReDim arrReturnData(1 To lRows, 1 To lCols)

  Set rng = Selection
  arrData = rng.value

  For j = 1 To lCols
    For i = 1 To lRows
      arrReturnData(i, j) = Trim(arrData(i, j))
    Next i
  Next j

  rng.value = arrReturnData

  Set rng = Nothing
End Sub

You could also use a simple procedure like this:

Sub Trim_Cells()

Dim cell As Excel.Range

For Each cell In Selection
  cell.Value = Trim(cell.Value)
Next cell

End Sub

While you could minimize the impact by turning off screen updating, you're still touching the worksheet once for each cell. It's inefficient and a bad habit to develop. So if you have a block of 500 by 500 cells, VBA has to interact with Excel 250,000 times! Using the array method, the worksheet is only touched twice — once to read the cells into the array, and again to write the new values back to the worksheet.

5. Typecast your variables

Declaring your variables as Variant (which is what you are doing when you write Dim myVarb instead of Dim myVarb As Long) is quick, but lazy. It uses more memory, and robs you of the ability to use Intellisense. It also lets you get away with not having control over your code.

Take the time to figure out which variable type you need, and declare your variables appropriately. It forces you to understand what your code is capable of, what it can't do, and what should happen if an error occurs.

6. Take advantage of VBA's built-in features

I'll admit, I've reinvented the wheel many times. Have you forgotten these functions?

• Split (see SpreadsheetPage and elsewhere)
• Join (see here)
• LBound/UBound

Instead of hardcoding the upper and lower bounds of an array, I use LBound and UBound. That way, I don't have to remember if my array starts at 0 or 1, or how many elements it contains.

7. Organize your code

This means: put all your variable declarations at the top, and your error handling code at the bottom. (I'm guilty of not always doing this.) Choose a variable naming scheme that make sense and apply it consistently.

Got any more? Share yours in the comments.