How to handle "type mismatch" in VBA boolean/LongLong array?

Question

When trying to execute below code, I get the VBA error: type mismatch. Anyone know the cause (and solution? :-))

I changed datatypes from Long to LongLong to be able to handle larger numbers. Before this the code (excerpt) was working fine.

Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hDC As LongPtr, ByVal nIndex As Long) As Long
Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hwnd As LongPtr, ByVal hDC As LongPtr) As Long

Public Sub TestScreenResolution()
    Debug.Print ScreenResolution
End Sub
Private Function ScreenResolution() As Double
     Dim hDC As Long
     hDC = GetDC(0)
     ScreenResolution = GetDeviceCaps(hDC, 88)
     ReleaseDC 0, hDC
End Function

Public Sub TestMySub()
    Call MySub(999999999)
End Sub
Private Sub MySub(ByVal x As LongLong)

Dim y As LongLong
Dim Max As LongLong
Dim Min As LongLong

Max = x * x
Min = (x - 1) * (x - 1)

Dim Arr() As Boolean   'Default Boolean type is False
ReDim Arr(Min To Max) ''<<< "Type Mismatch" compile error

For y = Max To Min Step -2
    Arr(y) = True
Next y

End Sub

Of course this code is leading to nothing, just for testing this piece of code.

@Comintern it *does*. That deleted answer was onto something, it's the `ReDim` statement that's failing, highlighting `Max` as the cause for the type mismatch... at compile-time — Mathieu Guindon, Feb 04 '19 at 16:59
@M.Doerner `TypeName(999999999)` prints `Long`... It would be a `Double` if it were a cell's value though. — Mathieu Guindon, Feb 04 '19 at 17:05
Sorry, I miscounted. One more `9` and it would have been a `Double`. — M.Doerner, Feb 04 '19 at 17:12
What do you need such a large array for anyway? If we knew *why*, maybe we could offer alternatives. An array can't be dimensioned with a `LongLong` - given the `x` and `y` names and the `TestScreenResolution` method it looks like you're trying to make a flattened "list" of what would otherwise be a 2D array? Why can't a 2D array work? — Mathieu Guindon, Feb 04 '19 at 17:25
Wow, lot's of answers, thanks you guys...don't know if I understand everything but I'll try to catch up: Comintern is right: the error is at "ReDim Arr(Min To Max)", but an array can't be dimensioned with a LongLong Guindon? What's a good replacement? Some background: I am developing a new technique for calculating large primes and dove into VBA because excel was restricting me ;-). About the TestScreenResolution: I just copied this part for making the new MySub 'suitable' for 64-bit calculations. Alternatives are welcome! — Ronald Vonk, Feb 04 '19 at 18:08
FWIW, the only thing you should need to store for generating large prime numbers is the list of primes up to your current index. — Comintern, Feb 04 '19 at 18:33
[This](https://stackoverflow.com/q/9625663/1188513) might be of interest. — Mathieu Guindon, Feb 04 '19 at 19:38

score 7 · Accepted Answer · answered Feb 04 '19 at 18:18

tldr;

You can't "handle" it - LongLong isn't compatible with your ReDim statement. (Although 999999999 would technically fit into a Long, the compiler doesn't allow a narrowing conversion there).

The maximum size of any array in VBA is determined by the SAFEARRAY structure (defined in section 2.2.30.10 of the OLE Automation Protocol specification) that it is backed by internally. The definition of the structure in C++ is this:

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

Note, the cbElements the size in bytes of an array item. This effectively limits each item to ~4GB.

The problem that you run into is in the SAFEARRAYBOUND structures that store information about the array's dimensions:

typedef struct tagSAFEARRAYBOUND {
  ULONG cElements;
  LONG  lLbound;
} SAFEARRAYBOUND, *LPSAFEARRAYBOUND;

That means that the maximum number of total items you can cram into any dimension of a SAFEARRAY regardless of programming language is the value of ULONG_MAX (4,294,967,295). Thus the following compiles (although it runs out of memory with the allocation on my machine):

Dim foo(-2147483646 To 2147483647) As Byte

Note that in the above example, the lower bound is negative because VBA also doesn't support unsigned types, which presents the other hurdle for VBA code that is sizing arrays. You could technically get an array with bounds of 0 To 4294967294 by requesting one through the SafeArrayCreate function exported by oleaut32.dll, but I'm suspecting that you'd run into similar problems indexing into it.

Peeling the layers back even further, you start hitting some of the more interesting limits. For example, looking back at the SAFEARRAYBOUND structure above, you'll notice that although you can have ULONG_MAX elements, the lower bound of the array is constrained by a signed LONG. This is limitation is carried over into most of the other OLE automation that deal with handling SAFEARRAYs, including SafeArrayGetLBound and others (interestingly, SafeArrayGetUBound is also signed, which makes me wonder if you could overflow it...).

So why didn't MS update this when they added 64 bit support? Well, it would have broken pretty much everything. Beyond that, there really wasn't any pressing need to - once you get beyond ULONG elements, you start running into very real problems with memory in that the memory for the data area has to be allocated when the structure is created - otherwise it's not possible to use it via COM, because at the root of that structure is a pointer, and the contract says that the calling code (regardless of client) must be able to use any address that falls within the data area, VBA included.

Aw, I just calculated that I need approx. 53,038,088,939,238,200 array positions, so I guess I have to use a multi-dimensional array...or does the same limitation apply? — Ronald Vonk, Feb 04 '19 at 19:09
Well, given the fact that an array that size would require 197,582,278GB of RAM, I'm thinking ***that*** would be your limitation. — Comintern, Feb 04 '19 at 19:14
meh... what's a couple hundred petabytes of RAM among friends? — FreeMan, Feb 04 '19 at 19:33
No kidding @Comintern! Even if I just fill it with booleans? Hmm, I see you use bytes instead. How did you calculate this memory requirement? Itn't there a way to store this in the harddrive memory? Maybe I need to learn Python... — Ronald Vonk, Feb 04 '19 at 19:53
I was assuming `Longs`. Each array position requires as many bytes as the data type that it contains. So, the memory requirement (not including the tiny overhead for the structure itself) is `N * B` where the `N` is the number of elements and `B` is the size in bytes of the contained type. After that, it's a simple byte to XB conversion. A `Byte` array that size would cut the memory requirement to a "mere" 49,395,569GB. ;-) — Comintern, Feb 04 '19 at 19:58
Too bad...then I can only go up to 20.000 digits for my primes (requiring 2147483647 array positions with my method), not the 100.000.000 digits which holds the record ;-). I guess this makes my method unfeasable in practice. But 20.000 is still a huge challenge, for I have to streamline my methode for 'chunking' the large numbers into pieces for this. Thanks for your advice though! — Ronald Vonk, Feb 04 '19 at 20:21

How to handle "type mismatch" in VBA boolean/LongLong array?

1 Answers1

tldr;