Excel Macros

Excel supports small programs known as Macros, written in VBA [Visual Basic for Applications] to process data and respond; this might be to provide a new number or text into a cell, or create a chart, or it might be to adjust cell contents - the options are very much open to your imagination.

To mark an Excel file as one including macros - you should name it xxxxx.xlsm instead of .xls (or .xlsx).

Macros contain VBA commands that allow you to check conditions, prompt for additional content, loop, set values, etc.You can update the status bar as you process things, e.g. Application.StatusBar = "I'm doing this now...", and you can interact with the file system, loading data from external files, and even deleting files (Kill) if you need.

To create a macro, you need to access the VBA Window - accessible by pressing [ALT]-[F11]

Alternatively, you can enable the Developer Tab & Ribbon by ticking on the Developer tab on the Quick Access toolbar, and selecting the More Commands... menu option:

... go to Customize Ribbon and then tick Developer

This gives you a Ribbon for your VBA work:

Now, you can create macros against your workbook, or on a specific sheet.

Macros can then be triggered via either [ALT]-F8 or with the Developer Tab, selecting the Macros option..

Add in a Macro Name, and click Create to open the VBA editor

Enter your preferred macro name and the start building your VBA. ; macros can be Subroutines or Functions, each with subtle differences:

Tip: Make sure you can see the two fields at the top of the right-hand panel - if you are in a different type of panel then your code may not work as expected.

Where to add a Macro

Macros are typically added to in a standard Module (Module1, Module2, etc) - these exist to hold reusable, runnable code that is not tied to a specific Excel object.

Macros can be placed into the sheet or Workbook - but best practice is that they are in sheets when the sheet itself triggers the code and a specific non-generic/non-reusable response is needed (e.g. responding to user actions on that sheet, enforcing specific data rules, automatic reactions to changes, e.g. Worksheet_Change, Worksheet_SelectionChange, Worksheet_Activate, Worksheet_BeforeDoubleClick)

e.g.

vba :: Sheet macro to detect changes

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        MsgBox "Column A changed"
    End If
End Sub

Workbook event use a similar rule set, e.g. Startup logic, Cleanup logic, Security checks, Environment setup, etc, e.g. Workbook_Open, Workbook_BeforeClose, Workbook_BeforeSave, Workbook_SheetChange

vba :: Workbook macro run when is is opened:

Private Sub Workbook_Open()
    MsgBox "Welcome!"
End Sub

Recording Macros

To short cut hand writing (or chatGPT writing) macros, you might choose to record steps performed on the sheet directly - each click on a cell or a toolbar option creates a VBA command that is added to your macro as you go - simply click on [Record Macro] and start - then [Stop Recording] when you're done - you can then review and edit the generated macro as if you had written it directly.

Writing Macros

Using [ALT]-F8 and add your VBA to whichever Module, Workbook or sheet is appropriate.

Sample VBA Macro - WeightedAverage

vba :: :WeightedAverage

Function WeightedAverage(values As Range, weights As Range) As Double
    Dim i As Long
    Dim sumProduct As Double
    Dim sumWeights As Double
    
    ' Check ranges match
    If values.Count <> weights.Count Then
        WeightedAverage = CVErr(xlErrRef)
        Exit Function
    End If
    
    ' Loop through all cells
    For i = 1 To values.Count
        sumProduct = sumProduct + values.Cells(i).Value * weights.Cells(i).Value
        sumWeights = sumWeights + weights.Cells(i).Value
    Next i
    
    ' Avoid division by zero
    If sumWeights = 0 Then
        WeightedAverage = 0
    Else
        WeightedAverage = sumProduct / sumWeights
    End If
End Function

Usage: =WeightedAverage(A2:A10, B2:B10)

Sample VBA Macro - BusinessDaysBetween

vba :: BusinessDaysBetween

Function BusinessDaysBetween(startDate As Date, endDate As Date) As Long
    Dim countDays As Long
    Dim currentDate As Date
    
    ' Swap if startDate > endDate
    If startDate > endDate Then
        currentDate = startDate
        startDate = endDate
        endDate = currentDate
    End If
    
    countDays = 0
    currentDate = startDate
    
    ' Loop through dates
    Do While currentDate <= endDate
        ' Check if weekday (Mon-Fri)
        If Weekday(currentDate, vbMonday) <= 5 Then
            countDays = countDays + 1
        End If
        currentDate = currentDate + 1
    Loop
    
    BusinessDaysBetween = countDays
End Function

Usage: =BusinessDaysBetween(A2, B2)

Sample VBA Macro - BusinessDaysBetweenWithHolidays

vba :: BusinessDaysBetweenWithHolidays

Function BusinessDaysBetweenWithHolidays(startDate As Date, endDate As Date, Optional holidays As Range) As Long
    Dim countDays As Long
    Dim currentDate As Date
    Dim cell As Range
    Dim isHoliday As Boolean
    
    ' Swap if startDate > endDate
    If startDate > endDate Then
        currentDate = startDate
        startDate = endDate
        endDate = currentDate
    End If
    
    countDays = 0
    currentDate = startDate
    
    ' Loop through each date
    Do While currentDate <= endDate
        ' Check if weekday (Mon-Fri)
        If Weekday(currentDate, vbMonday) <= 5 Then
            isHoliday = False
            
            ' Check if currentDate is in holidays range
            If Not holidays Is Nothing Then
                For Each cell In holidays
                    If IsDate(cell.Value) Then
                        If cell.Value = currentDate Then
                            isHoliday = True
                            Exit For
                        End If
                    End If
                Next cell
            End If
            
            ' Count only if not a holiday
            If Not isHoliday Then
                countDays = countDays + 1
            End If
        End If
        currentDate = currentDate + 1
    Loop
    
    BusinessDaysBetween = countDays
End Function

Usage: =BusinessDaysBetween(A2, B2, C2:C10) (where C2:C10 is an Excel range giving the optional list of holiday days)