0

I was reading about some VBA performance optimisations and learned that there's a difference between optimising for memory usage versus performance in 32bit VBA applications.

An example of memory optimisation would be using the smallest variable appropriate for your application, eg using an Int instead of a Long of a Float.

For performance it appears to be about matching the variable size in bits to the OS so that it doesn't have to do any type conversions from 8bit to 32bit 'under the hood'.

Can I assume this logic extends into the 64bit version of Excel 365 running VBA7? Ie, is a LongLong (64bit) the most performance optimised approach to working with integers at the cost of memory use versus alternatives such as the Long or Int?

EDIT: There's an answer here that shows the results of testing on 64 bit operating systems and the answer was to use Longs but the optimisation doesn't account for much in terms of time saved (13s over 1 billion operations).

Michael.C
  • 79
  • 10
  • 1
    See https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long on the subject of using Int vs Long in 32-bit VBA – Tim Williams Aug 03 '22 at 00:38
  • Thanks Tim, there's an answer in there that tests the 64 bit OS and shares the results. – Michael.C Aug 05 '22 at 05:07

1 Answers1

0

In 64 bit standard integer size is 32 bits (and signed). Memory addressing is 64 bit (and unsigned). CPU is optimised for both.

The major way programs are optimised for speed versus memory is to unwind functions to inline code for speed if it can. Function calls are slow.

x64 has a faster calling convention than x32 on Windows. In x32 all parameters are passed on the stack, an area of memory so slower than a register. In x64 they are passed in registers where ever possible, ie if it runs out of registers it will pass the rest on the stack..

Lundt
  • 142
  • 1
  • 1
  • 3