vba - 处理表时如何编写清晰且可维护的代码？

Question

在我的项目中，我经常利用表以及底层的 ListObjects 和 ListColumns。我喜欢它们，因为它们比裸 Range 对象更容易引用和更新。然而，我仍然没有找到一种合理且可维护的方法来处理由许多 ListColumn 组成的多个 ListObject，并在项目中的所有工作表中引用。

假设我有一个工作表((名称)属性设置为“WorksheetA”)，其中包含带有几列(称为 Column1、Column2、...、Column10)的表(称为 TableA)。

现在我想引用另一个工作表代码中的其中一列。我可以这样做:

WorksheetA.ListObjects("TableA").ListColumns("Column7")

现在，直接使用字符串是一种不好的做法，因为它难以维护并且容易出错。

那现在怎么办？

我可以创建专用模块来将我的字符串存储为常量。例如，名为“Constants”的模块:

Public Const TABLE_A As String = "TableA"
Public Const COLUMN7 As String = "Column7"

然后我的引用可以转换为:

WorksheetA.ListObjects(Constants.TABLE_A).ListColumns(Constants.COLUMN7)

但是，该解决方案有一些缺点:

1. 随着每个表和列的添加，常量模块的增长速度会快得离谱。
2. 引用文献本身会增加并且变得不那么可读。
3. 所有工作簿中与表相关的所有常量都被扔进一个大坑。

我可以将常量存储在 WorksheetA 中，并通过公共(public)函数使它们可用，例如:

Private Const TABLE_A As String = "TableA"
Private Const COLUMN7 As String = "Column7"

Public Function GetTableAName() As String
    GetTableAName = TABLE_A
End Function

Public Function GetTableA() As ListObject
    Set GetTableA = WorksheetA.ListObjects(TABLE_A)
End Function

Public Function GetTableAColumn7() As ListColumn
    Set GetTableAColumn7 = GetTableA().ListColumns(COLUMN7)
End Function

这个解决方案实际上解决了上面提到的所有三个问题，但它仍然有点“脏”并且耗时，因为添加新表需要为每列创建一个函数。

您有更好的想法如何处理这个问题吗？

EDIT1(为了清楚起见):假设用户不得更改任何名称(表名或列名)。如果用户这样做，则应归咎于他/她。

EDIT2(为了清楚起见):我使用 Column7 作为列名仅作为示例。我们假设列有更有意义的名称。

Answer 1

这是我的两分钱。我不是一个受过教育的程序员，但我确实得到报酬，所以我想这让我变得专业。

第一道防线是我创建一个类来对表进行建模。我从表中填充类，其他代码甚至不知道数据所在的位置。当我初始化时，我将运行类似的代码

clsEmployees.FillFromListObject wshEmployees.ListObjects(1)

然后在类中，代码如下所示

vaData = lo.DataBodyRange.Value
...
clsEmployee.EeName = vaData(i,1)
clsEmployee.Ssn = vaData(i,2) 
etc

每个工作表只有一个 ListObject。这是我的规则，我从不违反它。任何有权访问工作表的人都可以重新排列列并破坏我的代码。如果我想使用 Excel 作为数据库(有时我确实这样做)，那么这就是我所承担的风险。如果它非常重要以至于我无法冒这个风险，那么我会将数据存储在 SQL Server、SQLite 或 JET 中。

我实际上可以调用 ListColumns 名称，而不是将范围放入数组中。这样，如果有人重新排列列，我的代码仍然可以工作。但它介绍了他们可以重命名这些列，所以我只是用一种风险换取另一种风险。它将使代码更具可读性，因此这可能是您想要进行的交易。我喜欢数组填充的速度，所以这就是我所做的交易。

如果我的项目足够小或者应该直接使用 ListObjects，那么我遵循与任何字符串相同的规则。

• 我在代码中只使用了一次字符串。
• 如果我多次使用它，我会创建一个过程级常量
• 如果我在多个过程中使用它，我会尝试将其作为参数传递
• 如果我无法将其作为参数传递，我会创建一个模块级常量
• 如果这两个程序位于不同的模块中，我首先会问自己为什么两个程序位于不同的模块中并且使用相同的常量。相关的程序不应该在同一个模块中吗？
• 如果这两个过程确实属于不同的模块，那么我尝试将其作为参数传递
• 如果这些都不起作用，那么它确实是一个全局常量，并且是我在 MGlobals 模块中设置的。

如果 MGlobals 占据了超过半个屏幕，那么我就做错了什么，我需要退一步思考我想要完成的任务。然后我创建一个自定义类。