I’ve received several requests lately to discuss Excel Macros and VBA (Visual Basic for Applications), which is the programming language built into Excel and other Microsoft Office products.
Excel by itself is so flexible that you can do many things without having to get into VBA. However, if you really want to increase the capabilities of Excel for engineering calculations you will want to learn some level of VBA.
In general, there are two types of procedures in Excel: subroutines and functions. I’ll discuss functions (also known as User Defined Functions or UDF’s) in this post. Next week, I’ll cover subroutines.
What’s an Excel User Defined Function?
A User Defined Function is a procedure (a group of commands) written in VBA that (usually) accepts inputs and returns a result. A UDF cannot modify the formatting of a cell or workbook or move values around on a worksheet.
Basically, UDF’s enable you to create custom functions that act very similarly to the built-in functions that are included in every installation of Excel, such as SQRT, SUM, and MAX.
For instance, below I’ve called a custom UDF that I created called “BendingStress” in a cell to calculate the bending stress in a beam. The arguments to the function are moment, distance from the neutral axis, and the moment of inertia.
Why use a Function?
There are a few different reasons why you might want to consider creating a custom User Defined Function in your worksheet.
The first advantage of functions is that they can clean up your spreadsheets. Instead of cell after cell of sequential calculations, you can combine many successive calculations into a single function. This can significantly clean up your spreadsheets.
User Defined Functions can increase your productivity by allowing you to store and re-use calculations that you use over and over again. Rather than having to recall an equation from memory or looking it up in a reference, you can build it into a UDF and call the UDF instead of retyping the calculation. This has the added benefit of minimizing typing errors.
Finally, User Defined Functions provide you with all of the flexibility of the Visual Basic for Applications language. With UDF’s you can take advantage of loops, expanded logic, and other functionality. This is where you can really increase the capability of you engineering spreadsheets.
How to Create a Function
Let’s walk through creating a User Defined Function. We’ll use the BendingStress function from above as an example.
Before you can start creating a custom function in Excel, you’ll have to enable the Developer tab.
With the Developer tab enabled, open the Visual Basic Editor by pressing Alt+F11 or by clicking Visual Basic on the far left side of the developer tab.
Next, insert a new module in the current workbook by right-clicking on “VBAProject (YourWorkbookName), then selecting Insert>Module.
In the Code Window (the biggest window – in the upper right of the screen) start by typing the following:
When you press enter, the Visual Basic Editor automatically adds “End Function” and you end up with this:
The space between “Function” and “End Function” is where we will write our User Defined Function.
Type “BendingStress” again, an equal sign, and the formula:
That’s it. You just created a User Defined Function. Let’s go back to the worksheet and try to use it.
As soon as you enter the equal sign and the first few letters of the function into a cell, Excel suggests the function. Pressing the Tab key auto fills the name of the function into the cell.
From there, we can enter the three arguments required by the function as either references to cells containing the values or as static values:
After pressing “Enter”, the UDF returns the result of the calculation to the cell:
That’s a pretty brief introduction to User Defined Functions in Excel, but I think you get the idea. Obviously, they can get much more complex in a hurry. So far I’ve created UDF’s to compute integrals, calculate normal load distributions, and even solve differential equations. And I have dozens of other calculations I’d like to wrap up into a UDF when I have some time.
What are some ways that you want to start using User Defined Functions? Let me know in the comments below.
Are you struggling to the find the right solutions to your engineering problems in Excel?
In Engineering with Excel, you'll learn Excel for advanced engineering calculations through a step-by-step system that helps engineers solve difficult problems quickly and accurately.
1 thought on “Intro to Excel VBA User Defined Functions”
Well written intro, Charlie. As to your question, I usually use UDFs for two kind of operations:
1. Pull data from somewhere
2. Perform some lightweight helper functions that are missing in Excel (like SplitArray or JoinArray though they’ve finally been added by Microsoft… if you happen to be on Excel365)
3. Complex, specialised functions (coming from a financial services background, I did a lot of rather nasty things in VBA back in the day), but only if it’s tied to a particular workbook
Otherwise, if it’s used across different workbooks and is a generic function, I tend to write ExcelDna Addins.
So I’m looking forward to reading more about 1 and 2 in particular. Keep up the good work!
Comments are closed.