VBA Style Guide()

A mostly reasonable approach to VBA.

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

Naming Conventions
Variables
Functions
Comments
Performance
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

Cheap and reliable Node.js hosting starts at $3/month, and $1/month static HTML hosting

todar / VBA-Style-Guide

Labels

Projects that are alternatives of or similar to VBA-Style-Guide

VBA Style Guide()

Table of Contents

Naming Conventions

Variables

Functions

Comments

Performance

Design