CODEWITHSUNDEEP
vbaexceloptimizationtypes
Greedo
Greedo

Reputation: 5543

Least memory intensive way of storing small integers in VBA

Occasionally I will store the state of some system as an integer. I often find myself using small values for these states (say 1-10), since the system is relatively simple.

In general, what's the best declaration for a variable which stores small positive integers - where best is defined as fastest read/write time & smallest memory consumption? Small is here defined as 1-10, although a complete list of integer storing methods and their ranges would be useful.

Originally I used Integer as on the face of it, it uses less memory. But I have since learned that that is not the case, as it is silently converted to Long

I then used Long for the above reason, and in the knowledge that it uses less memory than Double

I have since discovered Byte and switched to that, since it stores smaller integers (0-255 or 256, I never remember which), and I guess uses less memory from it's minute name. But I don't really trust VBA and wonder if there's any internal type conversions done here too.

Boolean I thought was only 0 or 1, but I've read that any non-zero number is converted to True, does this mean it can also store numbers?

Upvotes: 0

Views: 318

Answers (2)

user1196549
user1196549

Reputation:

The smallest chunk of memory that can be addressed is a byte (8 bits).

I cannot guarantee that VBA Bytes are stored as bytes in all cases, but using this type you are on the safest side.

By the way, the largest byte value is 11111111b, i.e 255d. The value 256d is 100000000b which requires 9 bits.

Also note that using Bytes every possible time might be unproductive as it can have a cost in terms of running time, if numerical conversions are required, while the spared memory space may be insignificant.

Except for very special applications, this kind of micro-optimization is of no use.

Upvotes: 4

Pᴇʜ
Pᴇʜ

Reputation: 57683

Originally I used Integer as on the face of it, it uses less memory. But I have since learned that that is not the case, as it is silently converted to Long

That's right there is no advantage in using Integer over Long because of that conversion, but Integer might be necessary when communicating with old 16 bit APIs.
Also read "Why Use Integer Instead of Long?"

I then used Long for the above reason, and in the knowledge that it uses less memory than Double

You would not decide between Long or Double because one uses less memory. You decide between them because …

  • you need floating point numbers (Double)
  • or you don't accept floating point numbers. (Long)

Deciding on memory usage in this specific case is just a very bad idea because these types are fundamentally different.

I have since discovered Byte and switched to that, since it stores smaller integers (0-255 or 256, I never remember which), and I guess uses less memory from it's minute name. But I don't really trust VBA and wonder if there's any internal type conversions done here too.

I don't see any case where you use Office/Excel and run into any memory issues by using Long instead of Byte to iterate from 1 to 10. If you need to limit it to 255 (some old APIs, whatever) then you might use Byte. If there is no need for that I would use Long just to be flexible and not run into any coding issues because you need to remember which counters are only Byte and which are Long.
E.g. If I use i for iterating I would expect Long. I see no advantage in using Byte for that case.

Stay as simple as possible. Don't do strange things one would not expect only because you can. Avoiding future coding issues is worth more than one (or three) byte of memory usage. Sometimes it is worthier to write good human readable and maintainable code than faster code especially if you can't notice the differences (which you really can't in this case). Bad readable code always results in errors or vulnerabilities sooner or later.

Boolean I thought was only 0 or 1, but I've read that any non-zero number is converted to True, does this mean it can also store numbers?

No that's wrong. Boolean is -1 for True and 0 for False. But note that if you cast e.g. a Long into Boolean which is not 0 then it will automatically cast and result in True.

But Boolean in VBA is clearly defined as:

 0 = False
-1 = True

Upvotes: 5

Related Questions