An extensive excel vba documentation for creating large-scale apps without a total mess

00_overview.md

Excel VBA

Many times you want to fiddle around with amazing tech like React or RustLang that are pushing the boundaries of what is possible. Back to the real world, there are still cases where an Excel sheet is more appropiate. Especially when companies previously built around it and have existing legacy code. Maybe the end user simply wants just one file to send per mail or move around. For this, Excel is still a viable option.

So for those people that are struggling with the limited vba language, this documentation aims to improve just a little bit of the developer experience while creating excel applications.

This may also partially apply to other office suite applications.

Feel free to send pull request with your Excel code that you found helpful or documentation additions.

Tips

' Define variables  
Dim intNum As Integer

' Set variables
intNum = 21

' Business logic
render()

In each module or sheet there there is a header. @name improves orientation when browsing modules as the vba editor does not clearly highlight the current module. Option Explicit helps for stricter code and better error messages.

'
' @name Overview
'

Option Explicit

Naming Conventions

I personally hate naming conventions but you can't get around it with VBA. VBA has a very strict type system so it helps when creating lots of functions

These are prefixes and are used like [prefix]Name.

' Example
Dim intNum As Integer
Dim colUsers As Collection
' ...

GUI

• Checkbox chk
• Button cmd
• TextInput txt
• Form frm
• Image img
• Label lbl

These must be strictly followed. More

Variables in Code

For small functions you can use only the prefix for a variable name.

• Integer int
• Double dbl
• String str
• Object obj
• Boolean bln
• Collection col
• Currency cur
• Date dtm
• Error err
• Range rng

These must be strictly followed. More

General

• Use Option Explicit in each file for better errors
• Use 2 spaces for indentation
• Write functions/subs (except eventHandlers), OLEObject identifiers camelCase
• Only pass arguments to a functions that are needed. For Example use tbl.Range instead of just tbl. This makes functions less coupled
• Write modules PascalCase

Application

• Use Application.ScreenUpdating to prevent flickering, -> 05_render.vb

Sheets

• Always get a sheet by their codename, -> 09_getWorksheetFromCodeName.vb
• Never use ActiveSheet , always reference the real name or access it through a Common module

Data Types

• Get the type of a variable with TypeName(variable)
• Use Currency data type for numbers longer than 10 digits (useful for like byte to mbytes conversion)

Debugging

• Log via Debug.Print to immediate window
• You can also evaluate arbitrary code via ? [code] and then pressing enter

Application Structure

The Common Module

It is advised to have a common module for table and sheet acessors.

Public mainSheet As Worksheet
Public tblServers As ListObject
Public rowHeight As Integer

Public Function init()
  ' Sheets
  Set mainSheet = utils.getWorksheetFromCodeName("oleOverview")
  
  ' Tables
  Set tblServers = mainSheet.ListObjects("tblServers")
  
  ' Styles
  rowHeight = 30
End Function

The Style Module

In the style module there are small helpers that are used for a consistent styling throughout your application.

Public Sub setFontFamily(rng As Range, family As String)
  rng.Font.name = family
End Sub

Public Sub setFontSize(rng As Range, size As Integer)
  rng.Font.size = size
End Sub

Public Sub setRowHeight(rng As Range, height As Integer)
  rng.rowHeight = height
End Sub

Public Sub setRowAlignment(rng As Range, alignment As Integer)
  rng.VerticalAlignment = alignment
End Sub

' etc ...

The Fs Module

When doing lots of file transformations and analysis, create a fs module. A lot of those snippets are standalone down below.

Function getTotalSize(fso As Object, nt As String)
  Dim drive As Object
  Dim pathspec As Variant
  
  Set drive = fso.GetDrive(nt)
  getTotalSize = drive.totalSize
End Function

Function rootify(path As String)
  rootify = "\\" + path
End Function

' etc...

Sheets

In sheets there should only ever be event handlers and an init() function used for bootstrapping. Delegate actual business logic into a seperate module.

In this example I declare the sheet oleOverview and the module Overview

'
' @name: oleOverview
'

Private Sub Worksheet_Activate()
  init
End Sub

Sub cmdRefresh_Click()
  Dim tbl As ListObject
  Set tbl = Common.tblName
  ' reference to the external module for actually doing stuff
  Overview.render tbl
End Sub

Sub init()
  Dim tbl As ListObject
  
  Set tbl = Common.tblName
  
  Overview.initStyles tbl
End Sub

Modules

Each module has an initStyles() function which can be used to set styling

Public Sub initStyles(rng As Range)
  Style.setFontFamily rng, "Arial"
  Style.setFontSize rng, 8
  Style.setRowHeight rng, Common.rowHeight
  Style.setRowAlignment rng, xlCenter
End Sub

01_docs.md

Docs

Filesystem API

• API Reference

Create a new instance

Set Fs = CreateObject("Scripting.FileSystemObject")

02_sheetExists.vb

'
' Check if a given sheet exists
'

Public Function sheetExists(SheetName As String, Optional wb As Excel.Workbook)
   Dim s As Excel.Worksheet
   If wb Is Nothing Then Set wb = ThisWorkbook
   On Error Resume Next
   Set s = wb.Sheets(SheetName)
   On Error GoTo 0
   sheetExists = Not s Is Nothing
End Function

03_collectionToArray.vb

'
' Convert a `Collection` to an `Array`
'

Public Function toArray(col As Collection)
  Dim Arr() As Variant
  ReDim Arr(1 To col.Count) As Variant
  For i = 1 To col.Count
      Arr(i) = col(i)
  Next
  toArray = Arr
End Function

04_hexToLongRGB.vb

'
' Convert hexadecimal colors to the wierd excel color thing
'
' @Example
'   #000000 -> &H80000012&
'

Public Function hexToLongRGB(sHexVal As String) As Long
    Dim lRed As Long
    Dim lGreen As Long
    Dim lBlue As Long

    lRed = CLng("&H" & Left$(sHexVal, 2))
    lGreen = CLng("&H" & Mid$(sHexVal, 3, 2))
    lBlue = CLng("&H" & Right$(sHexVal, 2))

    HexToLongRGB = RGB(lRed, lGreen, lBlue)
End Function

05_render.vb

'
' Stops or starts updating the application screen. When doing lots of
' sheet manipulation, disable rendering to prevent flickering
'

Public Function render (b As Boolean)
    Application.screenUpdating = b
End Function

06_formatBytes.vb

'
' Formats bytes to a human-readable form.
'
' @Example
'   `2101388760` -> 1.96 GB
'

Function formatBytes(bytes As Currency) As String
  If bytes >= 1073741824 Then
      formatBytes = Format(bytes / 1024 / 1024 / 1024, "#0.00") & " GB"
  ElseIf bytes >= 1048576 Then
      formatBytes = Format(bytes / 1024 / 1024, "#0.00") & " MB"
  ElseIf bytes >= 1024 Then
      formatBytes = Format(bytes / 1024, "#0.00") & " KB"
  ElseIf bytes < 1024 Then
      formatBytes = Fix(bytes) & " Bytes"
  End If
End Function

07_getDirectorySize.vb

'
' Get the size of a directory
'
' @param fso:Object [CreateObject("Scripting.FileSystemObject")]
' @param dir:String
'

Function getDirSize(fso As Object, ByVal dir As String) As Double
  Dim fsofolder As Object
  getDirSize = 0
  
  If fso.folderExists(dir) Then
    Set fsofolder = fso.getFolder(dir)
    getDirSize = fsofolder.Size
  End If
End Function

08_getNextFreeDriveLetter.vb

'
' Get the next free drive letter
'
' @param fso:Object [CreateObject("Scripting.FileSystemObject")]
'

Function getNextFreeDriveLetter(fso As Object)
  Dim i As Integer
  Dim drive As String
  
  For i = 67 To 90
    drive = Chr(i) & ":"
  
    If Not fso.folderExists(drive & "\") Then
      getNextFreeDriveLetter = drive
      Exit Function
    End If
  Next
  
  getNextFreeDriveLetter = "No Drive Found"
End Function

09_getWorksheetFromCodeName.vb

'
' Get a worksheet from the codename
'

Function getWorksheetFromCodeName(CodeName As String) As Worksheet
  Dim WS As Worksheet
  For Each WS In ThisWorkbook.Worksheets
    If StrComp(WS.CodeName, CodeName, vbTextCompare) = 0 Then
      Set GetWorksheetFromCodeName = WS
      Exit Function
    End If
  Next WS
End Function

10_openDialog.vb

'
' Open a dialog, centered on the screen.
'

Public Function openDialog(ByRef frm)
    With frm
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With

    openDialog = frm
End Function

11_unselectAll.vb

'
' Excel always has something selected. To get around that and have
' nothing selected, use this sub which gets around that by selecting
' a cell that is far away.
'

Public Sub unselectAll(sheet As Worksheet)
  If ActiveSheet.name = sheet.Name Then
    sheet.Range("BB100").Select
    ActiveWindow.SmallScroll up:=100
    ActiveWindow.SmallScroll ToLeft:=44
  End If
End Sub