使用VBA中的默认数组填充Excel
我有一个WBS (工作分解结构)，包含多个行(组大纲的顶层)，每个顶层行都是一个活动。活动的直接之下是所涉及的角色。
根据顶层活动的值(例如，“计划”)，将根据另一个工作表(“默认”选项卡)中的相关表中的值填充下面级别的单元格。
目前，活动下的行(对应于角色)正在执行丑陋的索引/匹配查找(乘以25个角色)，可能会使电子表格陷入停顿。
我认为解决这个问题的方法是使用角色默认值表，将其放入持久数组中，并一次又一次地使用数组中的值，就像用户在顶级活动中所做的那样。我只是不知道如何使数组持久(这样VBA就不会在用户更改单元格时重新填充它)。如果角色默认值表中的值发生了变化，我可以使用工作表OnChange来处理，所以这不是一个问题。
第3行“活动1”是活动行与组大纲折叠后的样子。
行4-9是活动行与组大纲展开后的样子，显示了底层角色。
对于每个角色，这是另一个选项卡上的表，用于查找WBS选项卡上相应的活动/角色单元格中的值。
回答 1
Stack Overflow用户
发布于 2016-08-29 21:23:30
每当需要查找时，我都支持使用Dictionary对象。在下面的解决方案中，我使用嵌套字典返回顶级和活动的组合。(注:我试着尽我所能地理解你的业务需求，但我确信我没有做到这一点。我还假定了一些VBA知识以上的初学者水平。如果您有后续问题，请询问，我们将尽力帮助)。
首先，创建一个新模块来保存全局可用的Dictionary。这不能是Worksheet模块。(在VBE中，转到插入->模块)。在模块的最顶端，在创建子例程之前，声明一个公开可用的Dictionary。
Public oDictWbs As Object我们只想要这个字典的一个实例，所以我喜欢使用类似于Singleton的模式，如果已经创建，它返回一个Dictionary，如果没有，则创建并返回一个新的。(注意:我将返回一个新字典到RefreshWBS的例程计算出来，以便它可以用于根据业务规则创建一个新字典。因此，例如，在默认工作表OnChange事件中，您可以调用RefreshWBS代码重用总是很有趣的)。
Private Function GetWBS() As Object
If Not oDictWbs Is Nothing Then
Set GetWBS = oDictWbs
Exit Function
End If
Set GetWBS = RefreshWBS()
End Function
Private Function RefreshWBS()
Dim sDefault As Worksheet
Dim rTopLevels As Range
Dim rActivities As Range
Dim rIterator As Range
Dim rInnerIter As Range
Set oDictWbs = Nothing
'Both variables below establish the range that stores the fixed info (the default worksheet)
'Instead of hard coding in the range, create your own logic based on your needs and rules
Set sDefault = Sheets("Default")
Set rTopLevels = sDefault.Range("B1:C1")
Set rActivities = sDefault.Range("A3:A4")
Set oDictWbs = CreateObject("Scripting.Dictionary")
For Each rIterator In rTopLevels
If Not oDictWbs.exists(rIterator.Value) Then
Set oDictWbs(rIterator.Value) = CreateObject("Scripting.Dictionary")
End If
For Each rInnerIter In rActivities
If Not oDictWbs(rIterator.Value).exists(rInnerIter.Value) Then
oDictWbs(rIterator.Value)(rInnerIter.Value) = sDefault.Cells(rInnerIter.Row, rIterator.Column)
End If
Next rInnerIter
Next rIterator
Set RefreshWBS = oDictWbs
End Function最后，我们创建一个可以从工作表内部访问的函数，允许用户访问WBS字典中的信息。您可以在Excel单元格中输入类似于=GetWbsActivityTime(B1, A4)的函数，假定单元格B1包含顶级描述符，A4描述活动。只要该值在字典中，它就会返回与它相关的值。
Function GetWbsActivityTime(sTopLevel As String, sActivity As String) As Variant
Dim oDict As Object
Set oDict = GetWBS()
If Not oDict.exists(sTopLevel) Then
GetWbsActivityTime = CVErr(xlErrRef)
Exit Function
End If
If Not oDict(sTopLevel).exists(sActivity) Then
GetWbsActivityTime = CVErr(xlErrRef)
Exit Function
End If
GetWbsActivityTime = oDict(sTopLevel)(sActivity)
End Function我知道这是一个很大的吸收，所以回顾它，并让我知道任何问题或怪癖，我可以帮助。另外，如果我完全忽略了练习的重点，让我知道，我会看看我们能否挽救部分解决方案。
https://stackoverflow.com/questions/39212953
复制相似问题
腾讯云开发者
