VBA Macros: Using variables with type modifier

Question

I have declared an integer variable testInt in shorthand notation as follows

Dim testInt%

Is there any difference between using

somevalue = testInt * testInt

versus

somevalue = testInt% * testInt%

In short, is there an advantage of using the type-specifier at every point the variable is referenced?

A similar question http://stackoverflow.com/questions/10890892/use-of-symbol-in-vba-macro :) — Siddharth Rout, Aug 08 '12 at 06:21
Great explanation there! But specifying the int specifier `%` after every variable as done here has no real use does it? — Anirudh Ramanathan, Aug 08 '12 at 07:59
No it doesn't. It is only useful in cases as shown in that link :) — Siddharth Rout, Aug 08 '12 at 08:04

brettdj · Accepted Answer · 2012-08-08T04:38:11.540

5

A quick time test shows they are line ball - which intuitively makes sense

Using Long rather than Integer will be more efficient. See this MSFT Link

I will repeat this with a more accurate API timer

Sub B()
Dim testInt%
Dim somevalue%
Dim lcnt As Long
Dim dbStart As Double
dbStart = Timer()
For lcnt = 1 To 100000000
somevalue = testInt * testInt
Next
MsgBox "Time was " & Timer() - dbStart
End Sub

Sub A()
Dim testInt%
Dim somevalue%
Dim lcnt As Long
Dim dbStart As Double
dbStart = Timer()
For lcnt = 1 To 100000000
somevalue = testInt% * testInt%
Next
MsgBox "Time for type-specified was " & Timer() - dbStart
End Sub

edited Aug 08 '12 at 04:38

answered Aug 08 '12 at 04:31

brettdj

54,857
16
114
177

1

why is Long more efficient? I would've assumed that a) both data types would have hardware support b) VBA would take advantage of this support c) integers are 15 (non-sign) bits, longs are 31, therefore less work to do with an integer. Are any of these assumptions false, or is there something I've missed? – mkingston Aug 08 '12 at 04:41
By the way, I have no idea what the phrase `line ball` means. This might be my ignorance (and I can infer based on the rest of the answer) but it might be worth updating your answer if it's some form of regional slang. Further, I'll just clarify that I'm not against any regional dialect (the opposite, in fact) but other readers may not understand this term either :) – mkingston Aug 08 '12 at 04:44
Ahh. you mean they are inconclusive? :) Also, does everything get cast into long and cast back into int in the result? – Anirudh Ramanathan Aug 08 '12 at 04:54
I had thought line ball meant there was no difference. Anyway, thanks for clarifying with the link- I guess you added that shortly after I read the post, but shortly before I commented (judging by the edit time). – mkingston Aug 08 '12 at 05:19
@DarkXphenomenon I guess "lineball" is an Australian centric term, yes the times are indistinguisable, which is consistent with the variable type already having been declared. :) – brettdj Aug 08 '12 at 05:33

VBA Macros: Using variables with type modifier

1 Answers1