ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week #419 - Revisiting macros and custom functions

Author: David Lyford-Tilley

Published: 09 Nov 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week, we have a Developer post in which I am sharing some of my frequently-used macros and custom functions with you all.

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

Join the Excel Community

Do you use Excel in your organisation? Are you using it to its maximum potential? Develop your skills and minimise spreadsheet risk with our Excel resources. Membership is open to everyone - non ICAEW members are also welcome to join.

Excel polaroid

Search the Excel Community archive

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Excel polaroid
Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250