VBA String to Array?

[IAVENOIDEAWHAIMDOIN]

Hi!

I've got a string of values, it looks like this...
MyStr = "0.001, 0.003, 0.001, 0.004" ...etc.
In all it contains around 800 numbers.

What I want to do with this string is to make the numbers into Y values for a new series in an existing chart. The code I'm working with at the moment is like this...

Worksheets(ShtName).ChartObjects(1).Select
ActiveChart.SeriesCollection.NewSeries
With ActiveChart.SeriesCollection(LastSeries + 1)
    .XValues = "=Results!R2C1:R846C1
    .Values = "={" & MyStr & "}"
End With

Can I convert my string into an array to input the values as it recommends in the VBE help file on the Values property? Or is there another way I can achieve the same thing?

I would really appreciate any help on this one,
Cheers, Martin

 

[Ahnold]

Use Split.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()
    <SPAN style="color:#00007F">Dim</SPAN> myStr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> x
    myStr = "0.001, 0.003, 0.001, 0.004"
    x = Split(myStr, ",")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[IAVENOIDEAWHAIMDOIN]

Hi Ahnold,

I've tried this and I'm getting the "Compile Error: Sub or Function not defined". Do I need to reference a new library?

I'm on Excel97

Martin.

 

[Andrew Poulsom]

Here is an Excel 97 version of the Split function:

http://www.dicks-blog.com/archives/2004/05/20/split97/

 

[jindon]

Hi

try

Sub Test()
    Dim myStr As String
    Dim x
    myStr = "0.001, 0.003, 0.001, 0.004"
    x = Split(myStr, ",")
End Sub

Function Split(txt As Variant, delim As String) As Variant
Dim i As Long, ii as Integer, a(), result()
For i = 1 To Len(txt)
    If i < Len(txt) And Mid(txt, i, 1) = delim Then
        ii = ii + 1: ReDim Preserve a(ii): a(ii) = i
    End If
Next
a(0) = 1
ReDim Preserve a(ii + 1): a(ii + 1) = Len(txt) + 1
ReDim result(UBound(a) - 1): ii = -1
For i = LBound(a) To UBound(a) - 1
    ii = ii + 1
    result(ii) = Replace(Mid(txt, a(i), a(i + 1) - a(i)), delim, "")
Next
Split = result
End Function

 

[IAVENOIDEAWHAIMDOIN]

Thank you Andrew & Jindon.