访问相同对象的多个Excel例程？

Question

我有以下两个子程序：

Rem Attribute VBA_ModuleType=VBAModule
Sub FixPlatformsSelection()
    Dim fndList As Object
    Set fndList = CreateObject("Scripting.Dictionary")
    fndList.Add "3DO Interactive Multiplayer", "3DO"
    fndList.Add "Nintendo 3DS", "3DS"
    fndList.Add "Ajax", "AJAX"
    fndList.Add "Xerox Alto", "ALTO"
    fndList.Add "Amiga CD32", "AMI32"
    fndList.Add "Amiga", "AMI"
    fndList.Add "Apple I", "APPI"
    fndList.Add "Apple IIe", "APPIIE"
    fndList.Add "Apple IIGS", "APPGS"
    fndList.Add "Apple II Plus", "APPII+"
    fndList.Add "Apple II series", "APPII"
    fndList.Add "Apple II", "APPII"

    For Each strKey In fndList.Keys()
        Selection.Replace What:=strKey, Replacement:=fndList(strKey), _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
    Next strKey
End Sub

Rem Attribute VBA_ModuleType=VBAModule
Sub FixPlatformsWorkbook()
    Dim fndList As Object
    Set fndList = CreateObject("Scripting.Dictionary")
    fndList.Add "3DO Interactive Multiplayer", "3DO"
    fndList.Add "Nintendo 3DS", "3DS"
    fndList.Add "Ajax", "AJAX"
    fndList.Add "Xerox Alto", "ALTO"
    fndList.Add "Amiga CD32", "AMI32"
    fndList.Add "Amiga", "AMI"
    fndList.Add "Apple I", "APPI"
    fndList.Add "Apple IIe", "APPIIE"
    fndList.Add "Apple IIGS", "APPGS"
    fndList.Add "Apple II Plus", "APPII+"
    fndList.Add "Apple II series", "APPII"
    fndList.Add "Apple II", "APPII"

    For Each sht In ActiveWorkbook.Worksheets
    For Each strKey In fndList.Keys()
        sht.Cells.Replace What:=strKey, Replacement:=fndList(strKey), _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
    Next strKey
    Next sht
End Sub

如何从子程序中删除fndList字典，并将其移到其他地方，以便所有子程序也可以访问它？我有两个例程需要这个字典，并且不想维护相同代码的两个副本。在VBA中是否有特殊的地方放置“全局”变量？谢谢。

编辑

我试着把声明放在程序之外：

Public fndList As Object
Set fndList = CreateObject("Scripting.Dictionary")
fndList.Add "3DO Interactive Multiplayer", "3DO"
fndList.Add "Nintendo 3DS", "3DS"
fndList.Add "Ajax", "AJAX"
fndList.Add "Xerox Alto", "ALTO"
fndList.Add "Amiga CD32", "AMI32"
fndList.Add "Amiga", "AMI"
fndList.Add "Apple I", "APPI"
fndList.Add "Apple IIe", "APPIIE"
fndList.Add "Apple IIGS", "APPGS"
fndList.Add "Apple II Plus", "APPII+"
fndList.Add "Apple II series", "APPII"
fndList.Add "Apple II", "APPII"

Rem Attribute VBA_ModuleType=VBAModule
Sub FixPlatformsSelection()
    For Each strKey In fndList.Keys()
    Selection.Replace What:=strKey, Replacement:=fndList(strKey), _
      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
      SearchFormat:=False, ReplaceFormat:=False
    Next strKey
End Sub

但是我得到了一个编译错误:无效的外部过程。

Answer 1

您可以在任何Sub或Function之外创建模块级变量。

Private fndList As Scripting.Dictionary

如果需要变量可以访问多个文件(AKA模块)，那么将变量声明为Public

Public fndList As Scripting.Dictionary

虽然可以在过程之外声明变量，但不能在过程之外执行语句(您将得到Invalid outside procedure.错误消息)。因此，初始化代码必须位于第三个Sub中。

Sub InitDictionary
    If Not fndList Is Nothing Then Exit Sub
    Set fndList = New Scripting.Dictionary
    fndList.Add "3DO Interactive Multiplayer", "3DO"
    fndList.Add "Nintendo 3DS", "3DS"
    fndList.Add "Ajax", "AJAX"
    fndList.Add "Xerox Alto", "ALTO"
    fndList.Add "Amiga CD32", "AMI32"
    fndList.Add "Amiga", "AMI"
    fndList.Add "Apple I", "APPI"
    fndList.Add "Apple IIe", "APPIIE"
    fndList.Add "Apple IIGS", "APPGS"
    fndList.Add "Apple II Plus", "APPII+"
    fndList.Add "Apple II series", "APPII"
    fndList.Add "Apple II", "APPII"
End Sub

这将初始化字典，如果需要的话。

然后，从其他每个子过程调用初始化Sub：

Sub FixPlatformsSelection()
    InitDictionary

    For Each strKey In fndList.Keys()
    '...
    Next
End Sub

Sub FixPlatformsWorkbook()
    InitDictionary

    For Each sht In ActiveWorkbook.Worksheets
        '...
    Next sht
End Sub

参考文献：

• Scripting.Dictionary
• 理解VBA的范围和可见性