VBA Style Guide()

A mostly reasonable approach to VBA.

Note: I will be adding to it as I can!

Table of Contents

1. Naming Conventions
2. Variables
3. Functions
4. Comments
5. Performance
6. Design

Naming Conventions

• 1.1 Avoid single letter names. Be descriptive with your naming.

  ' bad
  Public Function Q ()
      Dim i as Long
      ' ...
  End Function
  
  ' good
  Public Function QueryMySQL ()
      Dim recordIndex as Long
      ' ...
  End Function

• 1.2 Use PascalCase as the default naming convention for anything global.

  ' good
  Public Function GreetUser ()
      ' ...
  End Function

• 1.3 Use camelCase for parameters and local variables and functions.

  Microsofts convention is PascalCase for everything. The most important thing is to be consistent in whatever convention you use.

  ' good
  Private Function sayName (ByVal name as string)
      ' ...
  End Function

• 1.4 Do not use underscore case.

  Why? VBA uses underscores for pointing out events and implementation. In fact, you can't implement another class if the other class has any public methods or properties with an underscore in the name otherwise you will get the error Bad interface for Implements: method has underscore in name.

  ' bad
  Dim first_name as String
  
  ' good
  Dim firstName as String

• 1.5 Do not use Systems Hungarian.

  Why? These are useless prefixes that serve no purpose and can obscure the variables name.

  ' very bad
  Dim strString as String
  Dim oRange as Range
  
  ' bad
  Dim sName as String
  Dim rngData as Range
  Dim iCount as Integer
  
  ' good
  Dim firstName as String
  Dim queryData as Range
  Dim rowIndex as Integer

• 1.6 Do not use abbreviations.

  ' bad
  Public Function GetWin()
      ' ...
  End Function
  
  ' good
  Public Function GetWindow()
      ' ...
  End Function

• 1.7 Be descriptive and use easily readable identifier names. Programming is more about reading code!

  ' very bad
  Dim x As Boolean
  
  ' bad
  Dim scrollableX As Boolean
  
  ' good
  Dim canScrollHorizontally As Boolean

⬆ back to top

Variables

• 2.1 Declare and assign variables next to where they are going to be used, but place them in a reasonable place.

Why? This makes maintaining the code much easier. When you have a wall of declarations at the top of a procedure it is difficult to modify and refactor if needed. Also, you have to scroll up and down to see if a variable is used or not.

  ' bad
  Private Sub someMethod(ByVal path As String)
      Dim fileSystem As Object
      Dim folder As Object
      Dim files As Object
      Dim file As Object
      
      Set FSO = CreateObject("Scripting.FileSystemObject")
      Set folder = FSO.GetFolder(path)
      Set files = folder.Files

      For Each file In files
          '...
      Next
  End Sub

  ' good
  Private Sub someMethod(ByVal path As String)
      Dim FSO As Object
      Set FSO = CreateObject("Scripting.FileSystemObject")
      
      Dim folder As Object
      Set folder = FSO.GetFolder(path)
      
      Dim files As Object
      Set files = folder.Files
      
      Dim file As Object
      For Each file In files
          '...
      Next
  End Sub

• 2.2 Prefer to keep variables local using the Private keyword. We want to avoid polluting the global namespace. Captain Planet warned us of that.

  ' bad
  Public Const FileName as string = "C:\"
  
  ' good
  Private Const fileName as string = "C:\"

• 2.3 Disallow unused variables.

  Why? Variables that are declared and not used anywhere in the code are most likely an error due to incomplete refactoring. Such variables take up space in the code and can lead to confusion by readers.

  ' bad
  Dim someUnusedVariable as String
  
  ' good
  Dim message as string
  message = "I will be used!"
  Msgbox Messgae

• 2.4 Use Option Explicit to ensure all variables are explicitly declared.

  ' good
  Option Explicit
  
  Sub doSomething()
      x = 1 ' Compile error: Variable not defined
  End Sub

• 2.5 Use one Dim declaration per variable or assignment.

  Why? It's easier to read and debug going back. It also prevents variables from accidentally being declared as Variants.

  ' very bad
  Dim lastRow, lastColumn As Long ' lastRow is a Variant, NOT a long
  
  ' bad
  Dim lastRow As Long, lastColumn As Long
  
  ' good
  Dim lastRow As Long
  Dim lastColumn As Long

• 2.6 Declare all variable types explicitly.

  ' bad
  Dim row
  Dim name
  Dim cell
  
  ' good
  Dim row As Long
  Dim name As String
  Dim cell As Range

⬆ back to top

Functions

• 3.1 Prefer ByVal for parameters.

  Why? Reassigning and mutating parameters can lead to unexpected behavior and errors. ByRef is very helpful at times, but the general rule is to default to ByVal.

  ' bad
  Private Function doSomething(name As String) As String
  
  ' ok
  Private Function doSomething(ByRef outName As String) As Boolean
  
  ' good
  Private Function doSomething(ByVal name As String) As String

Comments

• 4.1 Above the function should be a simple description of what the function does. Keep it simple.

  ' Adds new element(s) to an array (at the end) and returns the new array length.
  Function PushToArray(ByRef SourceArray As Variant, ParamArray Element() As Variant) As Long
     '...
  End Function

• 4.2 Just above the function is where I will put important details. This could be the author, library references, notes, Ect. I've styled this to be similar to JSDoc documentation.

  I've decided to make this the same as JSDoc because there is no reason to recreate the wheel. This is a very known way of documenting and has plenty of documentation and examples already out there. This will keep your code consitent and easy for anyone to understand what is going on.

  '/**  
  ' * Adds new element(s) to an array (at the end) and returns the new array length.
  ' * @author Robert Todar <https://github.com/todar>
  ' * @param {Array<Variant>} SourceArray - can be either 1 or 2 dimensional array.
  ' * @param {...Variant} Element - Are the elements to be added.
  ' * @ref No Library references needed =)
  ' */
  Function PushToArray(ByRef SourceArray As Variant, ParamArray Element() As Variant) As Long
     '...
  End Function

• 4.3 Notes should be clear and full sentences. Explain anything that doesn't immediately make sense from the code.

  ' Need to check to make sure there are records to pull from.
  If rs.BOF Or rs.EOF Then
      Exit Function
  End If

• 4.4 Add a newline above a comment when the previous code is on same indention level. Otherwise, don't have a line break.

  ' bad
  Private Sub doSomething()
      
      ' Different indention from line above, no need for newline above.
      Application.ScreenUpdating = False
      ' Same indention as previous code, must add a newline above to make it easy to read.
      Application.ScreenUpdating = True
  End Sub
  
  ' ok
  Private Sub doSomething()
      ' Different indention from line above, no new line.
      Application.ScreenUpdating = False
  
      ' Same indention as previous code, add a newline above.
      Application.ScreenUpdating = True
  End Sub

• 4.5 Prefixing your comments with FIXME or TODO helps other developers quickly understand if you’re pointing out a problem that needs to be revisited, or if you’re suggesting a solution to the problem that needs to be implemented. These are different than regular comments because they are actionable. The actions are FIXME: -- need to figure this out or TODO: -- need to implement.

⬆ back to top

Performance

• 5.1 Avoid using Select in Excel. See this Stackoverflow Post.

  Why? It slows down code and also can cause runtime errors. Select should only be used for visual reasons such as the users next task is doing something in that specific cell.

  ' bad
  Range("A1").Select
  ActiveCell.Value = "Darth Vader"
  
  ' ok
  Dim cell As Range
  Set cell = ActiveSheet.ActiveCell
  cell.Value = "Lando Calrissian"
  
  ' good
  With Workbooks("Star Wars").Worksheets("Characters").Range("Hero")
      .Value = "Luke Skywalker"
  End With

⬆ back to top

Design

• Functions should be small and do only a single action (think lego blocks).
• Functions should be pure — meaning they should not mutate outside state and always return the same value when given the same inputs.
• Anytime there is a section of code that is separated by a giant comment block, ask yourself if this needs to get extracted into it's own function.

⬆ back to top