Creating User Defined Functions in Excel

I don’t think I have written this before but sometimes it can be useful to create your own functions via VBA. This need happens very rarely but it occurs when I need to do something and there is no simple function to do the job. When Googling, the only option that would come up would be a VBA function.

Example 1: Finding hard-coded cells

A couple of years ago, I need to figure out a very quick way to check that a sheet in a file did not have any hard-coded numbers or text. The sheet was supposed to be strictly formulas. At the time, there was only one common method and that was using the “Show Formulas” located under the Formula tab in the menu system.

Unfortunately, that method just led to a sheet full of “=formula”, making it extremely difficult to spot the hard-coded cells. I needed something better.

Googling led me to VBA functions.

Now back then, I failed to make a note of the website that gave me the following VBA function, which I believe is also called a User Defined Function, so the following link is the next best thing I could find: https://www.pryor.com/blog/how-to-create-a-custom-function-in-excel/.

Function CellFormula(Rng As Range) As String
    CellFormula = Rng.Formula
End Function

To use in a spreadsheet, you would use =CellFormula(reference) just like you would use =SUM(reference) but instead of a range of cells, you would typically use just a single cell.

The way I used it back then, I had an auditing sheet, either in the same file or another file, and I would use the following formula: =IF(LEFT(CellFormula(‘Sheet1’!A1),1)<>”=”,”x”,””).

I would copy that formula throughout the sheet and any cell that had been overlaid with hardcoded numbers or text would show up as a “x”.

That was a really quick way of spotting hard-coded cells.

Today, we can use =FORMULATEXT and I think you can probably use it to fashion a quick look at a sheet for any hard-coded cells, much like I did way back when. This new FORMULATEXT is part of the Office 365 Excel or Excel 2021. If you have Excel 2019 or earlier, you are not going to have this function.

Example 2: Pulling directory path from links

This VBA function is a more recent function, like maybe a month ago, so Office 365 probably doesn’t have a function for this example.

I was trying to find a particular file which I created monthly with the same name but placed into different folders representing different weeks. For a while, I was using this file weekly and storing the results into a weekly folder but then my use of the file got sporadic. I wanted to find the last time I used the file without digging through the weekly folders.

I did have a VBA program that would create a list of file names found under a specified folder name. This list would name the files and would present them as hyperlinks in case I wanted to open them up.

The problem was that list did not include weekly folder name.

But, if I hover my mouse over the hyperlink, the directory path would show up as a tip. That gave me an idea of Googling for a way to extract the directory path from the hyperlink.

Again, Googling led me to VBA functions.

A website, which again I didn’t take note of at the time, you can use is https://www.excel-university.com/extract-url-from-hyperlink-with-an-excel-formula/.

Function URL(Hyperlink As Range)
  URL = Hyperlink.Hyperlinks(1).Address
End Function

In a regular sheet, just use =URL(reference).

Closing

To wrap up, those are the only two VBA functions I have ever had to create in the last decade or so, so this is a very rare occurrence. But it really helps to be familiar with writing up VBA codes because you can delve into this with comfort of knowing what to do.

If you ever run across a VBA user defined function when you are Googling, don’t discount this method. As you can see, it is very easy to set up. You just need to know how to start the process of writing a macro code. An easy way would be to use a macro recorder to jumpstart the process, then substitute SUB with Function, and copy over the function the author provides.