时间戳

首页 > 解决方案 > Excel VBA 工作表范围存储位置最佳实践

excel - Excel VBA 工作表范围存储位置最佳实践

问题描述

我从未找到一个很好的解决方案的问题之一是在 VBA 项目的工作表中定义事物的位置。如果你们中的任何人不得不使用 VBA 和工作表函数构建工作表，那么您就会知道这很重要。运行脚本并意识到保存 f 测试结果的单元格从 F20 移动到 H20 是非常令人沮丧的，因为您必须添加两列数据并且您没有在 VBA 中找到所有范围引用需要更新。

我正在寻找更多有助于系统存储 VBA 范围引用的想法和最佳实践，这些引用易于更新并与不断发展的电子表格保持一致。

我列出了我认为常见的选项。我之前做过前四个，但它们并没有完全做到这一点。

选项1：在每个子的顶部定义和编程位置变量

这通常是发生的事情。在每个主子的顶部，您定义范围接口变量，然后使用完全限定的引用设置它们。对于具有一两个主要功能的快速项目，这通常已经足够了。

当您开始拥有大量嵌套函数时，这将成为一个问题。您是否在函数内部重新定义headerRowand ？还是您每次都传递所有这些信息？多么痛苦。小规模可管理。随着事情的发展，完全变成了意大利面怪物和大海捞针（我们都知道他们通常会这样做）。firstDataRowmoveDataRow()

选项2：定义我自己的 Worksheet 对象

我已经竭尽全力定义自己的工作表对象，该对象具有“headerRow”或“firstDataRow”等附加属性，以便可以将这些属性存储在工作表代码中。然后，当我定义一个工作表对象时，我使用dim srcSheet as myWorksheet而不是dim srcSheet as Worksheet. 这使我可以通过对象模型找到属性。前任：srcSheet.headerRow

当您处理同一工作表的大量重复时，此解决方案非常棒。如果您有六张纸都在做不同的事情并需要不同的属性，那完全是浪费时间。例如：一张表可能是蒙特卡洛控制，而另一张表正在运行 f 检验。我不想为每个人创建一个新的工作表对象。

选项 3：动态查找所有内容

这可能是最强大的，但令人难以置信的耗时。找到一种基于其内容、格式和相对位置来定义每个单元格位置的方法。通常，您尝试引用的单元格的某些特定内容不会改变。编写一个脚本来找到那个东西会让事情变得更容易。但是，这是一项繁重的工作，并非对每个项目都真正可行。对于大多数项目来说，工作量太大了，这是浪费时间。

选项 4：模块级位置变量

将新模块用于与工作表子集交互的主要功能。然后在该模块的顶部将所有位置的东西定义为私有常量。

这适用于中型项目。但是，当您处理多个模块并在处理数据时跨多个工作表移动数据时，您开始必须多次定义相同的常量。

选项 5：创建“位置”模块

这可能会很好，因为所有位置都将在一个地方。我还没有尝试过，但这就是我在这里问这个问题的原因。每当您创建位置参考时，它都会进入位置模块。每个工作表都有一个定义的前缀，用于标识该特定工作表。这样，在子或函数中，您无需在子中设置工作表范围位置，而是调用srcHeaderRow = dataHeaderRow以定义数据标题位于第 5 行，而不必在每次需要使用时重新定义行srcHeaderRow。

还有什么？你尝试过的任何东西都很棒吗？

您还使用了哪些其他方法来保持单元格/范围位置以在 VBA 和工作表之间进行接口？

标签： excelvba

解决方案

我不知道这些是否符合最佳实践，但这是我的做法。

更改工作表的代码名称

为什么dim srcSheet as myWorksheet您可以只更改工作表的代码名称？

更改工作表代码名称

枚举列和标题行

我为每个 Worksheet 创建一个 Enum 来枚举每一列、[First Column]、[Last Column] 和 [Header Row]。将 Enum 成员括在括号中并在其名称中使用特殊字符将创建隐藏成员。请参阅下面的示例代码。如果我以后决定对列重新排序、添加列或重新定位数据，我所要做的就是更新枚举并且代码不会中断。

右键单击对象浏览器可以选择显示对象和枚举的隐藏成员。 显示隐藏成员

枚举可用于 Intellisense

智能感知

样本数据

我使用Excel 示例数据中的前 10 行作为数据。

样本数据

演示代码

Public Enum EnumWSDataColumns
    dcOrderDate = 3
    dcRegion
    dcRep
    dcItem
    dcUnits
    dcUnitCost
    dcTotal
    [_dcFirstColumn] = dcOrderDate
    [_dcLastColumn] = dcTotal
    dcHeaderRow = 4
End Enum

Sub DemoEnum()
    Dim r As Long, c As Long
    Dim value As String * 10
    With wsData
        For r = dcHeaderRow To .Cells(.Rows.count, [_dcFirstColumn]).End(xlUp).Row
            For c = [_dcFirstColumn] To [_dcLastColumn]
                value = .Cells(r, c)
                Debug.Print value; "|";
            Next
            Dim n As Long
            n = ([_dcLastColumn] - [_dcFirstColumn] + 1) * (Len(value) + 1)
            Debug.Print
            Debug.Print String(n, "-")
        Next
    End With
End Sub

输出

输出

推荐阅读