Tuesday, October 31, 2017

Where Should You Stick Your Function?

Let's say you need to create a VBA function to use in a spreadsheet. Where do you put it? Quick answer, put it in a module.

In Excel, press Alt-F11, Ctrl-R (to set focus to the Project Explorer). Right click on VBAProject (xls_name), where xls_name in the file name of your workbook. Select Insert Module.

A blank code window should appear and the cursor should be positioned in it.  This is "Module1" where you can enter your code, such as...

Option Explicit
Function FortyTwo(r As Range)
If r.Value = 42 Then
    FortyTwo = "Forty-Two"
Else
    FortyTwo = r.Value
End If
End Function


If you enter 1, apple, and =6*7 in cells A1, A2 A3, here's what you get as output if you enter =FortyTwo(A1) in cell B1 and drag down to B3:







Of course, if A3 contained =6*9 you should also get Forty-Two, but that's a programming challenge for another day.