Excel VBA - where to declare conditionally-used, multiple-use variables

Question

First question, so please be gentle. SO has been extremely helpful to me since I started writing and editing code, so I hope to contribute.

I'm trying to streamline (wherever possible) a program that performs a lot of checks repeatedly, line by line (user input is multiline). The question is specifically thus: for a sub that's run repeatedly (called multiple times by another), should I declare its variables 1) globally - even though they may not actually be used - 2) privately in the beginning of the sub in question, or 3) privately once the program determines they're needed by conditions?

1) would be:

Public VBase as Integer, VTop as Integer, VAWX as Boolean

For 2) and 3), the difference is with VBase and VTop, 2) being:

Sub XVolcanicAsh()
    Dim VBase as Integer, VTop as Integer, VAWX as Boolean
    ' <snip all the code>
End Sub

And 3) being:

Sub XVolcanicAsh()
    Dim VAWX As Boolean 'this one's always used regardless
    For ROW = 5 To NUMLINE + 4
        ' <snip code involving VAWX>
        If Sheets("QA").Cells(ROW, 31).Value = "" Then
            ' the variables won't be used because the cell's blank
        Else
            Dim VBase As Integer, VTop As Integer
            VBase = Mid(Sheets("QA").Cells(ROW, 31).Value, 3, 3)
            VTop = Right(Sheets("QA").Cells(ROW, 31).Value, 3)
            ' <snip other code>
        End If
    Next ROW
End Sub

The question is less for this specific instance than as a template for scrubbing the entire program, since there are a lot of global variables currently declared that only get used in a handful of lines - but the subs containing them are typically called multiple times.

The extent of my understanding here is that 1) will keep the variables in memory until the main sub ends, while 2) and 3) will dump them once this sub ends; and also that 3) may dimension them multiple times. (I am still unclear as to the precise consequences of this, but thus far it's not throwing errors at me.)

Is there a significant difference, performance-wise, between 1), 2), and 3)? And which, if any, is best coding practice? Maybe my search skills aren't what they should be, but neither Google nor SO's internal search has afforded me a proper answer.

I would recommend 2) because of locality

xmojmr
– xmojmr

2016-01-24 17:29:23 +00:00
Commented Jan 24, 2016 at 17:29 — xmojmr
– xmojmr, Commented Jan 24, 2016 at 17:29

Jeremy Thompson · Accepted Answer · 2016-01-24 09:30:49Z

1

In VBA (and VB6) the memory management is done with registers rather than say a generational garbage collector, so you won't notice anything performance wise, what matters is the scope of variables. You're right with how long the variables keep in memory.

A good coding rule (coding language agnostic) is to keep variables within the scope they're used.

For example, in VBA using Modules for global scope is very common, though it can often end up 'Procedural Programming' (spaghetti code). I've seen some huge & messy spreadsheets with thousand line functions all using global variables. Uh.

You want to get into the habit of using classes by encapsulating, refactoring & reusing generic/abstract/polymorphic methods and avoiding the public scope where you can.

Learn to write neat & well structured code before you look into optimisations.

Google this quote: "premature optimization is the root of all evil."

edited Jan 24, 2016 at 9:30

answered Jan 24, 2016 at 9:25

Jeremy Thompson

66.3k38 gold badges225 silver badges344 bronze badges

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

1 Comment

deucexm Over a year ago

I've been refactoring pieces of the code ever since I took over the program (it's still in active use, but no one had been bothering to update it since before I arrived), but ultimately I think the most efficient path in terms of functionality will have to involve rewriting most, if not all of it - because it is very much procedural in its current state. Although this isn't the answer I was looking for, I accepted it because think it's the answer I needed in this case.

Collectives™ on Stack Overflow

Excel VBA - where to declare conditionally-used, multiple-use variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related