vba Excel 2010: convert string array into int array

Question

Dim myarray(2) as Variant

myarray(0)="3"
myarray(1)="4"
myarray(2)="5"
myarray(3)="9"

I want it to become

myarray(0)=3
myarray(1)=4
myarray(2)=5
myarray(3)=9

What there any way to make it with no loops, just in 1 line? This is for Excel 2010.

I want something like this, but it doesn't work:

intArray = Array.ConvertAll(stringArray , Function(str) Int32.Parse(str))

I don't know if it can be done without loops... but each entry can be converted using the `cInt()` function... By the way, why "without loops"? — Barranka, Aug 06 '13 at 22:12
The performance is important for me here. I believe if there is a special function then it'll make it faster. — Haradzieniec, Aug 06 '13 at 22:15
If you use the array entries in some other parts of your code, an option could be to convert them only when you use them. Example: `Dim x as integer: x = cInt(myarray(0)) * 5` — Barranka, Aug 06 '13 at 22:24
I want to prepare the array of integers to use the array (find max value etc) in the code. — Haradzieniec, Aug 06 '13 at 22:27
If you are happy with casting to `Double`, a solution is to dump it to a range and then store it back. This will convert the strings to doubles automatically (just tested it), and it must be faster than looping for large arrays (untested claim). The downside is that restoring the results from the range (i) changes the array base to 1 instead of 0 and (ii) makes the array two dimensional, with one dimension having size 1. If these restrictions can be accommodated, I will post the code below. — Ioannis, Aug 06 '13 at 22:39
forgot to mention: there is no built-in method :( btw, do you mean `Dim myarray(3)` on the first line? — Ioannis, Aug 06 '13 at 22:43
However slow the conversion loop will be, I guarantee you that getting the data into/out of a worksheet is going to be 4x slower. So why even bother? — Chel, Aug 06 '13 at 22:49
@rdhs I did some testing and you are right: going to sheet and back is 2-3 times as slow. There is probably nothing better than looping (unless one is brave enough to mess up with `XLL` functions). — Ioannis, Aug 06 '13 at 23:32

Chel · Accepted Answer · 2013-08-07T00:03:09.657

Looping through the array and converting the values one-by-one is fast enough. Here's a snippet of code to show how fast a conversion loop is relative to performing your cell I/O:

Private Const I_MAX = 10000
Private Const J_MAX = 200
Private Declare Function GetTickCount Lib "kernel32.dll" () As Long

Sub ticktest()
    Dim ticks As Long, i As Long, j As Long
    Dim v() As Variant
    Dim a() As Long 'VBA integers are internally stored as longs
    Dim r As Range

    Set r = [A1].Resize(I_MAX, J_MAX)
    ReDim a(1 To I_MAX, 1 To J_MAX)

    ticks = GetTickCount
    v = r
    Debug.Print "Read from range: " & GetTickCount - ticks

    Debug.Print "Type of values in v(): " & TypeName(v(1, 1))

    ticks = GetTickCount
    For i = 1 To I_MAX
        For j = 1 To J_MAX
            a(i, j) = v(i, j)
        Next j
    Next i
    Debug.Print "Copy with cast to Long: " & GetTickCount - ticks

    ticks = GetTickCount
    For i = 1 To I_MAX
        For j = 1 To J_MAX
            v(i, j) = CLng(v(i, j))
        Next j
    Next i
    Debug.Print "In-place cast to Variant/Long: " & GetTickCount - ticks

    ticks = GetTickCount
    r = a
    Debug.Print "Write from long array: " & GetTickCount - ticks

    ticks = GetTickCount
    r = v
    Debug.Print "Write from variant array: " & GetTickCount - ticks

    For i = 1 To I_MAX
        For j = 1 To J_MAX
            v(i, j) = CStr(v(i, j))
        Next j
    Next i
    r = v
End Sub

With these values for I_MAX and J_MAX we get a 2,000,000–entry array, where reading from and writing to the worksheet is about 4x slower than converting each entry from a Variant/String to a Long:

Read from range: 749
Type of values in v(): String
Copy with cast to Long: 546
In-place cast to Variant/Long: 842
Write from long array: 921
Write from variant array: 1248

Read from range: 749
Type of values in v(): String
Copy with cast to Long: 546
In-place cast to Variant/Long: 827
Write from long array: 905
Write from variant array: 1248

Read from range: 749
Type of values in v(): String
Copy with cast to Long: 531
In-place cast to Variant/Long: 826
Write from long array: 905
Write from variant array: 1279

So there's no real need to avoid loops, with one caveat: If you do the cell I/O in a loop, performance becomes abysmal. Here, for example, a loop of r(i, j) = v(i, j) takes a full 100 seconds (that's 100,000 ticks, or 2,000x the conversion loop time) to complete.

vba Excel 2010: convert string array into int array

1 Answers1

Linked