2

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?

Improve this question
6
  • It's best practice to keep scope as small as possible, option 2 is the better way of doing it. There won't be any noticeable difference is speed Commented Sep 26, 2017 at 16:44
  • I think that if you need to use the master in multiple subs, it is better to open/set it once, keep it opened, and access the data when needed. I vote for #1. Using public scope will keep the object accessible later. I also agree with SWa, but best practice is a recommendation, not a "one fit all" solution. The #1 will probably alocate more memory to your xlsm, but will require less opening/closing, so less processor time and faster from this perspective. Commented Sep 26, 2017 at 16:49
  • I began my VBA career doing option 1, but I found after a while as my classes developed, my code pages became novels and my projects became huge, that it was much better practice to pass variables in to routines as per option 2 - but mainly because I can pass ANY workbook to a routine and don't have to open and define lots of workbooks upon opening. Just feels easier to keep track of. Commented Sep 26, 2017 at 17:21
  • The title seems to ask a different question than the question itself. The answer to "public variables vs passing variables" is that, unless there is a logical reason to have a global variable, you should not do it, and that if the price of passing a parameter to a function is important for your project, VBA is the wrong language of choice. The answer to whether it is faster to cache a workbook than to open it every time anew is that obviously caching is much faster, but you must decide first if keeping it open at all times is correct for you. Commented Sep 27, 2017 at 8:56
  • 1
    @jamheadart I think I'm finding that this is true as I talk to more and more people. Thanks for confirming that! Commented Sep 28, 2017 at 14:22

2 Answers 2

Reset to default
2

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.

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

3 Comments

I'll have to test this out but I really like this answer! I like that it avoids using global variables and still keeps things tidy.
Still one downside remains: You will lose the (unnoticeable) speed advantage of the "public variable"-way of doing it, as every interaction checks for it to be nothing which causes a(nother unnoticeable) delay. That in mind, it will be (again unnoticeable) faster to set a local variable inside the sub/function to the property which uses (yeah.... unnoticeable) more memory to store a pointer :P
@DirkReichel If I'm concerned about the overhead of a pointer to an object, I shouldn't be using Excel VBA in the first place ;)
2

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.