VBA : extensive use of global variables for referencing columns?

Question

语境

我有一个很大的宏，我在其中声明了很多 全局常量 在专用模块中(即，仅包含 Public Const 声明的模块)。

其中大约 100 个全局常量用于分配 列名 到我的主要数据工作表的每一列:

Public Const columnName = "A"
Public Const columnCity = "B"
Public Const columnPhone = "C"
...
Public Const columnColor = "CX"

这让我 引用列 (我的数据工作表，来自其他 10 个模块)使用 .Range(columnColor & l)而不是使用 .Range("CX" & l) (其中 l 显然是行号)。这是很多 更容易编码 (我不需要搜索正确的列)或更新如果我决定在“F”之前插入一列(我只需要更新我的 const 模块而不是其他 10 个代码模块)。

但是，它看起来像使用 .Range(columnCity & l)尤其是 较慢 比使用 .Range("A" & l) . (见下方编辑)

大多数处理器密集型任务是使用大型二维阵列完成的。但是我仍然可能在某些 subs 中调用这些全局列变量 100 000 次，因为我不仅要检查/更新值/公式(我可以在 2D 数组上执行此操作)，还要处理单元格的 .Interior.Color , .Comment.Text ...

问题

使用这样的全局变量 ( Public Const columnName...) 来引用列是多么糟糕的想法？

有一些标准的方法吗？

编辑

正如蒂姆所指出的，我认为我确实花时间改变每个 .Cells(l, 1)至 .Range(columnName & 1)当我重构我的代码以使用列变量时。这意味着:

我的问题可能来自使用 .Range对比 .Cells而不是来自全局变量。

我可能应该重构回 .Cells(l, colIndexName) .

Answer 1

没有“标准”方法可以做到这一点，因此如果性能对您来说是个问题，您应该运行一些性能测试并找出最有效的方法。

例如，使用数字常量和 Cells()似乎比使用 Range() 快两倍:

Option Explicit

Public Const columnName As String = "A"
Public Const colIndexName As Long = 1

Sub Tester()

    Dim l As Long, v, t

    t = Timer
    With Sheet1
        For l = 1 To 300000#
        v = .Range(columnName & 1).Value
        Next l
    End With
    Debug.Print Timer - t   '>> approx. 1.3 sec


    t = Timer
    With Sheet1
        For l = 1 To 300000#
        v = .Cells(1, colIndexName).Value
        Next l
    End With
    Debug.Print Timer - t   '>> approx. 0.6 sec

End Sub

但是，如果这就是你所做的一切，它的速度可能只有两倍 - 一旦你添加其他任务，差异可能会消失。