excel - VBA函数使用列名而不是单元格地址

Question

有两个 Excel 工作表："Table"和"Formulas".

"Table"sheet 有一张桌子。该表的每一列在第一行都有标题，其他行包含数据和公式。

"Formulas"工作表有一个列地址列表（A、B、C .. 等）和公式示例。

---

想象一下，这个结构：

“表”表

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

“公式”表

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

---

在这里我们看到，“表格”表中 C 单元格的值应该与“公式”表中 C 单元格的值相同。重要的是，“公式”表中的公式具有 SystemName，而不是直接的单元格地址。

因此，我需要编写一个 vba 函数，通过将它们与“公式”表中的公式进行比较来检查“表”表中的所有公式是否正确。此外，每次我更改“表格”表中列的顺序时，它都不应该中断检查功能，因为“公式”表中使用了 SystemNames，而不是直接单元格地址。

我知道，我需要使用 SystemNames 和公式创建一个数组，并以某种方式在每个循环中比较它们。但我不知道如何在 VBA Excel 中做到这一点。

谢谢

标签： excelvbaexcel-formula

Answer 1

我有一个基于此表的解决方案，称为 Table1。解决方案需要 Excel Office365 是动态数组。

在“公式”表上，输入以下公式

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

B2是=TRANSPOSE(Table1[#Headers])

D2是=COLUMNS(Table1)

E2是=SEQUENCE(D2)

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

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

然后使用以下 VBA 代码填写 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

该代码可以处理使用表格引用或单元格引用的公式。最终结果如下。