INTRODUCTION
If you haven’t done so see Part 1: Excel VBA for Engineers for an introduction on VBA and setting up the VBA development environment.
REBAR CUSTOM FUNCTIONS
Below are the VBA custom functions I’ve develop. This is repeated here with modifications from Part 1: Excel VBA for Engineers for your convenience. See Figures 1-3 and the embedded video for examples of how these custom rebar functions can be utilized in Excel.
Syntax
=RebarArea(value) – returns area (in2)
=RebarDiameter(value) – returns the diameter (in)
=RebarWeight(value) – returns the weight (lb/ft)
The RebarArea, RebarDiameter, and RebarWeight syntax has the following argument:
value – Rebar id. This is a string (i.e. #4, #5…….#18) or a reference to a cell holding a string.
Examples:
=RebarArea(“#4”)
=RebarDiameter(A10)
CUSTOM EXCEL FUNCTION PROGRAMMING
Now will dive into the VBA code. For simplicity I created one module for this blog post. You can download the module here: VBA_Part2_Blog.bas. For production I typically move functions such as IsArrayAllocated(Arr As Variant) As Boolean into a “utility” module because of its applicability on other projects.
Figure 4 shows the Declarations for the module. Rebar data is stored in an array of arrays called RebarTable. These array types are also called jagged arrays. Each rebar array (i.e. BarNo3, BarNo4, and etc.) holds the rebar data – area, diameter, and weight. We’ll have more on this later. Note that these arrays hold Variants. A Variant in VBA is a variable datatype that can be any datatype. For more information on Variants see this reference. The BarNo arrays could have been declared to hold Doubles instead of Variants, but I came across a conflict when attempting to use the rebar custom functions with Combo Boxes.
Note that all variables in Figure 4 are declared as Private except for the array RebarTable which is Public. Public variables are available in other modules. I use RebarTable for design functions and subroutines that are defined in other modules. For more information on variable declarations see this reference.
Figure 5 shows one of the three Custom Rebar Functions. The other functions are coded similarly. The RebarArea function is declared Public so it will be available in the Excel Worksheets. See Figure 1 for an example. This function passes RebarId which is a String and returns the area of the bar as a Double. Figure 3 shows the call. The function can be called with a cell reference as shown in Figure 2. If the cell doesn’t hold a valid String (#4, #11, etc.) the return value will be 0. My production code returns an Error, but this feature is beyond the scope of this blog post.
You will notice at the beginning of the function I call IsArrayAllocated. This function checks to see if the RebarTable has been initialized with the rebar data. See Figure 5 for the code. If the RebarTable hasn’t been initialized I call the function InitializeRebarTable which is shown in Figure 6. I use Select Case to match the RebarId with the correct RebarTable array indexes. For more information on Select Case see this referernce. Here you see why I defined the Id and Prop Enums shown in Figure 4. For more information on Enums see this reference. Array calls with integer indexes can be error prone as shown below:
RebarTable(Id.No3)(Prop.Area) is equivalent to RebarTable(0,1)
The InitializeRebarTable subroutine populates the BarNo arrays and RebarTable. For more information on Arrays see this reference. One thing of note in this subroutine is the line- continuation underscore character ( _ ). The underscore proceed by a space allows lines of code to broken into multiple lines.
The IsArrayAllocated function was found at this website. This website is one of the better Excel VBA references.
