Multiple Excel VBA routines accessing the same objects?

Question

I have the following two subroutines:

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

How do I remove the fndList dictionary from the subroutines and move it to somewhere else so that all subroutines can access it as well? I have two routines that need this dictionary, and don't want to have to maintain two copies of the same code. Is there a special place to put "global" variables in VBA? Thanks.

[edit]

I tried placing the declarations outside the procedure:

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

But I get a compile error: Invalid outside procedure.

Do you want the dictionary to be module-level in scope? Or do you need it to be project-level in scope? Or do you just want to pass the dictionary as a parameter from one subroutine to the other? Perhaps post more of your code so that we can make an informed decision of what you should use. — YowE3K, Nov 12 '17 at 07:55
And does it even need to be a Dictionary, or could it just be a "table" in Excel? — YowE3K, Nov 12 '17 at 07:56
@YowE3K I would think using a Dictionary would be far more lightweight than manipulating Excel objects. — Zev Spitz, Nov 12 '17 at 08:02
I would recommend adding a reference to **Microsoft Scripting Runtime**; this will allow you to write `Dim dict As New Scripting.Dictionary` and will provide Intellisense on the dictionary's properties and methods. — Zev Spitz, Nov 12 '17 at 08:03
1. I updated the question with the second routine. 2. I'm not familiar with the different scopes in Excel. 3. I don't think I can pass the dictionary between subroutines, since they are separate and do not communicate with each other. 4. Are there advantages to using a table versus a dictionary? — posfan12, Nov 12 '17 at 08:04
Microsoft Scripting Runtime sounds interesting, but is probably not necessary since I run the macros from the GUI and not from within a cell formula. — posfan12, Nov 12 '17 at 08:06
The purpose of adding the Microsoft Scripting Runtime reference is to make it easier to write VBA code, and has nothing to do with how you run the macro. As it stands, the VBA editor has no knowledge that `findLst` should point to a `Dictionary`, with methods like `Add` and properties like `CompareMode`. Once you specify the type of `fndList`, then the editor can warn you about trying to call non-existent methods, or wrong arguments passed to the methods. — Zev Spitz, Nov 12 '17 at 08:13

Zev Spitz · Accepted Answer · 2017-11-12T09:59:31.910

2

You can create a module-level variable outside of any Sub or Function:

Private fndList As Scripting.Dictionary

If you need the variable to be accessible to multiple files (AKA modules), then declare the variable as Public:

Public fndList As Scripting.Dictionary

While you can declare variables outside of procedures, you cannot execute statements outside of procedures (you'll get the Invalid outside procedure. error message). Therefore, the initialization code has to be in a third 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

which will initialize the dictionary, if needed.

Then, call the initialization Sub from each of the other subprocedures:

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

References:

edited Nov 12 '17 at 09:59

answered Nov 12 '17 at 08:09

Zev Spitz

13,950
6
64
136

I get an "Compile error: User-defined type not defined" error for `Dim fndList As Scripting.Dictionary`. – posfan12 Nov 12 '17 at 08:54
1

@posfan12 - add a reference to Microsoft Scripting Runtime through the Tools->References menu in the VB Editor – Robin Mackenzie Nov 12 '17 at 09:08
Now I get a "Run-time error '424': Object required" error. It points to `For Each strKey In fndList.Keys()` in the `FixPlatformsWorkbook()` subroutine. – posfan12 Nov 12 '17 at 09:27
I changed `Dim fndList As Scripting.Dictionary` to `Public fndList As Scripting.Dictionary`, and it seems to be working correctly so far. – posfan12 Nov 12 '17 at 09:42
1

@posfan12 I'm guessing that `FixPlatformsSelection` and `FixPlatformsWorkbook` are not in the same VBA file; which is why `Dim` (or `Private`) would be insufficient. – Zev Spitz Nov 12 '17 at 09:53
They are in different modules. Are modules stored in separate files? – posfan12 Nov 12 '17 at 09:55
1

@postfan In VBA, each file contains a single module, and each module is limited to a single file. Also note that I've added a link to the Microsoft docs on Scripting.Dictionary, and on scoping rules in VBA. – Zev Spitz Nov 12 '17 at 10:03
I will make sure to put all the subroutines in the same module, then. Thanks. – posfan12 Nov 12 '17 at 21:35
1

@posfan12 That really depends on your specific case. You may want to organize related groups of `Sub`s / `Functions` into separate modules/files, in which case you would want the variable to be `Public`. – Zev Spitz Nov 12 '17 at 22:55

Multiple Excel VBA routines accessing the same objects?

1 Answers1