What is VBA?
Visual Basic for Applications (VBA) is the standard programming language for Microsoft Office products including Excel. VBA allows the user to automate Excel tasks by writing macros, subroutines, and functions
Why use VBA?
- Automate repetitive tasks
- Simplify complex Excel equations with Basic language code
- Reduce spreadsheet errors
- Make spreadsheets more maintainable
- Automate tasks in software programs such as SAP2000 or AutoCAD
Note: This post uses Excel 2013 but the concepts will apply to any recent version of Excel.
Do you use the built-in Excel LOOKUP functions? Do you use these functions to extract reinforcement data (area, diameter, weight, and etc.) from a range of cells? Yuck! I can never remember the syntax for the LOOKUP functions. Over the years I have made many errors with these convoluted functions. Not only are they error prone, but creating a range of reinforcement data is another possible source of errors.
Here are the functions that bridgeautomation uses to enter reinforcement data into Excel replacing the error prone LOOKUP functions.
RebarArea, RebarDiameter, and RebarWeight returns the Area, Diameter, and Weight of US reinforcement data respectively. The base units are inch and lb.
The RebarArea, RebarDiameter, and RebarWeight syntax has the following argument:
value – Rebar id. This is a string (i.e. #4, #5…….#18). This parameter must be enclosed in quotation marks (i.e. RebarArea(“#4”).
Start typing “Rebar” in a cell and a drop down box opens making it easy to remember the function name.
No LOOKUP tables or typing in reinforcement data into cells!
Verify that the DEVELOPER menu item is visible.
If the DEVELOPER menu item is not visible navigate to the FILE / OPTIONS dialog box.
At the Options dialog box select Customize Ribbon and check the Developer checkbox and then press the OK button.
Hello World Example
Create a new Excel worksheet and save as an Excel Macro-Enabled Workbook (*.xlsm). From the main menu select the Developer menu item and then click on Visual Basic.
Select Insert and Module.
In the Project Tree to the left pane of the dialog box below click on Module 1. Enter the following code as shown below. You can either type the code or copy and paste from code text box below.
Source code for the HelloWorld function is below.
Public Function HelloWorld(flag As String) If flag = 0 Then MsgBox ("Hello World From Bridgeautomation") HelloWorld = "0 passed-Hello From Bridgeautomation" Else HelloWorld = flag + " passed-Hello World From Bridgeautomation" End If End Function
HelloWorld is a simple example function that shows how to pass parameters, return values, and use message boxes.
- A 0 value parameter opens a message box and returns a string message to the cell
- Any other numeric value parameter just returns a string message to the cell
- Non-numeric value parameter returns a #Value! error
The HelloWorld function syntax has the following argument:
value – Any numeric value.
The graphic below shows how to use the HelloWorld function. In the example below cell C3 is referenced for input. Enter the integer 100 as a parameter would have worked as well (=helloworld(100)).
The graphic below shows the final results in cell C5 for the input value of 3 in cell C3.
- Microsoft, Excel VBA – https://docs.microsoft.com/en-us/office/vba/api/overview/excel
- Microsoft, VBA Concepts – https://docs.microsoft.com/en-us/office/vba/library-reference/concepts/getting-started-with-vba-in-office