Create the ultimate lookup or decision formula; a User Defined Function using Visual Basic for Applications.
by David Goodmanson
Following our recent theme of reference, decision and lookup functions, in this article we will go one step further and create the ultimate lookup or decision formula; a User Defined Function (UDF), using Visual Basic for Applications (VBA).
A word before continuing. What is being presented here is one way of using VBA and the large library of functions available in the Excel Object Library. Our aim is to demonstrate methods you can use to solve your problems and make Excel work better for you.
We continue with our previous example listing some people and their ratings for the economic outlook.
Why a User Defined Function?
When a cell formula gets too complex to handle or even impossible to put in a single cell, you can write Visual Basic code and link to that code as a new custom made cell function.
Custom User Defined Functions (UDF) are simple to make and the code is easier to read than a long cell formula.
On the downside, custom UDF’s will bring up security warnings (just like macros) that might confuse other users. A worksheet with a custom UDF will run more slowly though the difference isn’t noticeable except with very large and complex worksheets.
Basic User Defined Function
In past articles, we used a VLookup table to translate the numeric rating into a prediction. Now we’ll take it a step further and use VBA to create a Custom function referred to as a UDF or User Defined Function.
First, we need to open a new workbook and enter the table of economists names along with their rating. (Don’t enter the prediction, this will be done by the UDF). Now we need to build the UDF.
To write the code for the UDF we need to go to the VBA Editor and paste the code that follows. If you are using Excel 2003 go to Tools -> Macro -> VBA Editor or press ALT + F11. In Excel 2007, there are a couple more steps. Go to the Office Button on the top left and click Excel Options. This opens a dialogue box, select “Popular” and check the “Show Developer tab” option, and click OK. Now click the Developer tab on the ribbon and click on the Visual Basic button (and I thought the ribbon was to make things simpler!).
Both of these methods will take you to the Visual Basic Editor (VBE). Or, you can use the keyboard shortcut (ALT + F11). The VBA code for our example works in both Excel versions.
When you open the VB Editor without any code it looks fairly barren. Whether you use the key combination ALT + F11 or use the menu method above to navigate to the VBE. The result will look empty and this is where we will insert a module into which we shall enter our code.
The next step is to insert the module. We can then copy the code from this article and paste the custom UDF code. The window on the left is called “Project Explorer”. In Project Explorer, right click a folder called “Microsoft Excel Objects” and then click Insert. On the flyout menu, click “Module” and Excel will fill out the remains of the VBE with a white sheet called a Standard Module.
To switch back to the standard Excel interface, either use (ALT + F11) or click on the Excel icon on the toolbar. Copy and paste the code listing below into the new module.
Function RatingText(intRate As Integer) As String
Select Case intRate
Case 1
RatingText = “Depression”
Case 2
RatingText = “Recession”
Case 3
RatingText = “Turning Point”
Case 4
RatingText = “Recovery”
Case 5
RatingText = “Boom”
End Select
End Function
This is a simple function to get us started, you could do a similar thing in a cell function, but later you’ll see a more complex UDF.
Now the UDF RatingText() becomes part of the available functions in a cell like IF, Round or many others. In other words, we can use it just like other “normal” Excel worksheet functions. Before we go a step further, let’s take a step back. What does this code mean?
Code Structure
The first word “Function” means it will return a value, e.g. “Boom”, “Recovery”. (As opposed to “Sub” which will execute a procedure). Next, we give the function a name, “RatingText” and at the end of the first line we see the expression “as String”. This tells VBA that the value we are going to return from the function (“RatingText”) is a “String”, meaning it is text. In brackets following “RatingText” is an argument called intRate as integer. Just like with the function, this tells VBA that the required value for this variable is an integer (or whole number e.g. 1,2,3 etc). in our example it is the rating number or integer, which we supply to the function so that it can use this value to produce a result, i.e. RatingText(3) = “Turning point”. In other words,
RatingText(intRate) = “Some string of characters that we define in our code”
Using the “Select Case” structure, we evaluate the variable (intRate) which is placed at the start of the code. Once the value of intRate is known the code then moves down case by case and examines each value to find a match. When a match is found the code moves to the next line within that case to return the prediction corresponding to that rating. So if the rating value was 4, the code finds a match with case 4 and moves to the next line within the case 4 block and assigns the value “Recovery” to RatingText which is the value the function returns.
Once all the possible values of intRating are described, the Select Case and Function statements are terminated by their respective “end” statements. This finishes the block of code, and so the function ends.
Using a UDF in a worksheet
As I type “=Ra” into cell C3, Excel shows the UDF “RatingText” as part of the listing of functions available to this workbook. So once the RatingText function is copied down the range from C3 to C12, the UDF will calculate the return values (the predictions).
The UDF becomes a function just like all the other “Standard” functions available to Excel and its workbooks. Although in its current format it is only available to this workbook.
A more complex UDF
Now we have created a simple UDF, let’s add a 2nd argument to our function and make the code do more that can’t be duplicated in a single cell. Note the change of name to “RatingTextPlus”. Copy and paste the code below into the VBE. You now have a function that uses 2 input arguments, strView and intRate.
The new variable strView uses your description of the economist as to whether they are optimistic, pessimistic or neutral. Based on what you use as a view (you can edit the function to mix and match what is used to describe “views” and “predictions”. The Case statement has an exception for ‘Turning Point’.
In addition, if the view is either missing or neutral the function adds an empty string or ” ” which signifies there is no value to go into that description.
‘Declare the function name = RatingTextPlus.‘RatingTextPlus uses 2 input parameters, intRate and strView.Function RatingTextPlus(strView As String, intRate As Integer) As String ‘ strView is type of economist – Optimist or Pessimist‘ intRate is numerical rating of economic outlook‘Declare strAddText which adds any additional text to the RatingTextPlusDim strAddText As String If strView = “Optimist” Then strAddText = “Strong”ElseIf strView = “Pessimist” Then strAddText = “Severe”Else ‘if the view is neutral or nothing, ‘then there is no additional text, hence strAddText = “” ‘ Note “” means empty string, empty textEnd If Select Case intRate Case 1 RatingTextPlus = strAddText & ” ” & “Depression” Case 2 RatingTextPlus = strAddText & ” ” & “Recession” Case 3 ‘ strAddText doesn’t make sense as prefix to ‘Turning Point’ RatingTextPlus = “Turning Point” Case 4 RatingTextPlus = strAddText & ” ” & “Recovery” Case 5 RatingTextPlus = strAddText & ” ” & “Boom”End SelectEnd FunctionThe second UDF includes the additional variable strAddText which is a string variable that adds an additional amount of text to the final prediction as approporiate.
RatingTextPlus will be added to the list of available functions that can be used in your Excel workbooks.
RatingTextPlus using the additional variable (view). This example uses code without the ‘Turning Point’ exception.
An obvious thing to do in a UDF is some cell formatting – for example green or red backgrounds depending on the result. Alas, Microsoft doesn’t permit that in UDF’s so you’ll have to create separate conditional formatting to match the results of the cell.
Of course, you don’t have to use UDF’s just for decisions or filtering. They are often used to program complex formulas that either can’t be handled by Excel in a single cell or would be hard to debug or edit in that format.
