CODEWITHSUNDEEP
excelvba
Carbo
Carbo

Reputation: 37

MS Excel VBA: Is it faster to use Public Variables or Pass Variables between Subs?

I'm not the most savvy VBA coder so I'd appreciate your advice! I have multiple modules where each of these modules need access to one workbook (Master.xlsm). Is it faster to 1) use a public variable to access this workbook in other modules or to 2) open it in each sub that uses it?

Option #1

I'd set the workbook to a public variable and have it assigned whenever the workbook is opened using Auto_Open.

Public wbk_MASTER As Workbook

Sub Auto_Open()

    Set wbk_MASTER = Workbooks.Open("C:\Master.xlsm")

End Sub

Option #2

Alternatively, in each sub that uses Master.xlsm, I'd just pass it like:

Sub DoSomething(wbk_master as Workbook)

    ' do something

End Sub

That assumes that whatever sub calls this sub would look like:

Sub CallDoSomething()

    Dim wbk_master as Workbook
    Set wbk_master = Workbooks.Open("C:\Master.xlsm")

    Call DoSomething(wbk_master)

End Sub

Note that there'd be multiple subs like DoSomething. If it makes a difference, I'd also like to have variables for important sheets in Master.xlsm and even values from specific ranges.

Personally, I think Option #1 is cleaner but which is faster?

Upvotes: 2

Views: 3273

Answers (2)

SWa
SWa

Reputation: 4363

Dirk has answered your question directly, however how about a more resilient 3rd option?

In a standard module:

Public Property Get SourceWorkbook() as Workbook

    Static wkb_Master As Workbook

    If wkb_Master is Nothing Then Set wkb_Master = Workbooks.Open("C:\Master.xlsm")

    Set SourceWorkbook = wkb_Master

End Property

It can then be used:

Sub test()

    SourceWorkbook.Sheets(1).Name

End Sub

It keeps the scope small and read only, additionally should your project be reset, will reopen the source document - neither of your approaches will do this.

Upvotes: 2

Dirk Reichel
Dirk Reichel

Reputation: 7979

To give a direct answer:

Using a global variable is "faster" in a special not noticeable way because:

What you are looking at is just a pointer to the real object.
This way there is by default no difference in speed for using globalVariable.Sheets(1).Name or subOrFunctionVariable.Sheets(1).Name.
But: Passing it to the sub/function creates a new pointer to it which takes time and uses memory.
Still: It's like blowing up a house and asking which needle makes more noise if dropped. There should never be any noticeable difference for a human being. ;)

Just looking for the use of pointers, using numerical/string/array/non-pointer-objects may create a full copy of the data (if used ByVal instead of ByRef) which may has an impact.

Upvotes: 2

Related Questions