In which format variant array store values internally

Question

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?

How do you know "it is considering format as General/Text"? Also probably see http://stackoverflow.com/q/588004/11683. — GSerg, Jul 11 '14 at 06:54
The problem has nothing to do with the variant, and everything to do with the way floating point math is handled. `Double` is rounded internally to `double floating point precision` and unlike certain data types may not handle decimals exactly the way you'd like it (Detailed answer below). — hnk, Jul 11 '14 at 12:31
I know that it is getting converted to general/Text as when i sum them in excel by just selecting all currency values and having coulmn format as 'Text/general' it gives same result i.e non zero something like 7.41056E-11 but in case of Number it is giving sum as 0 — Pradnya, Jul 11 '14 at 13:22
`7.41056E-11` [is zero](http://stackoverflow.com/q/588004/11683). In both cases the calculated sum is the same, but because inherent property of the `Number` textual format in Excel is number of decimal places, Excel rounds that number *for the purpose of displaying*. — GSerg, Jul 12 '14 at 19:31
@GSerg `7.41056E-11` is not zero. I could very well be the correct result of any expression. — z̫͋, Jul 14 '14 at 08:02
@z̫͋ It's `0.0000000000741056`, logically it can't be a result of adding up financial amounts that have two decimal positions. Excel does display this when calculating floating point numbers given that no decimal format has been set for diaplying purposes. — GSerg, Jul 14 '14 at 08:29
I solved this issue by using CCur function such that while summimg value should get converted to Currency. And due to this sum is calculated correctly. — Pradnya, Jul 21 '14 at 12:50

hnk · Answer 1 · 2014-07-11T12:48:15.967

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.

The text before "Finally, the problem in the question" has absolutely no relation to the problem and should be removed. — GSerg, Jul 12 '14 at 19:32
Tks for the feedback @GSerg. actually searching on google for **how are vba arrays stored internally?** shows this as the second most relevant link, so for the broader community it might be relevant, also, the OP asks about the internal structure of variant arrays, albeit not knowing that this isn't the cause of the issue, so I tried to address both - the question and the problem. Also, regd. helping the poster, I put a succinct reason in the comments and the detailed answer above. either case if it's adding value to SO and the programming community, I'll be happy to take it off. — hnk, Jul 13 '14 at 01:24

In which format variant array store values internally

1 Answers1