VBA function to use column name instead of cell address

Question

There are two Excel sheets: "Table" and "Formulas".

"Table" sheet has a table. Each column of this table has title in the first row, the other rows contain data and formulas.

"Formulas" sheet has a list of column addresses (A,B,C.. etc) with formula examples.

---

Imagine, this structure:

"Table" sheet

| A          | B              | C |
| -----------| ---------------|---|
| FirstNumber| SecondNumber   |Sum|
| 1          | 2              |3  |

"Formulas" sheet

| A        | B              | C                               |
| ---------| ---------------| --------------------------------|
| Cell     | SystemName     |Formula                          |
| A        |  FirstNumber   |                                 |
| B        |  SecondNumber  |                                 |
| C        |  Sum           |'=A{FirstNumber}+B{SecondNumber }|

---

Here we see, that the value of C cell in "Table" sheet should be the same as the value of C cell in "Formula" sheet. It is important, that the formula from "Formulas" sheet has SystemName, not a direct cell address.

So, I need to write a vba function that check all formulas in "Table" sheet are correct after I save my document by comparing them with formulas in "Formulas" sheet. Additionally, every time I change the order of the columns in "Table" sheet it should not interrupt the check function because SystemNames are used in "Formulas" sheet, not direct cell addresses.

I understand, that I need to create an array with SystemNames and formulas and somehow compare them in for each loop. But I have no idea how to do it in VBA Excel.

Thank you

Answer 1

I have a solution based on this table called Table1. The solution requires Excel Office365 is dynamic arrays.

On the Formulas sheet, enter the following formulas

A2 is =SUBSTITUTE(G2#,"2","")

B2 is =TRANSPOSE(Table1[#Headers])

D2 is =COLUMNS(Table1)

E2 is =SEQUENCE(D2)

F2 is =IFERROR(TRANSPOSE(FORMULATEXT(OFFSET(Table!A2,0,0,1,D2))),"")

G2 is =SUBSTITUTE(ADDRESS(2,E2#),"$","")

Then use the following VBA code to fill in column C.

Sub ConvertFormula()

Dim i As Long, j As Long, count As Long
Dim newFormula() As Variant, celladdr() As Variant, letter() As Variant, systemName() As Variant

count = Range("D2").Value2
newFormula = Range("F2#").Value2
celladdr = Range("G2#").Value2
letter = Range("A2#").Value2
systemName = Range("B2#").Value2
For i = 1 To count
    If newFormula(i, 1) <> "" Then
        newFormula(i, 1) = "'" & newFormula(i, 1)
        For j = 1 To count
            newFormula(i, 1) = Replace(newFormula(i, 1), celladdr(j, 1), letter(j, 1) & "{" & systemName(j, 1) & "}")
            newFormula(i, 1) = Replace(newFormula(i, 1), "[@" & systemName(j, 1) & "]", letter(j, 1) & "{" & systemName(j, 1) & "}")
        Next j
    End If
Next i
Range(Cells(2, 3), Cells(count + 1, 3)) = newFormula

End Sub

The code can handle formulas that use table references or cell references. The final result is below.