Custom Excel Functions for Engineering Constants

In this post, you’ll learn how to create custom Excel functions to store engineering constants by creating a user-defined function in VBA that can store the yield strength of steel and return it to the spreadsheet.

You’ll also learn how to save your custom functions in an Excel template so they are available whenever you create a new spreadsheet.

Video

Subscribe to EngineerExcel on YouTube

Create Custom Excel Functions for Engineering Constants

User-defined functions can be used for performing calculations, but they can also be used to store constant values.

For example, Excel stores the value of pi as a function. Typing “=pi()” in a cell returns 3.14159265358979.

excel pi function

Through VBA, or Visual Basic for Applications, we can set up our own user-defined functions to store constants like material density, strengths, specific heats, or any other constant value that you reuse in your engineering calculations. These functions can be used just like pi() in formulas or even within other VBA functions.

In the example that follows we’ll create a custom Excel function to store the constant value of yield strength for ASTM A36 structural steel.

Engineering Constant Function Steps

Step 1: Open the Visual Basic Editor

The custom function will be created using VBA or Visual Basic for Applications. To get started, open the Visual Basic Editor (VBE), which is found on the left side of the Developer tab. If you don’t see the Developer tab, you’ll need to enable it.

excel visual basic editor in developer tab

Step 2: Create a New Module to Store the Custom Function

VBA procedures (functions and subroutines) are stored in Modules, so create a new module for the function:

  1. Right-Click the current workbook (in the example below its “VBAProject (Book1)”) in the tree, or any of its branches
  2. Select “Insert”
  3. Select “Module”
insert excel vba module

Step 3: Rename the Module

Giving a module a unique name keeps everything organized. Since this module might be used to store constants in the future, a good name would be “modConstants”.

excel vba module name

Step 4: Create the User-Defined Function

To create a function in VBA, click inside the Code Window and type the word “Function” followed by the name (Sy_A36) and a pair of parentheses. Normally, arguments would go inside the parentheses to be used in a calculation. However, since this function is returning a constant value, there is no calculation, and arguments are not required. The statement “As Double” following the parentheses tells VBA to store the result of the function as a double precision floating point number. After typing the first line and pressing the Enter key, the VBE automatically adds the closing line “End Function”.

excel user defined function

Step 5: Describe the Purpose of the User Defined Function Using Comments

To keep from forgetting exactly what it is this function is doing, and most importantly, the units on the constant value that is being returned it’s a good idea to add some comments. In VBA comments always start with an apostrophe. The apostrophe indicates that the line should not be executed.

excel function comments

Step 6: Complete the Function

To complete the function, set the function name equal to a value. Here the value is 250 (megapascals).

custom excel function

Step 7: Test the Custom Spreadsheet Function

Test out the function on the spreadsheet by typing “=Sy_A36()” in any cell. This returns the value of 250 to the cell.

Don’t forget the parentheses after the function name. Leaving those out will generate a #NAME error.

using custom excel function

Step 8: Save the Workbook as a Template

Recreating these functions would be incredibly tedious, but if they are saved in a template that is used to create every new workbook, they will always be available to you or anyone else you share the spreadsheet with.

To create a template, select File>Save As and select a storage location.

Then set the file type to Excel Macro-Enabled Template.

excel macro enabled template

This automatically sets the directory to Documents/Custom Office Templates:

custom office templates

Then give the template a name and click Save.

MyTemplate

Step 9: Pin the Template to the Jump List for Ease of Re-Use

If you have Excel pinned to your task bar, right click on Excel and the file MyTemplate.xltm will be under “Recent”.

excel jumplist recent

Click the “pin” icon, and the template will now be pinned to the top of the jump list.

excel jumplist pinned

Step 9 (Alternate): Pin the Template to the “Backstage”

If you don’t have Excel in the task bar, you can pin the template to the Backstage view by doingthe following:

  1. Open Excel
  2. Click New
  3. Find the template in the “Personal” Section and pin it
excel backstage

Result

To create any new worksheet with the constants available, just click on the pinned template and a new Excel window will open.

open pinned excel template

Now instead of Book1, the default file name is MyTemplate1:

excel file name

Each new spreadsheet created from this template will have the custom functions available for use in any calculations.

Related Topics

References

Scroll to Top