How long is long in vba

Question

I am writing a vba code for Excel 2016 on Mac that will communicate with Excel in strings.  So I should be able to represent reasonably large numbers (the issue is their format and precision and not their size though it would be nice to be able to work with numbers up to 60^11).  I don't want to spend the time writing an arbitrary precision program, though that may have to happen.  Anyway, to keep the precision, I should be able to use long integers up to 10^18 which would give me accuracy for 60^10.  However, I am getting Value errors above 2147483647.  The only functions I'm using right now are basic arithmetic and ^ for integers and mod.  Does anyone have an idea what's up?

Also I notice that I get errors for lots of odd things like AscW and Dec and char data types?

Thanks for any help

Answer 1

Specifications for data types are at

https://msdn.microsoft.com/en-us/library/office/gg251528.aspx

For example: Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.

There's also a LongLong data type for 64-bit systems, but the Mac Excel 2011 Visual Basic Editor shows a compile error when I try to use "Dim MyLongLong as LongLong."

I have not tried LongLong with Mac Excel 2016.

Answer 2

Excel maintains 15 digits of significance. From Excel's specification and limits:

Smallest allowed negative number	-2.2251E-308
Smallest allowed positive number	2.2251E-308
Largest allowed positive number	9.99999999999999E+307
Largest allowed negative number	-9.99999999999999E+307
Largest allowed positive number via formula	1.7976931348623158e+308
Largest allowed negative number via formula	-1.7976931348623158e+308

What does "get errors for lots of odd things like ASCW and Dec char data types" mean? What errors? run time or compile time? Post some code snippets with examples of the data being acted upon and an explanation of what is happening that you think is wrong.