CODEWITHSUNDEEP
vbaexcel
Citanaf
Citanaf

Reputation: 464

VBA Memory Usage: Declaring the same variable in diferrent subs vs publicly in module

Quick question about memory usage as I would like to always be writing my code optimally. I looked around but wasn't finding my question exactly.

I have several worksheets in my workbook that I usually create when I start a new excel/vba project. Those may be things like "LIBRARY" and "DATA". At the start of each sub I will declare:

Dim libWS as Worksheet
Dim dataWS as Worksheet
Set libWS = Worksheets("LIBARARY")
Set dataWS = Worksheets("DATA")

I do this for each sub that will utilize those worksheets. My question is, would it be more optimal or just the same to declare those worksheets publicly in the module and use a workbook open macro to initialize them.

Module 1:

public libWS as Worksheet
public dataWS as Worksheet

This Workbook:

Private Sub Workbook_Open()
    Set libWS = Worksheets("LIBARARY")
    Set dataWS = Worksheets("DATA")
End Sub

Optimal meaning in terms of both speed/memory usage.

Thanks

Upvotes: 0

Views: 699

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

I think both options are unnecessary as you can directly refer to the worksheets as objects in the Excel object model. If you rename the objects (not the worksheet names) to DATA and LIBRARY:

enter image description here

Then you can refer to them in code like this:

enter image description here

Upvotes: 2

Related Questions