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.
' 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
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
' ...
- Checkbox
chk
- Button
cmd
- TextInput
txt
- Form
frm
- Image
img
- Label
lbl
These must be strictly followed. More
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
- 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 justtbl
. This makes functions less coupled - Write modules PascalCase
- Use
Application.ScreenUpdating
to prevent flickering,->
05_render.vb
- 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
- 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)
- Log via
Debug.Print
to immediate window - You can also evaluate arbitrary code via ? [code] and then pressing enter
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
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 ...
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...
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
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