Part 2 | VBA programming in Excel
When you encounter a problem that the available functions as in part 1 cannot handle, or can be done but is too complicated. That is when you think of an Excel extension tool, which is programming in VBA (Visual Basic for Application) language.
Don’t be too quick to ignore it when you hear “programming”. I don’t mean to tell you to take a 3-5 year IT course! Moreover, it is not necessary to learn every nook and cranny of VBA programming. Whatever the job requires, we will figure it out, in a practical way. The problems mentioned here are all basic, as the name suggests (Basic). Just need a little bit of Pascal programming skills learned in college and a little patience. Come on, these days, elementary school kids already know how to program, let alone those with an engineering degree in hand 🥰
Search Google
In the spirit of going from easy to difficult, this method does not even require programming knowledge.
- Step 1: Google Excel functions that have been shared online. The familiar way is to use English if Vietnamese is not available.
💎For example, the function that engineers often use to look up tables is One-dimensional and Two-dimensional Interpolation.
Google “Excel 2-dimensional interpolation function”… or translate it to “Excel VBA interpolate 2 dimensions”…
There are many results. We need to try each one, and if we see that it works well and has few errors, then OK.
Next, add these functions to the open XLS file.
- Step 2: Drag the “Developer” tab to the Excel toolbar:
- Step 3: Go to the Developer tab, press the “Visual Basic” button (or shortcut Alt-F11) to open the Microsoft Visual Basic window.
Select the Insert/Module menu to display the Code sub-window. This is where you type VBA commands.
- Step 4: Paste the function code from the website into this Code window. Ctrl-S to save and close the Microsoft Visual Basic window.
- Step 5: Use the new function🍺
Go back to the spreadsheet, type “=” + the new function name (syntax). For example, the 2D interpolation function we just added:
= INTER2D (lookup table area, row values, column values)
When opening the spreadsheet, remember to click the “Enable Macros” button.
🎁 Some Google search functions that are often used in real-life jobs:
- 1-way interpolation: https://wellsr.com/vba/2016/excel/powerful-excel-linear-interpolation-function-vba/
- 2-way interpolation: https://thuthuatxaydung.blogspot.com/2015/07/noi-suy-2-chieu-trong-excel.html
- Function to read numbers into words: https://exceleasy1.blogspot.com/2019/03/code-oc-chu-thanh-so-trong-excel-vba.html
very useful for budgeting, accounting, quotations…
Coding
When you can’t find ready-made food anymore. It’s time to get down to the main content. As said, we only need the basics of the job. A common need is a 2-way loop
💎Example of a floor vibration spreadsheet
At this point, we have to use VBA. We will focus on how to make a loop.
To evaluate the floor vibration, we need to calculate the reaction acceleration
– in the case of Continuous vibration, combined by the Root of Squares (SRSS) method
$$a_{w,rms,e,r}=\frac1{\sqrt2}\sqrt{\sum_{h=1}^H\left(\sum_{n=1}^N\left(\mu_{e,n}\mu_{r,n}\frac{F_h}{M_n}D_{n,h}W_h\right)\right)^2}$$
The formula is quite confusing because it calculates two sums according to two variables: first, take the sum according to the oscillation form n, then take the sum according to the hth harmonic.
🔍For… Next is the VBA statement to calculate this 2-way loop. The inner loop is according to n (the number of oscillation forms is n1), the outer loop is according to h (the number of harmonics H=4)
SRSS = 0 For h = 1 To 4 SoHang(h) = 0 For n = 1 To n1 SoHang(h) = SoHang(h) + muy_e(n, 1) * muy_r(n, 1) * Fh(h) / Mn(n, 1) * D_nh(n, h) * Wh(h) Next SRSS = SRSS + SoHang(h) ^ 2 Next a_rms = 1 / Sqr(2) * Sqr(SRSS)
Note that the sums (SRSS, SoHang (h)) are set to 0 before each loop starts to add up to the sum.
📜Literally translated:
for each value of h running from 1 to H=4, take the square of the sum $\sum_{n=1}^N\left(\mu_{e,n}\mu_{r,n}\frac{F_h}{M_n}D_{n,h}W_h\right)$, get those 4 square terms with each h. The final result $a_{w,rms,e,r}$ is equal to the square root of the sum of those 4 squares.
In which the term Dn,h also depends on the 2 variables n, h, we will calculate it first and turn it into a 2-dimensional array for speed (see below). According to the formula
$$D_{n,h}=\frac{h^2\beta_n^2}{\sqrt{(1-h^2\beta_n^2)^2+(2h\zeta\beta_n^2)^2}}$$
where: $\beta_n= f_p/f_n$
For n = 1 To n1 beta_n(n) = fp / fn(n, 1) For h = 1 To 4 D_nh(n, h) = h ^ 2 * beta_n(n) ^ 2 / Sqr((1 - h ^ 2 * beta_n(n) ^ 2) ^ 2 + (2 * h * Damping * beta_n(n)) ^ 2) Next Next– In the case of Temporary oscillation, it is simpler because there is only 1 loop in the form of n oscillation.
$$a_{w,peak,e,r,n}=2\pi\sqrt{1-\zeta^2}\sum_{n=1}^{n_2}\mu_{e,n}\mu_{r,n}f_n\frac{F_I}{M_n}W_n$$
The coding is similar to the above.
The remaining commands are basic, less complicated:
🔍Create a “Calculate” button
Purpose: because the loop is quite long, it takes up memory and time for the computer to process. So only when we have finished entering all the input data (which includes many lines according to each oscillation mode exported from ETABS). When the user clicks on the button, Excel will perform the calculation according to the Sub Button_Click() program.
How to do it: go to the “Developer” tab> Button. Draw a rectangular button in the spreadsheet, click to edit the text displayed in this button. We see it looks like a button of the windows.
Then right-click, Assign Macro, select the macro name with the suffix “_Click()” representing the command to execute when clicking the button with the mouse.
🔍If… Then… Else
Conditional statements, meaning is identical to Pascal’s If statement: If…Then…Otherwise… VBA syntax only adds End If to end the statement
🔍Dim…As
Declare the variable name. The variable data type includes:
🔍Integer: integer
🔍Double: real number
🔍Variant: any type
To declare an array, just add () after the variable name.
🔍ReDim
Reset declares variables and arrays again. This statement is very powerful compared to Pascal to change the size of the array.
For example:
ReDim D_nh(n1, 4): changes the size of the array D_nh to n1 rows and 4 columns. n1 depends on the calculation result.
🔍Const: Declare a constant
In the example, we use it for the number π. It’s hard to understand why VBA doesn’t have this constant available😔
🔍Function: create any function
The meaning is the same as in Pascal. Basic syntax:
Function function name (variable list) As data type
<Commands>…
function name = formula
End Function
In the example, some formulas are divided into many different cases, we will turn them into functions. Like $W_g, W_b, W_d$ will be functions according to the frequency f (f is a variable)
$$W_g=\begin{cases} 0,5\sqrt{f} &\text{if 1Hz < $f$ < 4Hz} \\[2ex] 1,0 &\text{if 4Hz ≤ $f$ ≤ 8Hz} \\[2ex] 8/f &\text{if $f$ > 8Hz} \end{cases}$$
$$W_d=\begin{cases} 0,4 &\text{if 1Hz < $f$ < 2Hz} \\[2ex] \frac{f}5 &\text{if 2Hz ≤ $f$ < 5Hz} \\[2ex] 1,0 &\text{if 5Hz ≤ $f$ ≤ 16Hz } \\[2ex] \frac{16}f &\text{if $f$ > 16Hz} \end{cases}$$
$$W_d=\begin{cases} 1,0 &\text{if 1Hz < $f$ < 2Hz} \\[2ex] 2/f &\text{if $f$ ≥ 2Hz} \end{cases}$$
In these functions, use the If… Then… Else command to divide according to the cases of f as above.
Some simpler formulas, such as $f_n,M_n,\alpha_h$, we put into arrays for faster results.
🔍Get input from data cells into variables, use the command:
Range (“cell name”).Value
Cell name is taken from Excel cell symbol, for example “B2”, or “B21”, “B60” for a range of cells.
Be careful to check carefully if you edit or insert rows or columns in the spreadsheet, the data may jump. Because the Cell Name is absolute, the value of this cell may have jumped from another cell.
🔍= assignment command, similar to Pascal’s “:=”
🔍Sqr: function to calculate square root
🔍Export the result back to Excel table:
The simple way is to assign Range in the opposite direction (in the example, get back the calculated arms value into cell I4 for continuous case, I6 for temporary case)
…
If you don’t know the command syntax, you can google “VBA syntax” + command name to look it up🥸
Debug
For new programmers, it is certain that they will never write code in one go and get the correct result. There will always be errors, like potholes on the road.
My experience is that after writing a few lines, I will run it to check for errors.
In addition to syntax errors, incorrect formula errors make VBA run, but the results are often not correct with manual calculations (should calculate manually independently to check, otherwise the wrong formula might collapse the house😵).
In the Microsoft Visual Basic window, we will use the “Debug” menu:
– Toggle Breakpoint: make VBA stop at the line that needs to be checked
– Add Watch: check the calculated value of a variable we enter, or
– Quick Watch: see the value of the variable at the text cursor position in the code window
In general, there are many types of errors, only by doing it directly and need time to trap errors. The reward for persistence is that the more you do it, the less mistakes you will make.
Some common mistakes you encounter, you can google “VBA common mistakes”.
Conclusion
Once you have programmed the first one, you will see that there is nothing complicated about meeting the technical needs of the construction profession. Gradually, you will be able to automate things in the profession such as:
– Drawing interactive diagrams of reinforced concrete columns subjected to eccentric bending according to TCVN 5574:2018
– Programming LISP to automatically calculate steel reinforcement in CAD, for the most boring structures such as beams, floors, etc.
– Programming an add-in for Revit to create steel reinforcement drawings and then automatically calculate schedules
– Programming an add-in for Etabs to calculate structures, etc.
If you are interested in any content and want me to write a programming tutorial, please leave a comment👍
🎁 Attached is an excel file according to the example, press Alt-F11 to see the VBA code. This example does not focus on the spreadsheet presentation, please see part 1 on how to present a printed spreadsheet.
Another example in the blog is to create an interactive chart with 1 eccentric column. The VBA code is in the attached excel file.
