Using VBA to Prepare Cell Formulas En Masse With Absolute References
When you’re working with Excel, if you’re using it alongside VBA, you often find yourself working in two different worlds. On the one hand, you have the values and formulas that you enter into the cells on the spreadsheet side. On the other hand, you have programmatic manipulation and logical decision making on the other. Can you use VBA to merge these two worlds?
Why You Should Know How To Prepare Cell Formulas Using VBA
It’s a fair question, why would you want to prepare the cell formulas using VBA? The answer, however, is not simple. There may be millions of use cases for this power, and no two people may ever encounter the same ones.
I recently was tasked with amalgamating the data of a worksheet that would change from month to month. Not only would this worksheet change from month to month, but various columns will be added or removed, and therefore setup month after month would be an arduous pain.
Rather than play the cut and paste game with thousands of lines of values, and risk being wrong, I took it upon myself to write a quick VBA macro to get the job done much more efficiently, and much more accurately.
It’s Easy To Enter Formulas In Cells!
That’s true! It is easy. Even using VBA, it’s pretty easy to set formulas in a cell. Take, for example making cell A1 equals the sum of cell A2 and A3.
Let’s use the following code:
After running this code, you will have the following on your active Worksheet.
So it seems relatively simple, right? It is! But what if we had a long list of values, and we wanted to add them all. Let’s try it. I’ll fill each cell between cells B2 and Z3 with the formula “=ROUND(RAND()*100,0)” to get a series of integers, which we can add in row 1. Then I copy them and paste them as values to prevent them from changing further.
As you can see, we have our dataset, and now we want to add them. Can we use the same code? I’m going to modify the code to apply the formula to the entirety of row 1.
Notice the problem?
Each of those cells is going to be adding cells A2 and A3.
Now, any regular user of Excel will say to this, “Well, I can just copy cell A1 and then paste it on cells B1 to Z1.” They would be entirely correct. However, this is a very simplified situation that you can use to solve increasingly complex problems.
Perhaps you don’t know which columns you’ll be adding.
Perhaps the columns are going to change, or you need to add more than one column to each formula.
Perhaps you are going to programmatically determine the structure of your Worksheet before completing the formulas, and you couldn’t be bothered to figure it out yourself.
If any of these apply to you, as they do me, knowing how to input absolute references to cells from VBA will be a lifesaver. The problem is immediately visible that we do not want to manually enter B, C, D, E, etc. because otherwise, we should fill in the formulas ourselves.
We can bypass this by relying on the Address() function of Cells in VBA as well as combining strings.
I’ve broken up the following code so that it is easier to follow as we assemble our formula. If you look at the comment, you can see the formula as you could write it in a single line.
In the first line within the For loop, we begin our formula with an equal sign. We are required to start our formulas with an equals sign as otherwise, Excel will treat our formula as merely a string, and not perform any calculations.
In the second line of the For loop, we begin by adding the start of our formula. In this case, we are using the Sum function available in worksheets in Excel.
After that, we have four lines, two sets of two lines to create each of our references. On line three of the For loop, we split the Address of our known cell, Cell B1, C1, or *Whatever*1 as identified by the VBA code Cells(1,x) where x is the number of the column.
Once we have the Address, it will be in the format “$A$1” so we use the Split function of VBA to separate that string based on the “$,” and then we take the string stored at position “1” which will be the letter for the column we need to reference.
You might be wondering why we didn’t use the item stored at 0 in the Split instead of 1 since the first item should be “A” right? Unfortunately, “A” would not be the first item. The first item in the Split will be “” as demonstrated in the screenshot below. This result is because the string was split at the “$” and anything before it becomes item one, anything after it, and until the next “$” becomes item two, and so on.
Anyhow, once we have our addresses, as we did initially, line 7 of the For loop will place the new formula into our Worksheet.
Once the Macro has run, you will have the proper formulas in each of your columns! Congratulations!
In this case, it was a simple matter that you could fix without VBA. However, there are many times where programmatically determining the Address of an absolute cell reference will be invaluable to you. I hope I’ve saved you some time today, and happy Excel’ ing!
Check out my Profile and follow me for more about Excel, Programming, and Technology in general. Thanks for reading!