Excel for ENGINEERS

INTRO TO EXCEL VBA USER DEFINED FUNCTIONS

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 “AstIS” in a cell to calculate the area of reinforcement based on the provisions of IS 456:2000. The arguments to the function are?Moment (Mmt), Grade of concrete (Fck), Grade of reinforcement (Fy), Width of section (B) and the Effective depth of section (D).

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.