VBA Type Declaration Character 1

[plip1978]

Hi,
I am looking at ways of optimising my vba code.
I've noticed that a collegue uses type declaration characters on his numbers within his code, e.g. value = 10# - otherValue
Can anyone tell me if there is any benefit to be gained from this approach? What is the default type of numbers used in vba expressions?
For example, would there be any performance gain from doing this:
value = 10% - otherValue over the statement above?
Thanks in advance.
Phil.

 

[kjv1611]

Since your coworker is doing this already, have you tried asking him/her? I'd suggest that'd be the best place to start, since it's their code you're looking at.

Then come back for further clarifications - that's just what I'd do if I were in your shoes.

 

[plip1978]

I take it you don't know the answer then...

 

[strongm]

Oh dear ...

 

[kjv1611]

[ROFL2]

I take it you don't wanna ask, then.

 

[plip1978]

Haha, possibly...

 

[kjv1611]

Well, I've used types some, but have generally not needed them. One place I've gone to for reference on that one a few times is Chip Pearson's website.

So here are a few links in that direction:

http://www.cpearson.com/excel/vbe.aspx

(more general, but mentions a WHOLE SLEW of good stuff.)

Not same thing, but might be worth looking at:

http://www.cpearson.com/excel/classes.aspx

You know what, just use this search, it'll specifically be for his site, you can pick out the pieces from there.

If you run into a specific problem, post back... or if it's specific enough, perhaps start another thread just for that issue.

 

[plip1978]

Ok, thanks very much.

 

[strongm]

>What is the default type of numbers used in vba expressions

The expression evaluator, working from left to right, tries to use the data type that requires the least storage to hold the number. This sometimes leads to overflow because it also examines the whole expression to deetrmine the smallest datattype to use for the evaluation

So, if it sees

dim x as long

x = 1024 * 1024

It uses an Integer for both the 1024 values, examines the expression, sees that there is nothing bigger than an Integer and assumes it can use an Integer for the intermediate calculation (before assigning to x)

You can use a type declaration character to stop the expression evaluator from doing this - you just need to use it on one of the values. So either

x = 1024& * 1024

or

x = 1024 * 1024&

will prevent the expression evaluator from overflowing since it now uses a long for the intermediate result.


>Can anyone tell me if there is any benefit to be gained from this approach?

Well, one benefit is given above ...
Speed benfits? No, not that I am aware of

Any others? Erm, there's a fairly obscure benefit when handling hexadecimal

Theoretically using VB's &H construct for hexadecimal number there are a number of numbers we cannot represent, because hex numbers are interpreted as signed integers. We can use a type declaration character to allow it to be treated as an unsigned integer (in actuallity it is a con, what we really do is get it to be interpreted as a signed long, but ity has the necessary effect)

For example:

MsgBox &HEFFF
MsgBox &HEFFF&