Why can't I reDim an array with LongPtr or LongLong index in Excel 2016 VBA

Question

I use the following code in Excel 2010 (64-bit Windows 7) and it works well.

Sub code_on_2010()
  Dim i As Long

  i = InputBox("input integer number")
  ReDim a(i) As Variant
  '....
End Sub

I recently upgrade my computer to Windows 10 (64-bit) and Excel 2016 (64-bit). Since I know the new type name for the 64-bit long integer type, I rewrite my code as follows:

Sub code_on_2016_with_LongPtr()
  Dim i As LongPtr

  i = InputBox("input integer number")
  ReDim a(i) As Variant
  '...
End Sub

It returns an error Type mismatch (Error 13).

Even I replace LongPtr with LongLong (as shown below), it also returns an error Type mismatch.

Sub code_on_2016_with_LongLong()
  Dim i As LongLong

  i = InputBox("input integer number")
  ReDim a(i) As Variant
  '...
End Sub

Can someone tell me why I cannot ReDim an array with an index like LongPtr or LongLong in Excel 2016 VBA?

Answer 1

For 64-bit Excel, Excel will not require LongPtr or LongLong in your code:

Option Explicit

Sub code_on_2010()
Dim i As Long 'declaring any other type won't speed up your code, and won't give a bigger range of possible numbers!
Dim h$
Dim a() 'basically says : a is a variable sized array of type variant
h = InputBox("input integer number")  'returns a string
if isnumeric(h) then i=clng(h)
ReDim a(i)
'....
End Sub