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.
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.