Excel Insights

A Microsoft MVP guide to the best parts of Excel

24 Excel MVPs

• Publisher: Holy Macro! Books
• Year: 2020
• Print ISBN: 9781615470679
• eBook ISBN: 9781615471539
• Edition: 1
• Topic: Computer Science
• Subtopic: Desktop Applications
• Index: Computer Science

 
Learn favorite techniques from this group of twenty-two Excel MVPs. The Excel MVPs are friends and competitors who each pulled out their favorite tricks to impress you and their fellow MVPs.

Make Your Own VBA Worksheet Functions

by Charles Williams

When none of Excel’s native Functions do what you want, you can write your own Worksheet Functions (User Defined Function or UDF for short) using VBA. This chapter shows you simple step-by-step techniques that you may use to write efficient UDFs like a professional.

A Simple Averaging Function

Suppose you want to write a function that calculates the average of the absolute values from a range of cells while excluding from the average anything that is not a number or is less than a certain number, tolerance. Let’s call the function AverageTol. To do this,

• Start Excel.
• Alt + F11 gets you to the Visual Basic Editor (VBE).
• Insert –> Module.
  Note: VBA Worksheet Functions MUST be in a standard module, not in a worksheet or other class module.

Then, enter the following VBA Code:

Function AverageTol(theRange, dTol)

For Each Thing In theRange

If IsNumeric(Thing) Then

If Abs(Thing) > dTol Then

AverageTol = AverageTol + Abs(Thing)

lCount = lCount + 1

End If

End If

Next Thing

AverageTol = AverageTol / lCount

End Function

The function loops through every cell in the range and, if the absolute value of the cell is a number greater than the tolerance, adds it to the total and increments a count. Finally, it divides the total by the count and returns the result.

Now, go back to the Excel worksheet, enter some data in cells A1:A10 and in B1 enter

=AverageTol(A1:A10,5)

That was pretty easy and works well for 10 cells. However, if you have a lot of data, say 32,000 cells, then 15 formulas using this UDF takes 2.3 seconds to calculate on my laptop.

One major reason this is so slow is that I used all the defaults: I was lazy and did not declare any of the variables, so they all defaulted to Variants. That’s SLOW … but I can easily improve it: here is version A of AverageTol:

Function AverageTolA(theRange As Range, dTol As Double)

Dim oCell As Range

Dim lCount As Long

For Each oCell In theRange

If IsNumeric(oCell) Then

If Abs(oCell) > dTol Then

AverageTolA = AverageTolA + Abs(oCell)

lCount = lCount + 1

End If

End If

Next oCell

AverageTolA = AverageTolA / lCount

End Function

This is the same function but with each variable declare...

Table of contents

• Smart Uses of Custom Number Formatting
• Ctrl + Enter
• Auto-Magically Master INDEX MATCH (and Other Formulas)
• Relative Named Ranges – When Named Ranges Go Walkabout
• An Introduction to Excel’s New Data Types
• A Look to the Future – Dynamic Arrays
• XLOOKUP Debuts in Excel
• Why the Love / Hate for Pie Charts?
• Intermediate Charting in Excel
• Creating Charts for Presentations
• Advanced Filter
• Power Query: Manipulate Your Data Like a Pro
• Combine All Files in a Folder
• Power Query M Code Approximate Match Lookup Formula
• The Power Behind the Boringest Sentence in Excel
• Understanding Context in Power Pivot
• Thinking Through the Modelling of a Seating Chart
• Financial Modelling
• Creative Excel Model Development
• An Introduction to Simulation in Excel
• Staying out of Trouble
• Make Your Own VBA Worksheet Functions
• An Overview of Modern VBA Best Practices
• About the MVPs
• Index