No detailed VBA knowledge is required; if you want you can just install these without understanding how they work with our handy guide from TOTW #410. But the code is included here for reference if you do want to use it to study and learn.

This topic has been covered once before, back in TOTW #82, if you want to compare and contrast.

Macros

Here are a few of my favourites!

Unhide all sheets

This one is pretty straightforward – if you are on any of the older Excel versions that don’t let you unhide multiple sheets at once, this macro will automatically unhide all the sheets for you:

Sub UnhideAllSheets()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.Visible = xlSheetVisible
Next ws

End Sub

Replace with values

A simple one, but very handy if you assign a keyboard shortcut to it – this will copy the current cell, and then paste over it with a paste-values:

Sub ReplaceWithValues()

    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

Table of contents

Likewise quite straightforward, this macro creates a list of all the sheet names, starting at the current cell:

Sub TableOfContents()

For Each ws In Worksheets
    ActiveCell.Value = ws.Name
    ActiveCell.Offset(1, 0).Activate
Next ws

End Sub

There’s a longer version of this that also makes hyperlinks between the contents page and all the other sheets in TOTW #263.

Worksheet splitter

Finally, here’s a function that splits each worksheet in a workbook into its own individual file:

Public Sub Splitter()
Dim i As Integer
Dim FPath As String
Dim ParentFile As Workbook

FPath = ActiveWorkbook.Path
Set ParentFile = ActiveWorkbook

For i = 1 To ActiveWorkbook.Sheets.Count
    ParentFile.Activate
    ActiveWorkbook.Sheets(i).Copy
    ActiveWorkbook.SaveAs (FPath & Application.PathSeparator & ActiveSheet.Name)
Next i

End Sub

User-defined functions

ORDINAL

This function changes a whole number into the ordinal version – so for example 1 into 1st, 4 into 4th, and so on. Note that the output of this function will be a text value.

Public Function ORDINAL(InputCell As Range) As String

Select Case InputCell.Value
Case 11
    ORDINAL = "11th"
Case 12
    ORDINAL = "12th"
Case 13
    ORDINAL = "13th"
Case Else
    Dim LastNum As Integer
    LastNum = Right(InputCell.Value, 1)
    Dim Suffix As String
    Select Case LastNum
    Case 1
        Suffix = "st"
    Case 2
        Suffix = "nd"
    Case 3
        Suffix = "rd"
    Case Else
        Suffix = "th"
    End Select
    ORDINAL = InputCell.Value & Suffix
End Select

End Function

The formula syntax is just:

=ORDINAL(cell address)

NDAY

This function can compute the date of the nth occurrence of a particular day of a week within a month – e.g. the 3rd Saturday in July 2021. Here’s the code:

Function NDAY(ORDINAL As Variant, WhichDay As Variant, MonthNo As Variant, YearNo As Variant) As Date
Dim TrialDate As Date

TrialDate = DateSerial(YearNo, MonthNo, 1)

Do While ORDINAL > 0
    If Weekday(TrialDate, vbMonday) = WhichDay Then
        ORDINAL = ORDINAL - 1
        Else
    End If
    TrialDate = DateAdd("d", 1, TrialDate)
Loop

NDAY = DateAdd("d", -1, TrialDate)

End Function

And here’s the formula syntax:

=NDAY(desired ordinal number, day of the week number (Monday=1), month number, year number)

Using our above example of the 3rd Saturday in July 2021, that would be:

=NDAY(3, 6, 7, 2021)

…which correctly returns 17/07/2021.

COUNTIFCOLOUR / SUMIFCOLOUR

A pair of functions here, which can either count how many cells in a range have the same colour as an example cell, or which add the numbers from the matching cells.

Public Function COUNTIFCOLOUR(SampleCell As Range, CountRange As Range) As Long
Dim cell As Range

For Each cell In CountRange
    If cell.Interior.Color = SampleCell.Interior.Color Then
        COUNTIFCOLOUR = COUNTIFCOLOUR + 1
        Else
    End If
Next cell

End Function

Public Function SUMIFCOLOUR(SampleCell As Range, AddRange As Range) As Double
Dim cell As Range

For Each cell In AddRange
    If cell.Interior.Color = SampleCell.Interior.Color Then
        If IsNumeric(cell.Value) Then
            SUMIFCOLOUR = SUMIFCOLOUR + cell.Value
            Else
        End If
        Else
    End If
Next cell

End Function

And the formula syntax:

=COUNTIFCOLOUR(sample cell, range)
=SUMIFCOLOUR(sample cell, range)

Do you have any favourites? Feel free to share them on LinkedIn, via our Twitter page, or via email!

Related resources

• Previous post - Select Case and stamp duty revisited
• TOTW index
• Next time – STOCKHISTORY and exchange rates!