0

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.

Improve this question
8
  • 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. Commented Nov 12, 2017 at 7:55
  • And does it even need to be a Dictionary, or could it just be a "table" in Excel? Commented Nov 12, 2017 at 7:56
  • @YowE3K I would think using a Dictionary would be far more lightweight than manipulating Excel objects. Commented Nov 12, 2017 at 8:02
  • 1
    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. Commented Nov 12, 2017 at 8:03
  • 1
    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. Commented Nov 12, 2017 at 8:13

1 Answer 1

Reset to default
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:

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I get an "Compile error: User-defined type not defined" error for Dim fndList As Scripting.Dictionary.
@posfan12 - add a reference to Microsoft Scripting Runtime through the Tools->References menu in the VB Editor
@posfan12 I'm guessing that FixPlatformsSelection and FixPlatformsWorkbook are not in the same VBA file; which is why Dim (or Private) would be insufficient.
@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.
@posfan12 That really depends on your specific case. You may want to organize related groups of Subs / Functions into separate modules/files, in which case you would want the variable to be Public.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.