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

  2. 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".

    correct variable

    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 ".

    wrong variable

    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.

  3. Limit user input

  4. phone

    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

  5. Turn off screen updating

  6. off switch

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

  7. Use VBA arrays

  8. 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.

  9. Typecast your variables

  10. 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.

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

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

    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.

  13. Organize your code

  14. 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.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 8 Comment(s) on 7 Things You Should Be Doing in VBA:

  1. Here's one I've been trying to implement: "Don't use any literal strings or numbers other than 0 and 1 – make them constants"

    It's more cumbersome than I thought it would be. Particularly when I'm filling an array.

  2. Nice post, JP. Personaly, I should be using Enum and Arrays more, I'd have to admit.

    Another thing I really focus on is marking my routines/functions as Public vs Private. You could argue that it's only necessary when you want them private, but I find that it forces me to think about how it's going to be used, and design it appropriately.

  3. 2 things to consider in vba:

    Application.Volatile.
    Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

    Turn off/on automatic calculation.

  4. @ Dick, Ken, Oscar –

    Thanks guys for your comments!

  5. Also, use comments liberally.
    I picked up the idea of using Title Blocks from Charlie Kyd at exceluser.com.
    (See the complete article here.)
    http://www.exceluser.com/explore/vbastds.htm

    Somebody said that well written code is self documenting, but when you go back to something you've written six months ago, the biggest problem is not working out what the code does, but working out how and why it fits with everything else. That's where Title Blocks and comments save the day.

  6. Django Dunn writes:

    Find tools that will help you with good coding practices.

    When I found MX Tools my productivity with VBA (and VB) improved. Adding error trapping with a single click is fantastic. Plus it will add a header comment block as per Johnno's comment.

  7. Your example of point #2 doesn't do anything. You haven't forced anyone to use a valid value, you have only provided a suggested list of correct values via intellisense. VBA treats Enums as the Long data type, so a user can still pass the function an invalid value. e.g. GetValue(1236)

    On top of that all the extra code in your 'fixed' example is completely unnecessary. Your example GetValue function doesn't care or need to know about the name of the day of the week. The code would be faster if written it like this.

    Function GetValue(inputValue As DayOfWeek) 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
        Case Else
          ' Handle invalid input here. Raise an error, return zero, or something.
      End Select
    End Function

    It is true that you might still want a GetDayOfWeek function to convert your Enum to understandable text, but use the Enum values in your Select Case, not literal numbers.

    Function GetDayOfWeek(DOW As DayOfWeek) As String
      Select Case DOW
        Case Sunday
         GetDayOfWeek = "Sunday"
        Case Monday
         GetDayOfWeek = "Monday"
        Case Tuesday
         GetDayOfWeek = "Tuesday"
        Case Wednesday
         GetDayOfWeek = "Wednesday"
        Case Thursday
         GetDayOfWeek = "Thursday"
        Case Friday
         GetDayOfWeek = "Friday"
        Case Saturday
         GetDayOfWeek = "Saturday"
      End Select
    End Function
    

    This has the benefit that if the whole world decided that there should 8 days a week, you could add the new day 'Foo' right after Wednesday without having to re-write the Thursday thru Saturday cases.

    Django Dun, I couldn't find anything about MX Tools, did you perhaps mean MZ Tools? It looks very helpfull. I can't wait to try it out.

    There isn't a preview button, so hopefully my comment if formatted correctly. :-)

    • You're right — I haven't implemented proper error handling. My code only prevents calling the function without passing a value, which in actuality is trivial because it is independent of the actual nature of the parameters. The conversion to a String is unnecessary. I like your implementation better.

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel