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?

Community
  • 1
  • 1
Carbo
  • 37
  • 1
  • 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 – SWa Sep 26 '17 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. – kolcinx Sep 26 '17 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. – jamheadart Sep 26 '17 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](https://stackoverflow.com/q/484635/11683), 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. – GSerg Sep 27 '17 at 08:56
  • 1
    @jamheadart I think I'm finding that this is true as I talk to more and more people. Thanks for confirming that! – Carbo Sep 28 '17 at 14:22
  • @GSerg Thank you for the awesome link re global variables! That makes sense to me. – Carbo Sep 28 '17 at 14:22

2 Answers2

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.

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
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.

SWa
  • 4,343
  • 23
  • 40
  • 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. – Carbo Sep 28 '17 at 14:25
  • 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 – Dirk Reichel Sep 29 '17 at 12:23
  • 1
    @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 ;) – SWa Sep 29 '17 at 12:36