Convert String array to Long array

[Jaafar Tribak]

Sub test()

    Dim s As String
    Dim Ar() As String
    
    s = "1,2,3,4,5"
    ar = Split(s, ",")
    MsgBox WorksheetFunction.Max(ar)

End Sub

The above Split Function returns a String array - Is there a way to convert this String array to a Long array so that we can obtain the Max element (ie: 5) but w/o looping though the array and converting each element one by one ?

 

[diddi]

Sub test()

    Dim s As String
    Dim Ar() As String
    dim arval() as long
    
    s = "1,2,3,4,5"
    ar = Split(s, ",")

    For row= 0 to ubound(ar)-1
        arval(row)=val(ar(row))
    next row

    MsgBox WorksheetFunction.Max(arval)

End Sub

 

[Peter_SSs]

Perhaps you missed that Jaafar was asking if this could be done without looping to convert each value?

 

[diddi]

yeh your right. lol so is there? i dont know that you can...

 

[wigi]

I do not know of any way without a loop, that is, no way that would be faster and/or more efficient than a loop (in memory).

but w/o looping though the array and converting each element one by one ?

That takes milliseconds on any decent PC, no? Even with large arrays. Why bother?

 

[mikerickson]

s = "1,2,3,4,5"
MsgBox WorksheetFunction.Max(Evaluate("{" & s & "}"))

 

[RoryA]

Or:

s = "1,2,3,4,5"
MsgBox Evaluate("MAX(" & s & ")")

 

[Marcelo Branco]

s = "1,2,3,4,5"
MsgBox WorksheetFunction.Max(Evaluate("{" & s & "}"))

Cool!!!

I tried many complicated things...no success
so simple like that!!

M.

 

[Peter_SSs]

Mike & Rory's suggestions are neat and produce the result without looping as requested. I'm not sure what Jaafar's ultimate goal is (he often seems to try things to just see if he can ) but I would contend that looping through the array is faster than either of those methods anyway. Of course if this is not being repeated a lot of times the speed difference is unlikely to matter.

I must say that I didn't test on any other (eg longer) strings so I'm not sure how much difference that would make. However this code would be even faster if the max value was not the last value in the string.

s = "1,2,3,4,5"
Ar = Split(s, ",")
Mx = Ar(0)
For j = 0 To UBound(Ar)
    If Ar(j) > Mx Then Mx = Ar(j)
Next j
MsgBox Mx

 

[GTO]

Hi all,

Maybe I'm missing something, but in Mike and Rory's, 's' is not an array.

Mark