1

Let's say I have something like "1-34-52", I want to split them into an array, however, while Test1 works, it gives me an String() type array. How do I put the numbers in "1-34-52" into a Long() type array? Can I redim type of an array in VBA?

Sub Test1()
    Dim arr As Variant
    arr = Split("1-34-52", "-")
    Debug.Print TypeName(arr), TypeName(arr(0))
End Sub

Sub Test2()
    Dim arr() As Long
    arr = Split("1-34-52") 'You get type mismatch error
End Sub
Nicholas
  • 2,560
  • 2
  • 31
  • 58
  • 1
    First issue I see is you are not putting a delimiter in your split statement, change it to `Split("1-34-52", "-")`. Secondly, I would just use CLng when I use the element: `TypeName(CLng(arr(0)))` – Dan Donoghue Jun 19 '17 at 00:14
  • Here's a post where someone had a similar situation with integers...https://stackoverflow.com/questions/19121595/convert-saved-string-of-numbers-into-an-array-of-integers – Darrell H Jun 19 '17 at 00:17
  • https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem – Slai Jun 19 '17 at 00:59

2 Answers2

0

You can Redim an array of Variants. Since Variants can hold integer values, there is no problem:

Sub dural()
    ary = Split("1-34-52", "-")
    ReDim lary(0 To UBound(ary))
    For i = 0 To UBound(ary)
        lary(i) = CLng(ary(i))
    Next i
End Sub

Note:

Sub dural()
    ary = Split("1-34-52", "-")
    Dim lary() As Long
    ReDim lary(0 To UBound(ary))
    For i = 0 To UBound(ary)
        lary(i) = CLng(ary(i))
    Next i
End Sub

will also work.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

You can loop through the array and populate a new one:

Sub Test1()
    Dim arr As Variant, LongArr() As Long, X As Long
    arr = Split("1-34-52", "-")
    ReDim LongArr(UBound(arr))
    For X = LBound(arr) To UBound(arr)
        LongArr(X) = CLng(arr(X))
    Next
    Debug.Print TypeName(arr), TypeName(arr(0))
    Debug.Print TypeName(LongArr), TypeName(LongArr(0))
End Sub
Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36