Convert String array to Long array

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,815
Office Version
  1. 2016
Platform
  1. Windows
Code:
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 ?
 
and, whilst the workaround solves the specific problem, it does not convert an array from 1 data type to another, which i think is the thrust of challenge put.
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Maybe I'm missing something, but in Mike and Rory's, 's' is not an array.
But neither was Jaafar's 's' an array

and, whilst the workaround solves the specific problem, it does not convert an array from 1 data type to another, which i think is the thrust of challenge put.
You may be correct about the thrust but I interpreted, from his example, that Jaafar had a string of numbers that he wanted to find the maximum of. I guess he'll clarify when he returns. :)
 
Upvote 0
But neither was Jaafar's 's' an array

Hi Peter , you are right of course (I knew I was tired:rolleyes:). I poorly relayed that Jaafar seemed to want to convert/evaluate the string array 'Ar', not the string.

Hope all is well, :-)

Mark
 
Upvote 0
irrespective of the intention of the OP, the discussion is all good for the grey matter which is a good thing imho
 
Upvote 0
and, whilst the workaround solves the specific problem, it does not convert an array from 1 data type to another, which i think is the thrust of challenge put.
Given an array of numeric strings, this will convert it to an array of Doubles.
Code:
Sub ttt()
    Dim stringArray(1 To 3)
    Dim numberArray As Variant

    stringArray(1) = "1":
    stringArray(2) = "2"
    stringArray(3) = "4"
    
    numberArray = Evaluate("{" & Join(stringArray, ",") & "}")
    
    MsgBox TypeName(numberArray(1)): Rem Double
End Sub
Given an array of Type double this will convert it to an array of strings.

Code:
Sub tts()
    Dim numberArray(1 To 3)
    Dim stringArray As Variant
    
    numberArray(1) = 1
    numberArray(2) = 2
    numberArray(3) = 3
    
    stringArray = Split(Join(numberArray, ","), ",")
    
    MsgBox TypeName(stringArray(1)): Rem String
End Sub
 
Upvote 0
Nice Mike.

In the 2nd piece of code, as you know, you could split and join on a space:

Code:
stringArray = Split(Join(numberArray))

For the rest, no comments and nice work, especially the first bit of code.
 
Upvote 0
I've got a feeling there is more to the OP's question than he has told us so far. But based on what he posted, I would avoid the Strings altogether...
Code:
Sub test()
    Dim Ar As Variant
    Ar = Array(1, 2, 3, 4, 5)
    MsgBox WorksheetFunction.Max(Ar)
End Sub
 
Upvote 0
Given an array of Type double this will convert it to an array of strings.

Code:
Sub tts()
    Dim numberArray(1 To 3)

I think you "cheated" here:eeek:... numberArray is not an array of Type Double, it is an array of Type Variant to which you have assigned Integer (not Double by the way) values. Actually, I think you "misspoke" when you used the upper case "T" in the word "Type".
 
Last edited:
Upvote 0
Hi

It's not clear to me what is the exact objective:

- how to convert an array of strings into an array of doubles
or
- how to calculate the maximum number in a set of numbers written as a CSV string

Anyway, using Evaluate is a simple solution if we have a small number of values. However, even for 127 values, with just 1 digit each, it didn't work for me because of the 255 character limit of the input string.

Ex.:

Code:
Sub Test1()
Dim s As String
 
s = "1" & Application.Rept(",1", 127)
 
MsgBox WorksheetFunction.Max(Evaluate("{" & s & "}"))
End Sub

One alternative way, that works till 64k values, it to use the Power() function to perform the conversion between string and double values.

A test with a string array with exactly 64k:

Code:
Sub test()
    Dim sArr() As String
    Dim j As Long
 
    Const UPPERBOUND As Long = 65536
 
    'Generate the array of strings
    ReDim sArr(1 To UPPERBOUND)
    For j = 1 To UPPERBOUND
        sArr(j) = CStr(j / 1000)
    Next j
 
    ' calculate the max after converting the strings to doubles
    With Application
        MsgBox .Max(.Power(sArr, 1))
    End With
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,225,156
Messages
6,183,222
Members
453,152
Latest member
ChrisMd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top