Maybe other engineers have had different experiences, but I know that during my degree, Microsoft Excel became a very close and special friend to me. Sure, it’s not the most powerful software, but it can do any calculation that you could do on paper, and it will do it over and over and over again. This makes it great for iterative calculations.

God I love a good spreadsheet.

The problem with Excel is that, like most good pieces of software, it was written by a person. This means that it was written by exactly the kind of jerk who would deliberately and maliciously write the code in a way that slightly inconveniences me. Those guys are the worst.

Okay, maybe I’m exagerating. But there are certain functions in excel that aren’t quite right, or haven’t been followed through to their logical conclusion (although I’ll touch on the likely reasons for this in a moment). This post is about addressing these shortcomings, and writing functions that will work the way you want them to.

Writing User Defined Functions (UDF):

To write your UDF, hit Alt + F11 to bring up the VBA (Visual Basic for Applications) window. Select a location to store the function. Your options are as follows:

- Worksheet
- Workbook
- A Module

Simply double click on the worksheet, workbook, or Module in which you want to insert the code. If you want to use a module, simple click INSERT – MODULE, as shown below. You can rename these modules whatever you want, and export them for use in other spreadsheets. I like modules, as they are easy ways of tidying up your code library within a spreadsheet

Note: Ensure that you are writing your code inside the right VBA Project. You’ll see in the panel on the left of the VBA window that there is a VBA project for each spreadsheet open, as well as any Add In spreadsheets running in the background. So just select the worksheet, workbook or module within the appropriate project.

Now, the fun bit: Coding.

Simply start writing your function using the following:

Function Functionname(value1, value2, value3)

You can specify the variable type for the values given in brackets, e.g. value1 As Range, or you can leave it implicit.

Excel should completed the End Function statement for you, and you can start writing your code in between these statements:

functionResult=value1+value2+value3

Within your function, simply set the output value by using a line similar to:

Functionname = functionResult

Putting it all together, in this example, you are left with your User Defined Function, which should look something like this:

Function Functionname(value1, value2, value3)

functionResult = value1 + value2 + value3

Functionname = functionResult

End Function

To use this function just type in “=Functionname(1,2,5)” in a cell, to return the value “8”. You can refer to a cell for any (or all) of the inputs, and it will use the value inside that cell.

Once you’ve written your function, if it’s one that you’ll use again and again, you can copy and paste it into the VBA project for any new spreadsheet that you need to use it in. Alternatively, if you only need to use these spreadsheets on YOUR computer (or if you want the code stored somewhere handy for easy pasting into new spreadsheets without having to find the original), you can post the code into a module in a blank spreadsheet, and save that spreadsheet as an Add In (.XLAM format) (see image below), then activate the add-in. Once you’ve done this, any time you open up Excel your computer will open up the XLAM file and make any SUBs or FUNCTIONs in it available to you. (Note: if you send your spreadsheet to someone who doesn’t have your add-in, it won’t operate correctly as the code isn’t availble, so in that case it’s good to save the function in the WORKBOOK of that specific spreadsheet). I’ll do another post shortly about how to add it to the list of functions displayed when you pres the “fx” button, as well as how to show descriptions for the inputs of the function.

As an example of where these custom Add Ins are useful, I have add-ins that I use at work for quick “back of the envelope” calculations (e.g. “how much power would the conveyor need to lift 20m?” or “what would the full load torque of that motor be?”), and since I generally have Excel open, I can simply use my LiftPower() or FLT() functions in excel to get a quick answer.

## Example: Lookup

I’m sure many of you are familiar with the shortcomings of the excel Lookup functions (LOOKUP, VLOOKUP, HLOOKUP). Primarily these shortcomings are:

- Formatting limitations (the lookup column must be sorted in ascending order)
- Result limitations (minimal control over what to do if no exact match is found, or if multiple matches are found)

After looking at how these functions work, it’s obvious that the code was written so as to give a reasonably workable function, while minimising processing requirements. Since excel spreadsheets can get quite large, an older computer could get quite bogged down in running the multitude of comparisons required by the Lookup function, so it seems like they let it shortcut out of the code by exiting the loop and returning the result value as soon as the value in the next lookup cell is greater than the value being searched for. Presumably the decision was made that it was less likely that people would be bothered by having to sort their data in ascending order (for the lookup column) than they would be by the slowness caused by the spreadsheet running through excess code.

As for shortcomings in the outputs, this is a result of an assumed philosophy of keeping things simple. An increased number of options just increases the difficulty of using the function.

At the end of the day, anyone who is so into Excel that they aren’t entirely happy with how these functions operate, is probably into it enough to write their own User Defined Functions.

Thank the heavens for VBA. Can I get an “Amen!”?

In a fit of pique, I wrote my own lookup function, which I call Ulookup (Ultra Lookup). Depending on the option selected, the function will search the Lookup array and return one of the following types of result from the Results array:

- Get first exact value
- Get last exact value
- Get nearest value
- Get nearest value below
- Get nearest value over
- Return “” if not found
- Interpolate between two nearest results
- Sum of exact results

Here’s the code for my Ulookup (I’ll upload a link to the actual XLAM format add-in as an example, just as soon as I find somewhere to store it):

Function Ulookup(value, lookupArray As Range, resultsArray As Range, lookupType As Integer)

temp = “error”

Dim temp1, temp2, a, b, Ra, Rb As Long

Dim check As Boolean

check = False

Dim Rl, Cl, Rr, Cr, i As Integer

Rl = lookupArray.Rows.Count

Cl = lookupArray.Columns.Count

Rr = resultsArray.Rows.Count

Cr = resultsArray.Columns.Count

‘ lookupType is type of lookup. 0 = get first exact value, 1 = get last exact value, 2 = get nearest value, 3 = get nearest value below, 4 = get nearest value over, 5 = return “” if not found, 6 = interpolate

Select Case lookupType

Case 0 ‘ get first exact value

For i = 1 To Rl

If check = False Then

If lookupArray.Cells(i, 1).value = value Then

temp = resultsArray.Cells(i, 1)

check = True

End If

End If

Next

If temp = “error” Then

temp = “value not found”

End If

Case 1 ‘ get last exact value

For i = 1 To Rl

If lookupArray.Cells(i, 1).value = value Then

temp = resultsArray.Cells(i, 1)

End If

Next

If temp = “error” Then

temp = “value not found”

End If

Case 2 ‘ get nearest value

For i = 1 To Rl

If i = 1 Then

temp1 = Val(lookupArray.Cells(i, 1))

temp = resultsArray.Cells(i, 1)

Else

temp2 = Val(lookupArray.Cells(i, 1))

If Abs(temp2 – value) < Abs(temp1 – value) Then

temp1 = temp2

temp = resultsArray.Cells(i, 1)

End If

End If

Next

Case 3 ‘ get nearest value below

For i = 1 To Rl

If Val(lookupArray.Cells(i, 1)) <= value Then

If check Then

temp1 = Val(lookupArray.Cells(i, 1))

temp = resultsArray.Cells(i, 1)

Else

temp2 = Val(lookupArray.Cells(i, 1))

If Abs(temp2 – value) < Abs(temp1 – value) Then

temp1 = temp2

temp = resultsArray.Cells(i, 1)

End If

End If

End If

Next

Case 4 ‘get nearest value over

For i = 1 To Rl

If Val(lookupArray.Cells(i, 1)) >= value Then

If check Then

temp1 = Val(lookupArray.Cells(i, 1))

temp = resultsArray.Cells(i, 1)

Else

temp2 = Val(lookupArray.Cells(i, 1))

If Abs(temp2 – value) < Abs(temp1 – value) Then

temp1 = temp2

temp = resultsArray.Cells(i, 1)

End If

End If

End If

Next

Case 5 ‘linear interpolation

check = False

temp1 = 0

temp2 = 0

For i = 1 To Rl

If Val(lookupArray.Cells(i, 1)) <= value Then

If check Then

temp1 = Val(lookupArray.Cells(i, 1))

Ra = Val(resultsArray.Cells(i, 1))

Else

temp2 = Val(lookupArray.Cells(i, 1))

If Abs(temp2 – value) < Abs(temp1 – value) Then

temp1 = temp2

Ra = Val(resultsArray.Cells(i, 1))

End If

End If

End If

Next

a = temp1

check = False

temp1 = 0

temp2 = 0

For i = 1 To Rl

If Val(lookupArray.Cells(i, 1)) >= value Then

If check Then

temp1 = Val(lookupArray.Cells(i, 1))

Rb = Val(resultsArray.Cells(i, 1))

Else

temp2 = Val(lookupArray.Cells(i, 1))

If Abs(temp2 – value) < Abs(temp1 – value) Then

temp1 = temp2

Rb = Val(resultsArray.Cells(i, 1))

End If

End If

End If

Next

b = temp1

If a = b Then

temp = Ra

Else

temp = ((value – a) / (b – a)) * (Rb – Ra) + Ra

End If

Case 6 ‘return “” if not found

temp = “”

For i = 1 To Rl

If lookupArray.Cells(i, 1).value = value Then

temp = resultsArray.Cells(i, 1)

End If

Next

End Select

Ulookup = temp

End Function