Does Excel/VBA optimize redundant variables away?

Question

I have a pretty time consuming Excel macro, with a lot of input data. First, i load these data into an array (arr).

Is this code

for n =1 to 1000000
...
arr(n,1)=arr(n,2)+arr(n,4)*arr(n,78) 
...
next n

faster than this

for n =1 to 1000000
...
Sensor_Top=arr(n,2)
Age       =arr(n,4)
Material  =arr(n,78)
output    =Sensor_Top+Age*Material
arr(n,1)  =output
...
next n

?

So in other words, i would like to include unnecessary variable declarations for readability. Does VBA do some sort of JIT compiling to deal with this?

Assuming arr came from a worksheet, why wouldn't arr(n, 1) be precomputed on the Excel worksheet in the first place? Why write any code to do a calculation whose result you can readily grab along with the rest of the data? — Mathieu Guindon
– Mathieu Guindon, Commented Nov 21, 2019 at 17:34
It does not. This was probably unclear in this toy example. In reality the calculations are a lot more involved. (I load the input data into an array, and then populate a different output array with the results. As last step i copy the output array back into the worksheet.) — kiara
– kiara, Commented Nov 21, 2019 at 17:50
Consider putting your actual code up for peer review on Code Review if you're interested in feedback on any/all aspects of the code, including (but not limited to) performance. — Mathieu Guindon
– Mathieu Guindon, Commented Nov 21, 2019 at 18:10
"Is this code […] faster than this […]?" - did you run it and try it? — AJD
– AJD, Commented Nov 21, 2019 at 19:58

TinMan · Accepted Answer · 2019-11-21 17:36:50Z

4

These helper variables will have negligible effect on the the performance. Using an enumeration would be more efficient.

Public Enum NamedArrayColumns
    output = 1
    Sensor_Top = 2
    Age = 4
    Material = 78
End Enum

Sub Foo()
    Rem Some Code

    For n = 1 To 1000000
        arr(n, output) = arr(n, Sensor_Top) + arr(n, Age) * arr(n, Material)
    Next n

End Sub

answered Nov 21, 2019 at 17:36

TinMan

7,8142 gold badges13 silver badges24 bronze badges

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

Comments

Mathieu Guindon · Accepted Answer · 2019-11-21 17:33:01Z

4

Not that I know of, but then again it's not like VBA to P-Code compilation is so completely documented that this kind of thing is easy to see.

I doubt the compiler does any inlining or other kind of optimization, but I could be wrong (VB6 did have optimization settings that did... something).

That said, write code for readability and maintainability.

If your code is too slow, the bottleneck isn't going to be what variables you're using.

As for whether A is faster than B... you have two horses - race them!

edited Nov 21, 2019 at 17:33

answered Nov 21, 2019 at 17:26

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Comments

Tim Williams · Accepted Answer · 2019-11-21 17:35:50Z

3

If you want to improve readability then this can also work:

const c_sensor_top as long = 2
const c_age as long = 4
const c_output as long = 78

for n = 1 to 1000000
    ...
    arr(n,1) = arr(n, c_sensor_top) + arr(n, c_age) * arr(n, c_output) 
    ...
next n

answered Nov 21, 2019 at 17:35

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

Does Excel/VBA optimize redundant variables away?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related