CODEWITHSUNDEEP
arraysvbavariant
Pradnya
Pradnya

Reputation: 11

In which format variant array store values internally

I have amount column which has format of number. I declare 2 dimensional array of type variant, first dimension, I store currency(ex. : GBP, USD) and in other dimension I store amount(eg.: 1234.22 or-1567.69)

myArray(1,0)=GBP
myArray(1,1)= -1234.12
myArray(2,0)=GBP
myArray(2,1)= 1234.12

I am summing myArray(1,1) and myArray(2,1), while summing it is considering format as General/Text instead of Number(which is my column format) and sum is non-zero whereas ideally sum should be 0.

Please suggest, how do I handle this scenario?

Upvotes: 1

Views: 1245

Answers (1)

hnk
hnk

Reputation: 2214

To understand that, you will need to understand exactly what a VARIANT is in VBA and exactly what an ARRAY is.

Arrays:

Starting with arrays, a VBA array is actually not really an array of memory locations but a data structure called SAFEARRAY which includes details as shown in the listing below (source):

typedef struct tagSAFEARRAY {
  USHORT         cDims;
  USHORT         fFeatures;
  ULONG          cbElements;
  ULONG          cLocks;
  PVOID          pvData;
  SAFEARRAYBOUND rgsabound[1];
} SAFEARRAY, *LPSAFEARRAY;

So, you can see that this structure has a pointer to where the data actually is, and what the number of elements are, the number of dimensions and so on and so forth. Because of that VBA is able to ensure that by using its arrays, you will not accidentally mess up some not-to-be-disturbed memory location.

Variants:

With that out of the way, you need to understand what exactly a VARIANT is. VARIANT is also not really a primitive data type but a data structure which makes it able to handle multiple data types easily.

Details of the structure can be found by a simple search but the details are simple:

Total data structure size: 16 bytes

2 bytes: Information about the data type
6 bytes: Reserved bytes (set to 0)
8 bytes: Contain the actual data

Hence when you do a VarType the first two bytes are obtained and that is how the interpreter knows what data type is being used. See here for more details.

So you can understand now what a SAFEARRAY of VARIANT data is.

Finally, the problem in the question::

That has nothing to do with the Variant and everything to do with floating point math. Floating point numbers are not stored exactly as you think they are.

E.g. 2.323 will not be stored as 2.323 but rather as something like 2.322999999999999999999

  • This rounding error will eventually cause trouble (leading to the entire study of stable and unstable methods, etc.) unless you are very careful about the way you handle this quantization of sorts.
  • Some algorithms will be such that the errors cancel out and in some they add-up.
  • So, if you are looking for exact calculations, you need to use a different fixed point data type which might be more suited to your problem domain (e.g. Currency might help in some precision financial calculations)

The Solution: The Currency data type is a 64-bit data type and interally it's like a very long integer scaled by 10,000. So up to 4 decimal places and 15 digits before the decimal can be accurately represented.

Upvotes: 1

Related Questions