Ribbon buttons, ribbon logic, Outlook automation: practical routines that save real time every day. This is the macro and automation side of the practice.
BlackTor Group LtdDartmoor, UK
A note on what follows
No client names, no client data.
BlackTor's work is covered by client NDAs, so the examples below don't describe a real client, project or dataset. To keep things concrete, they're all set at "Ride Me Cycles": a fictitious multi-branch bike retailer invented for this site. The techniques are real; Ride Me Cycles and everything about it are not.
Latest update
Latest: shipped a custom ribbon tab with progressive button-enabling logic, written up as a three-part series. Also rebuilt bulk email through Classic Outlook and shared-mailbox .oft templates, after New Outlook broke COM automation.
A few examples
Three examples of automation in daily use.
Illustrative, per the note above: not real client work.
The problem: A finance assistant at Ride Me Cycles retyped the same six-branch P&L pack into an email, by hand, every month.
The approach: Built a ribbon button that walks the workbook, pulls the six branch tabs into one formatted P&L summary, and drops it straight into a pre-addressed Outlook draft.
Sub BuildMonthEndPack()
Dim wks As Worksheet
Dim wksSummary As Worksheet
Dim lngLoop1 As Long
Dim lngOutRow As Long
Dim strBody As String
Dim objOlApp As Object
Dim objOlMail As Object
Set wksSummary = ThisWorkbook.Worksheets("Month End Pack")
wksSummary.Range("A2:C100").ClearContents
lngOutRow = 2
For lngLoop1 = 1 To 6
Set wks = ThisWorkbook.Worksheets("Branch " & lngLoop1)
wksSummary.Cells(lngOutRow, "A").Value = wks.Range("B1").Value
wksSummary.Cells(lngOutRow, "B").Value = wks.Range("F20").Value
wksSummary.Cells(lngOutRow, "C").Value = wks.Range("F21").Value
lngOutRow = lngOutRow + 1
Next lngLoop1
strBody = "Month end P&L pack - " & Format(Date, "mmmm yyyy") & _
vbNewLine & vbNewLine
For lngLoop1 = 2 To lngOutRow - 1
strBody = strBody & wksSummary.Cells(lngLoop1, "A").Value & _
": revenue " & Format(wksSummary.Cells(lngLoop1, "B").Value, "#,##0") & _
", profit " & Format(wksSummary.Cells(lngLoop1, "C").Value, "#,##0") & _
vbNewLine
Next lngLoop1
Set objOlApp = CreateObject("Outlook.Application")
Set objOlMail = objOlApp.CreateItem(0)
With objOlMail
.To = "regional.manager@ridemecycles.co.uk"
.Subject = "Month End Pack - " & Format(Date, "mmmm yyyy")
.Body = strBody
.Display
End With
End Sub
The result: A fifteen-minute click replaces a morning's typing.
The problem: Ride Me Cycles' shared stock workbook kept corrupting when two branches opened it at once and both hit save.
The approach: Added a lightweight file-lock check on Workbook_Open that warns a second user before they can overwrite the first branch's stock changes.
Private Sub Workbook_Open()
Dim wksLock As Worksheet
Dim strLockedBy As String
Dim dtmLockedAt As Date
Set wksLock = ThisWorkbook.Worksheets("Lock")
strLockedBy = wksLock.Range("A1").Value
dtmLockedAt = wksLock.Range("B1").Value
If strLockedBy <> "" And strLockedBy <> Environ("USERNAME") _
And Now - dtmLockedAt < TimeSerial(0, 15, 0) Then
MsgBox strLockedBy & " opened this workbook " & _
Format((Now - dtmLockedAt) * 24 * 60, "0") & " minute(s) ago." & _
vbNewLine & "Saving now may overwrite their stock changes.", _
vbExclamation
End If
wksLock.Range("A1").Value = Environ("USERNAME")
wksLock.Range("B1").Value = Now
End Sub
The result: No more working out whose stock count just got overwritten.
The problem: An overtime-approval macro only worked if a branch manager filled in every field in exactly the right order, so half the team avoided it.
The approach: Rewrote the validation as independent getEnabled callbacks on the ribbon, so each control lights up the moment its own requirement is met, in any order.
Private Function Ribbon_getEnabled(objControl As IRibbonControl) As Boolean
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets("Overtime Approval")
Select Case objControl.ID
Case "btnApproveOvertime"
Ribbon_getEnabled = Trim(wks.Range("StaffName").Value) <> "" _
And Trim(wks.Range("HoursClaimed").Value) <> "" _
And wks.Range("ManagerSignOff").Value = True
Case "btnRejectOvertime"
Ribbon_getEnabled = Trim(wks.Range("RejectReason").Value) <> ""
Case Else
Ribbon_getEnabled = False
End Select
End Function
The result: The button nobody trusted became the button every branch manager uses.
Practical routines
Ten routines, start to finish.
The write-ups above explain the thinking; these are the routines themselves, complete and runnable, from a short one-line fix through to the array, export, file-system and email patterns that come up in almost every automation project.
Simple
Flag overdue purchase orders
Sub FlagOverduePOs()
Dim wks As Worksheet
Dim lngLastRow As Long
Dim lngLoop1 As Long
Set wks = ThisWorkbook.Worksheets("Purchase Orders")
lngLastRow = wks.Cells(wks.Rows.Count, "A").End(xlUp).Row
For lngLoop1 = 2 To lngLastRow
If wks.Cells(lngLoop1, "D").Value < Date _
And wks.Cells(lngLoop1, "E").Value = "Open" Then
wks.Cells(lngLoop1, "A").Resize(1, 5).Interior.Color = RGB(240, 200, 200)
Else
wks.Cells(lngLoop1, "A").Resize(1, 5).Interior.ColorIndex = xlNone
End If
Next lngLoop1
End Sub
One pass down the Purchase Orders sheet, comparing the due-date column against today and the status column against "Open". A row that is both overdue and still open gets a light red fill across the whole row; everything else has its fill cleared, so re-running the macro after an order is closed or paid removes the flag automatically rather than leaving a stale highlight behind.
Simple
Standardise branch codes to uppercase
Sub StandardiseBranchCodes()
Dim wks As Worksheet
Dim rngCodes As Range
Dim rngCell As Range
Set wks = ThisWorkbook.Worksheets("Branch Master")
Set rngCodes = wks.Range("B2:B" & wks.Cells(wks.Rows.Count, "B").End(xlUp).Row)
For Each rngCell In rngCodes
If Len(rngCell.Value) > 0 Then rngCell.Value = UCase(Trim(rngCell.Value))
Next rngCell
End Sub
Branch codes typed by different people arrive as bike-1, Bike-01 and BIKE1 for the same branch. The loop trims any stray leading or trailing whitespace and forces the result to uppercase, so a lookup run against the branch code afterwards matches consistently. Deliberately the simplest possible fix: run once after import, rather than folded into a bigger validation routine.
Simple
Tidy a sheet after a CSV import
Sub TidyImportedColumns()
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets("Stock Import")
wks.Cells.EntireColumn.AutoFit
wks.Rows(1).Font.Bold = True
wks.Range("A2").Select
ActiveWindow.FreezePanes = True
End Sub
A freshly imported CSV arrives with default column widths, an unbold header row, and no frozen header, three small things that make a sheet feel untidy before anyone has even looked at the data. This one macro fixes all three in one call. Freeze panes is one of the few places Select is genuinely unavoidable, since FreezePanes operates on the active window rather than on a range object, worth noting given the general advice elsewhere on this page to avoid Select where there is a direct alternative.
Complex
Consolidate each branch's daily report into one summary
Sub ConsolidateBranchReports()
Dim strFolderPath As String
Dim strFileName As String
Dim wbkSource As Workbook
Dim wksSummary As Worksheet
Dim dictTotals As Object
Dim strBranchName As String
Dim dblDailyTotal As Double
Dim lngOutRow As Long
Dim varKey As Variant
Set dictTotals = CreateObject("Scripting.Dictionary")
strFolderPath = "C:\RideMeCycles\DailyReports\"
strFileName = Dir(strFolderPath & "*.xlsx")
Application.ScreenUpdating = False
On Error GoTo CleanFail
Do While strFileName <> ""
Set wbkSource = Workbooks.Open(strFolderPath & strFileName, ReadOnly:=True)
strBranchName = wbkSource.Worksheets(1).Range("B1").Value
dblDailyTotal = wbkSource.Worksheets(1).Range("F20").Value
If dictTotals.Exists(strBranchName) Then
dictTotals(strBranchName) = dictTotals(strBranchName) + dblDailyTotal
Else
dictTotals.Add strBranchName, dblDailyTotal
End If
wbkSource.Close SaveChanges:=False
strFileName = Dir
Loop
Set wksSummary = ThisWorkbook.Worksheets("Weekly Summary")
wksSummary.Range("A2:B500").ClearContents
lngOutRow = 2
For Each varKey In dictTotals.Keys
wksSummary.Cells(lngOutRow, "A").Value = varKey
wksSummary.Cells(lngOutRow, "B").Value = dictTotals(varKey)
lngOutRow = lngOutRow + 1
Next varKey
CleanExit:
Application.ScreenUpdating = True
Exit Sub
CleanFail:
If Not wbkSource Is Nothing Then wbkSource.Close SaveChanges:=False
MsgBox "Could not consolidate " & strFileName & ": " & _
Err.Description, vbExclamation
Resume CleanExit
End Sub
Each branch emails a daily report workbook into a shared folder. The macro opens every file in that folder read-only in turn, reads the branch name and daily total off the first sheet, and accumulates the figures per branch in a Dictionary, keyed on branch name, so a branch appearing in more than one file adds to its running total rather than overwriting it. The results are written to a summary sheet in one pass once every file has been read. On Error GoTo CleanFail, paired with a CleanExit label, means one corrupt or locked file reports which file failed and still restores ScreenUpdating, rather than aborting the whole run or leaving the workbook stuck with updating switched off.
Complex
Validate a stock-take import before it touches the ledger
Sub ValidateStockTakeImport()
Dim wks As Worksheet
Dim lngLastRow As Long, lngLoop1 As Long
Dim colIssues As Collection
Dim varIssue As Variant
Dim strMsg As String
Set wks = ThisWorkbook.Worksheets("Stock Take Import")
Set colIssues = New Collection
lngLastRow = wks.Cells(wks.Rows.Count, "A").End(xlUp).Row
For lngLoop1 = 2 To lngLastRow
If Trim(wks.Cells(lngLoop1, "A").Value) = "" Then
colIssues.Add "Row " & lngLoop1 & ": missing SKU"
ElseIf Not IsNumeric(wks.Cells(lngLoop1, "C").Value) Then
colIssues.Add "Row " & lngLoop1 & ": counted quantity is not a number"
ElseIf wks.Cells(lngLoop1, "C").Value < 0 Then
colIssues.Add "Row " & lngLoop1 & ": negative quantity"
ElseIf Application.WorksheetFunction.CountIf( _
wks.Range("A2:A" & lngLastRow), wks.Cells(lngLoop1, "A").Value) > 1 Then
colIssues.Add "Row " & lngLoop1 & ": SKU " & wks.Cells(lngLoop1, "A").Value & _
" appears more than once"
End If
Next lngLoop1
If colIssues.Count = 0 Then
MsgBox (lngLastRow - 1) & " rows checked, no issues. Ready to post.", vbInformation
Else
For Each varIssue In colIssues
strMsg = strMsg & varIssue & vbNewLine
Next varIssue
MsgBox colIssues.Count & " issue(s) found, nothing posted:" & _
vbNewLine & strMsg, vbExclamation
End If
End Sub
Four checks run against every row of a stock-take import before it is allowed anywhere near the ledger: a missing SKU, a counted quantity that is not numeric, a negative quantity, and a SKU that appears more than once in the same import. Rather than stopping at the first problem row, every issue found is collected into a Collection first, so a branch manager sees the complete list in one message box and can fix everything in a single pass instead of resubmitting the file repeatedly by trial and error. The routine is deliberately read-only: it reports problems and nothing else, called ahead of whichever separate macro actually posts the figures.
Arrays & speed
Read a range into an array once, not cell by cell
' Slow: one worksheet read per iteration
Sub SumStockSlow()
Dim wks As Worksheet
Dim lngLoop1 As Long, dblTotal As Double
Set wks = ThisWorkbook.Worksheets("Stock Levels")
For lngLoop1 = 2 To 20000
dblTotal = dblTotal + wks.Cells(lngLoop1, "D").Value
Next lngLoop1
MsgBox dblTotal
End Sub
' Fast: one worksheet read in total
Sub SumStockFast()
Dim wks As Worksheet
Dim varData As Variant
Dim lngLoop1 As Long, dblTotal As Double
Set wks = ThisWorkbook.Worksheets("Stock Levels")
varData = wks.Range("D2:D20000").Value
For lngLoop1 = LBound(varData, 1) To UBound(varData, 1)
dblTotal = dblTotal + varData(lngLoop1, 1)
Next lngLoop1
MsgBox dblTotal
End Sub
SumStockSlow touches the worksheet 20,000 times, once per row, and each of those reads carries the fixed overhead of a call across the boundary between VBA and Excel's own engine, paid every single time regardless of how little work happens inside the loop. SumStockFast reads the whole range into a two-dimensional Variant array in one call, then loops over the array entirely in memory, which is ordinary VBA and carries none of that per-call cost. Worth timing both with Timer before and after on a range of any real size: the difference is not marginal, and it is usually the single biggest speed change available to a macro built around a large loop.
Arrays & speed
Bulk-write calculated results back to the sheet
Sub ApplyBranchDiscountFast()
Dim wks As Worksheet
Dim varData As Variant
Dim lngLoop1 As Long
Dim dblDiscountRate As Double
Set wks = ThisWorkbook.Worksheets("Price List")
dblDiscountRate = 0.1
varData = wks.Range("C2:C5000").Value
For lngLoop1 = LBound(varData, 1) To UBound(varData, 1)
If Not IsEmpty(varData(lngLoop1, 1)) Then
varData(lngLoop1, 1) = varData(lngLoop1, 1) * (1 - dblDiscountRate)
End If
Next lngLoop1
wks.Range("D2:D5000").Value = varData
End Sub
The source prices are read into an array once, the discount is applied to every element in memory, and the whole result is written back with a single Range.Value assignment instead of one cell at a time inside the loop. A write costs exactly as much per call as a read, so this halves the total number of worksheet touches compared with reading and writing cell by cell, on top of avoiding the per-cell overhead already covered in the routine above. Same principle, applied to output rather than input: build the result in memory, then commit it in one write.
Export
Save a branch report as PDF
Sub ExportBranchReportAsPDF()
Dim wks As Worksheet
Dim strBranchName As String
Dim strExportPath As String
Set wks = ThisWorkbook.Worksheets("Branch Report")
strBranchName = wks.Range("B1").Value
strExportPath = ThisWorkbook.Path & "\" & strBranchName & " - " & _
Format(Date, "yyyy-mm-dd") & ".pdf"
wks.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strExportPath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
MsgBox "Saved: " & strExportPath, vbInformation
End Sub
The branch name is read straight off the report sheet so the exported file names itself, and the export path is built next to the workbook with today's date included, so running the macro twice in one day never silently overwrites a colleague's copy from earlier. ExportAsFixedFormat is called against the sheet's own print area rather than the whole workbook; IgnorePrintAreas:=False matters here specifically, since leaving it at its default True exports everything on the sheet, including anything sitting outside the area actually meant to print.
File system
Create (or clear out) a dated export folder
Sub PrepareExportFolder()
Dim objFso As Object
Dim strBasePath As String
Dim strTodayFolder As String
Dim objFile As Object
Set objFso = CreateObject("Scripting.FileSystemObject")
strBasePath = Environ("USERPROFILE") & "\Desktop\RideMeCycles Exports"
strTodayFolder = strBasePath & "\" & Format(Date, "yyyy-mm-dd")
If Not objFso.FolderExists(strBasePath) Then
objFso.CreateFolder strBasePath
End If
If objFso.FolderExists(strTodayFolder) Then
For Each objFile In objFso.GetFolder(strTodayFolder).Files
objFile.Delete
Next objFile
Else
objFso.CreateFolder strTodayFolder
End If
MsgBox "Ready: " & strTodayFolder, vbInformation
End Sub
FileSystemObject is used here rather than the older MkDir and RmDir statements specifically because it can check whether a folder exists before acting on it; MkDir raises an error if the folder is already there, and RmDir raises one if it isn't empty. The base folder defaults to the current user's own desktop via Environ("USERPROFILE"), so the macro works unmodified on any machine, though strBasePath is an ordinary string and can just as easily point at a specified network location instead. Rather than deleting the dated folder itself, the routine clears out whatever files are already inside it, so a shortcut or a shared link already pointing at that path keeps working even after the macro has run more than once on the same day.
Email
Email a branch summary to a distribution list
Sub EmailBranchSummary()
Dim objOlApp As Object
Dim objOlMail As Object
Dim wks As Worksheet
Dim wksRecipients As Worksheet
Dim strRecipientList As String
Dim rngCell As Range
Dim strTempPath As String
Set wks = ThisWorkbook.Worksheets("Branch Summary")
Set wksRecipients = ThisWorkbook.Worksheets("Distribution List")
For Each rngCell In wksRecipients.Range("A2:A" & _
wksRecipients.Cells(wksRecipients.Rows.Count, "A").End(xlUp).Row)
If Len(rngCell.Value) > 0 Then _
strRecipientList = strRecipientList & rngCell.Value & ";"
Next rngCell
strTempPath = ThisWorkbook.Path & "\Branch Summary - " & _
Format(Date, "yyyy-mm-dd") & ".xlsx"
wks.Copy
ActiveWorkbook.SaveAs strTempPath, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close SaveChanges:=False
Set objOlApp = CreateObject("Outlook.Application")
Set objOlMail = objOlApp.CreateItem(0)
With objOlMail
.To = strRecipientList
.Subject = "Branch Summary - " & Format(Date, "dd mmm yyyy")
.Body = "Attached is today's branch summary." & vbNewLine & vbNewLine & _
"Total sites reporting: " & wks.Range("B1").Value
.Attachments.Add strTempPath
.Send
End With
Kill strTempPath
End Sub
The recipient list is built from a Distribution List sheet rather than hardcoded into the macro, so adding or removing someone is a spreadsheet edit, not a code change. Outlook has no way to attach "a worksheet" directly, only a saved file, so the routine first copies just the Branch Summary sheet out to its own small workbook (wks.Copy with no destination argument creates a new workbook containing only that sheet), saves that as the attachment, sends the email, then deletes the temporary file with Kill. Outlook is deliberately late-bound here with CreateObject rather than referenced early, consistent with the note on early versus late binding elsewhere on this page, since a macro that emails a distribution list is exactly the kind of routine likely to run from a colleague's machine rather than the one it was written on.
Notes in full
Every note above, in full.
The sidebar carries the short version; this is the longer one, for whoever wants the detail behind it.
SEP 2026
Option Explicit, every module, no exceptions
VBA is a loosely typed language. Unless a module explicitly forbids it, referring to a variable name that has never been declared does not raise an error; VBA simply creates a new Variant on the spot, initialised to Empty, and carries on. Option Explicit switches that behaviour off. Once it sits at the top of a module, every variable must appear in a Dim, Public, Private or Static statement before it is used, and the code will not compile until that is true. The practical effect is that a mistyped variable name is caught at compile time, before the macro ever runs, rather than surfacing later as unexplained behaviour.
The failure mode this avoids is easy to miss without it. Consider a loop that accumulates a running total into a variable called runningTotal, and a line further down that refers to runnigTotal by mistake. Without Option Explicit, VBA quietly creates a second, empty variable, and anything that reads from it returns zero or blank rather than an error. Nothing crashes; the macro completes and reports success, and the output is simply wrong, with no line number and no message pointing at the cause. That kind of silent failure is considerably more expensive to track down than a compile error.
Because Option Explicit is a per module setting, it has to be typed into each new module individually unless the editor is told otherwise. Ticking Require Variable Declaration under Tools, Options, Editor makes every module created afterwards insert the statement automatically, which is worth doing once on any development machine. It does not retrofit modules that already exist without it, so a project inherited from elsewhere is worth checking module by module; even one module missing it reintroduces the whole class of error within that module.
Any macro that changes application level settings such as Application.ScreenUpdating, Application.EnableEvents or Application.Calculation is making a temporary change on the understanding that it will be put back afterwards. If an unhandled error occurs partway through, VBA stops execution at the exact line that failed and does nothing further, including nothing to restore those settings. The workbook is left in whatever half configured state the macro happened to be in when it broke, and it stays that way until something else resets it.
The usual structure for avoiding this is a single On Error GoTo CleanUp statement near the top of the procedure, with a CleanUp label near the bottom that restores every setting the macro touched, followed by an Exit Sub placed just before that label so the normal, error free path runs the same restoration code on its way out. Written this way, the cleanup only needs to exist once, and it runs whether the procedure finished normally or was interrupted by an error, rather than being duplicated at every possible exit point.
This matters most for settings like screen updating and automatic calculation being left off, since a workbook stuck in manual calculation looks fine until someone changes a cell and the totals do not move, which is a much harder symptom to trace back to a macro than an obvious crash. Each procedure that changes these settings needs its own handler; an error in a called procedure propagates up to the nearest active handler, so a cleanup section only protects the settings that procedure itself changed, not settings altered elsewhere in the call stack.
Turn off screen updating and automatic calculation for heavy loops
By default, every individual change to a cell can trigger two expensive side effects: the screen redraws to show it, and any dependent formulas in the workbook recalculate. In a loop that writes to a handful of cells this is unnoticeable. In a loop that writes to thousands of cells, both costs are paid on every single iteration, and they dominate the total run time far more than the loop's own logic does. Setting Application.ScreenUpdating to False stops the redraw until updating is switched back on, and setting Application.Calculation to xlManual stops the whole workbook recalculating after every write, deferring it until the code finishes and calculation is set back to automatic.
The effect on a large loop is usually dramatic rather than marginal. A loop writing to several thousand cells across a sheet with a reasonable number of dependent formulas can take a noticeable, visible amount of time with both settings left at their defaults, and drop to a fraction of a second with them switched off. For most slow macros, this single change produces a bigger improvement than any amount of rewriting the loop's internal logic.
The setting must be restored afterwards, and doing so reliably is exactly the reason this pairs with structured error handling: if the macro errors out partway through with calculation left in manual mode, the workbook stays in manual calculation after the macro ends and after the file is closed and reopened, so figures on screen quietly stop updating until someone notices and switches calculation back to automatic by hand. Application.EnableEvents False is often set alongside these for the same reason, to stop worksheet change events firing on every write during the loop.
Code produced by the macro recorder nearly always works by selecting a range or activating a sheet first, then acting on ActiveCell or Selection. That mirrors what a person does with a mouse, but it means the code's behaviour depends on whatever happens to be selected or active at the moment it runs, rather than on the objects it is meant to work with. If the macro is run from a different sheet than the one it was recorded on, or the user has something else selected, the code can act on the wrong range entirely, or fail outright.
Referring to objects directly avoids that dependency. A line such as Worksheets("Data").Range("A1").Value = total works correctly regardless of which sheet is currently displayed or what is currently selected, because it names the target explicitly rather than relying on it having been selected first. The recorded equivalent, selecting the sheet and then the range before setting the value, only works when those selections succeed in the current context, and each Select call also carries its own small overhead.
This becomes more important as a workbook's layout changes over time. Code built around ActiveCell.Offset references breaks quietly when a row is inserted above it, since the offset now points somewhere else, whereas code built around named ranges or explicit cell references keeps pointing at the right place. Declaring proper object variables, such as a Worksheet variable set to a specific sheet, and using With blocks to work through them, also makes the intent of the code considerably easier for someone else to follow later.
Early binding for development, then decide on late binding
Adding a reference to a library such as the Outlook Object Library under Tools, References gives early binding: the VBA editor knows the object model in advance, so it offers IntelliSense, autocomplete on properties and methods, parameter tooltips, and compile time checking that catches a misspelled member name before the code ever runs. The alternative, late binding, declares the variable simply As Object and creates it at runtime with CreateObject, without any reference in place; VBA resolves what the object actually supports only when the line executes.
The choice matters most at distribution time, because an early bound reference is tied to a specific version of the library installed on the machine that created it. Open the file on a machine with a different Office version and the reference can go missing, which produces a compile error that blocks every procedure in the whole project from running, not just the one using that library. Late binding sidesteps this entirely, since no reference is required at all; the cost is that any typo in a late bound member name is no longer caught until the line actually runs, and named constants belonging to the library are not available either and have to be hardcoded as their literal values instead.
The practical approach is usually to keep the reference in place while writing and testing the macro, for the productivity of IntelliSense, then decide before distribution whether every machine that will run the file has the same library version available. On a fixed, internally controlled estate, keeping early binding is often fine; anywhere the file will be opened on machines outside that control, switching the declarations to Object and the object creation to CreateObject before it goes out avoids the missing reference failure altogether.
It is common for a fixed value, a folder path, a threshold percentage, a file name pattern, to end up typed directly into the code at every point it is needed, rather than declared once. Each occurrence works fine on its own, but the value now exists in several places at once, tied together only by the fact that a person remembered to type the same thing each time. When that value needs to change, every occurrence has to be found and updated individually, and missing even one leaves part of the macro working from a value that no longer matches the rest.
Declaring it once, as a Public Const in a dedicated module, or as a function that reads a named range on a configuration sheet for values a business user might reasonably need to change, removes that risk. Every other procedure refers to the constant or the function by name rather than repeating the literal value. Changing the value then means editing a single declaration; every procedure that reads it picks up the new value automatically the next time it runs, and there is no search and replace step that could miss an instance.
It is worth distinguishing genuinely fixed, compile time values, which suit a Const declaration, from values a business user might want to adjust without opening the VBA editor at all, which are better held on a hidden configuration sheet and read through a small wrapper function. Either way, the underlying principle is the same: one canonical source for the value, referenced everywhere it is used, rather than the same number or string typed out independently in several places across the project.
Workbook_Open and Workbook_BeforeClose, held in the ThisWorkbook module, fire automatically every time the file is opened or closed, without the user asking for anything to run. Because of that, whatever code lives inside them is the code that shapes a user's first and last impression of the workbook on every single use, regardless of how well optimised the rest of the macros in the file are. Slow code anywhere else in the project only matters when that particular routine is called; slow code here runs unconditionally, every time.
This is often where refreshing external data connections, checking permissions, or resetting the screen state ends up placed, since it feels natural to want the workbook ready as soon as it opens. If that work is done synchronously and takes any real time, the user sees Excel appear to hang, with a spinning cursor, before the file becomes usable, and the same delay repeats on every single open regardless of whether that refresh was actually needed on that occasion.
Keeping these two event procedures minimal, and moving anything genuinely slow out of them, avoids that. Heavy work can be deferred with Application.OnTime so the file opens immediately and the update happens a moment later once the interface is already responsive, or it can be moved behind an explicit button so it only runs when a user actually asks for it rather than unconditionally on every open. Workbook_BeforeClose deserves the same treatment: slow validation there delays the user's ability to close the file at all, which is a worse experience than the same check running earlier, while there is still time to act on it.
Office treats any macro enabled workbook that is neither digitally signed nor opened from a trusted location as untrusted by default, and shows the yellow security warning bar every time such a file is opened, until that specific user has explicitly clicked Enable Content for that specific file. That means every recipient of an unsigned workbook from an untrusted location sees the same warning on their own first open, and it can reappear later if the file is moved, renamed, or if local security settings are reset.
There are two established ways to remove that friction. Digitally signing the VBA project with a code signing certificate, whether an internally issued one for use within an organisation or one issued by a public certificate authority for wider distribution, means Office can validate the signature and remember that publisher as trusted after the recipient accepts it once. Alternatively, configuring a specific folder, typically a shared network location the whole team already uses, as a Trusted Location in the Trust Center means any file opened from that folder runs its macros without any prompt at all.
A self signed certificate still produces a warning the first time a given user encounters it, since nothing has told their machine to trust that particular signer yet; the benefit at any real scale comes from a certificate authority issued certificate, or from rolling out the trusted location setting centrally so it is already configured before anyone opens the file. It is also worth remembering that a signature covers the compiled state of the project at the moment it was signed; any subsequent change to the code invalidates the existing signature, and the project has to be signed again before the updated file is redistributed.
Structured data is sometimes represented as several separate arrays kept in step by a shared index, one array for names, one for amounts, one for dates, with record number three meaning the third element of every array at once. Nothing in the language enforces that alignment; it holds only by convention, and every operation that resizes, sorts, inserts into or removes from one array has to remember to apply exactly the same operation to every other array in the set. Update one and forget another, and record three's name silently ends up attached to record four's amount, with no error raised anywhere.
A class module avoids that by packaging the related fields into a single object. A class such as clsRecord with Name, Amount and Date as properties, instantiated once per record and held in a Collection, keeps each record's fields together as one unit; there is no separate index to keep synchronised, because the fields cannot get out of step with each other inside a single object. Adding a new field to the data model later means adding one property to the class, rather than introducing a fourth parallel array and updating every procedure that touches the existing three.
The readability difference tends to matter more as the code grows. Sorting a collection of objects by a named property, or passing a single record into a procedure as one parameter, reads clearly to someone unfamiliar with the code, whereas passing several separate arrays and an index number requires them to work out what index three actually represents across four unrelated variables. The extra structure of writing a class module upfront is a modest cost that pays back once the data being represented has more than a couple of fields, or needs to be passed between procedures at all.
An .xlsm or .xlsb file stores its VBA project inside a binary structure, not as readable text, so ordinary version control tools cannot show anything meaningful when comparing two versions of the workbook. The whole file registers as changed, since the binary content differs, but there is no way to see which procedure was actually edited, what the change was, or who made it; the file simply looks different from the outside, with no detail behind that.
The VBA editor can export each component as a separate plain text file: standard modules as .bas, class modules as .cls, userforms as .frm together with an accompanying .frx for any binary form resources, using File, Export File. Keeping those exported files in a folder tracked by ordinary source control such as git, alongside or instead of relying on the workbook file itself, gives a genuine line by line diff between versions, showing exactly which procedure changed, what the change was, and when it happened, in the same way source control works for any other codebase.
The exported text captures the code only, not the workbook's data or sheet layout, so the binary workbook still needs to be tracked separately, or a documented process needs to exist for reimporting the modules into a fresh copy of the workbook. Reimporting also needs a degree of care with worksheet code behind modules such as Sheet1 or ThisWorkbook, since these already exist inside the workbook rather than being freely creatable objects, and any .frx resource files, while not human readable text, still need to be kept alongside their .frm file for a userform to reimport correctly.
