CODEWITHSUNDEEP
vbaobjectdictionary
General covariance
General covariance

Reputation: 1

Objects/dictionary for global variables

I am writing a macro that will sit in workbook A.

Workbook A's primary function will be to open workbooks 1-5, and run the macros in those workbooks. However in order to run the macros in workbooks 1-5 I will need to pass inputs to workbooks 1-5 from workbook A.

I would like to define a dictionary or an object in workbook A, which will pass an entire set of inputs to each workbook 1-5. And the macros in workbooks 1-5 will only grab the necessary inputs.

How can I acheive this?

Upvotes: 0

Views: 43

Answers (1)

NeepNeepNeep
NeepNeepNeep

Reputation: 913

I'm wary of the vagueness of "run the macros in those workbooks", but I'll proceed with caution... :)

Let's say you have your workbooks, Master.xls and Child1-3.xls. They all have a reference set to the Windows Scripting Runtime library.

  • Master has a Settings worksheet and some VBA
  • Child1-3 each have two worksheets, DataDump and Settings
  • Child1-3 each have a public module with a VBA proc to be run (name is known)

The Master Settings w/s is populated thusly:

A1 = SettingName      B1 = SettingValue
A2 = Setting1         B2 = Value1
A3 = Setting2         B3 = Value2
A4 = Setting3         B4 = Value3

(note that column C is empty and E always has a trailing \)

D1 = WBName         E1 = WBPath        F1 = ProcName
D2 = Child1.xls     E2 = C:\Temp\      F2 = MaryJo
D3 = Child2.xls     E3 = C:\Temp\      F3 = MaryLou
D4 = Child3.xls     E4 = C:\Temp\      F4 = DaisyLou

There's the setup, now the execution.

Within a proc ("Bob") in Master write something like this:

Public Sub Bob
    Dim dctSetting As Dictionary
    Dim wkbCurrent As Workbook
    Dim rngWkbook  As Range
    Dim rngSetting As Range

    Set rngSetting = ThisWorkbook.Worksheets("Sheet1").Range("A2")
    Set dctSetting = New Dictionary
    Do Until rngSetting = ""
        dctSetting.Add rngSetting.Value, rngSetting.Offset(0, 1).Value
    Loop

    Set rngWkBook = ThisWorkbook.Worksheets("Sheet1").Range("D2")
    Do Until rngWkBook.Value = ""
        Set wkbCurrent = Workbooks.Open(rngWkBook.Offset(0,1) & rngWkBook)
        Application.Run "'" & rngWkBook & "'!" & rngWkBook.Offset(0,2), 
                        dctSetting
        Set wkbCurrent.Saved = True
        wkbCurrent.Close False
        Set rngWkBook = rngWkBook.Offset(1,0)
    Loop
End Sub

Each of Child1-3 has their respective procs (MaryJo, MaryLou, DaisyLou), with the limitation of this approach being that each of those procs has to accept one param (best to make it a dictionary, I guess).

Each child's proc doesn't have to use all three settings, or even any of them. By using a dictionary, you can check for the existence of a setting prior to using it. So in each of the child procs there would be something like:

Public Sub MaryLou(dctSettings as Dictionary)
    Dim strMyValue As String

    If dctSettings.Exists("TheNameOfTheMasterSettingIWantToUse") Then
        strMyValue = dctSettings("TheNameOfTheMasterSettingIWantToUse")

        ' Your code runs here with the populated variable
    End If
End Sub

You'll need to do extra stuff like check for the child w/b at the specified path - at a bare minimum!

Upvotes: 1

Related Questions