Parse String into Array

[JeaShe]

I give the user a dialog box to add an unlimited list of 13 or so digit values. I get it all as one string that I dilineate with a space between each value. Now I want to parse out the string and assign each parsed value to a variable to run through another routine. I will loop through the routine with each variable value parsed. So let's say I get the following string value:

"12489908 080348 9080 893802840 093800985312 823974 4985045 "

The macro doesn't know how many sets (digits + space) the user will enter.

So I need to somehow get those values into an array without knowing how many items will end up in the array. Can someone help with this? I'll put down some really loose pseudocode I thought of for more explanation:
Do until nothing left in string
id() = get the piece of string before a space
remove that piece + the space
find out if anything is left
If anything is left do it over
Loop until nothing is left
Thanks

 

[IknowMe]

To bad EB has no inherient Array Function

Sub Main

    Dim sIdArray() 
    Dim sIdArrayCnt As Long
    Dim sInput As String
    Dim x As Long
    
    sInput = "111111 222222 333333 444444 555555 666666 777777 888888"
    
    'add a Space to end of string to get last element
    'should tim string to remove leading spaces also
    
    If Right(sInput,1) <> " " Then sInput = sInput + " "
    
    sIdArrayCnt = 0

    While Instr(sInput," ") > 0
        Redim Preserve sIdArray(sIdArrayCnt)
        sIdArray(sIDArrayCnt) = GetField$(sInput,1, " ")
        sIdArrayCnt = sIdArrayCnt + 1
        sInput = Right(sInput, len(sInput) - Instr(sInput," "))
    Wend

    For x = 0 to Ubound(sIdArray)
        MsgBox sIdArray(x)   
    Next
    
End Sub

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[IknowMe]

'add a Space to end of string to get last element
'should trim string to remove leading spaces also
Replace
If Right(sInput,1) <> " " Then sInput = sInput + " "
With
sInput = LTrim(RTrim(sInput)) & " "


Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[Skie]

Sub Main
    Dim sIdArray()
    Dim sIdArrayCnt As Long
    Dim sInput As String
    Dim x As Long
    
    sInput = "111111 222222 333333 444444 555555 666666 777777 888888"

    sIdArrayCnt = 0

    While GetField(sInput,sIdArrayCnt+1," ") <> ""
        Redim Preserve sIdArray(sIdArrayCnt)
        sIdArray(sIDArrayCnt) = GetField$(sInput,sIdArrayCnt+1, " ")
        sIdArrayCnt = sIdArrayCnt + 1
    Wend

    For x = 0 to Ubound(sIdArray)
        MsgBox sIdArray(x)   
    Next
End Sub

This'll save you a couple lines. You won't have to tack on a space either. About the only other thing you might want to do would be to make sure it's single spaced.

While InStr(sInput,"  ")
    sLeft = Left(sInput,InStr(sInput,"  ")-1)
    sRight = Right(sInput,Len(sInput)-InStr(sInput,"  "))
    sInput = sLeft & sRight
Wend

FYI, rather than using a right with an instr to crop:
sInput = Right(sInput, len(sInput) - Instr(sInput," "))
You can use SetField to crop the string:
sInput = LTrim(SetField(sInput,1," "," "))

 

[JeaShe]

Thank you for responding so quickly. I was hoping to get feedback right away. I'll try these options today.

 

[JeaShe]

Ok... using the following code:

Sub Main
Dim sIdArray()
Dim sIdArrayCnt As Long
Dim sInput As String
Dim x As Long

sInput = "111111 222222 333333 444444 555555 666666 777777 888888"

sIdArrayCnt = 0

While GetField(sInput,sIdArrayCnt+1," ") <> ""
Redim Preserve sIdArray(sIdArrayCnt)
sIdArray(sIDArrayCnt) = GetField$(sInput,sIdArrayCnt+1, " ")
sIdArrayCnt = sIdArrayCnt + 1
Wend

For x = 0 to Ubound(sIdArray)
MsgBox sIdArray(x)
Next
End Sub
I need to capture each of those array elements. Right now they are x. How do I capture each of those elements? Again, I don't know how many I may get. Thank you

 

[IknowMe]

They values are captured as elements of the array. X is simply a pointer.

To help you understand

sIdArray(0) = "111111"
sIdArray(1) = "222222"
sIdArray(2) = "333333"

All x is doing is iterating from 0 to the total number of elements in the array (in the example 0 - 8). The code is dynamic and will adjust. To see play with sInput

ex.

sInput = "111111 222222 333333 444444 555555 666666"
sInput = "111111 222222 333333 444444 555555 666666 777777"
sInput = "11 22 33 44 55 66 77 88 99 1010 1111 1212 1313"




Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[JeaShe]

So I guess I'll put the rest of the code (that uses the individal elements) inside the "while". That way I don't have to know the number of elements, it'll just iterate until it's done.

 

[IknowMe]

or inside the

For x = 0 to Ubound(sIdArray)
'Do something with sIdArray(x)
Next


Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[JeaShe]

Ok, all... I really need your help now!

I want to be able to provide the dialog box you gave me above and let my staff enter account numbers in there and use them, etc... but can I set it up to also obtain additional numbers (previously stored in a spreadsheet)and run them through the macro just like the records added manually to the dialog box?

For example, the macro runs today and keeps a record of accounts it couldn't work today in a spreadsheet(Excel). Then tomorrow the tech would enter new accounts to run and also want the ones from the previous day that were entered in the spreadsheet to run through again. So the macro would essentially keep a record of the accounts it needs to run each day.

I can provide the code I have but it's pretty big and it's build off the code you all provided above:

For x = 0 to Ubound(sIdArray)
accountnumber = sIdArray(x)Next


Any ideas? Thanks

 

[Skie]

If you're just looking to make the array bigger from the data in the Excel.

Set oExcel = CreateObject("Excel.Application")
Set oWkBk = oExcel.Workbooks.Open "C:\Temp\Your.xls"
For i = 1 To oWkBk.Cells.SpecialCells(xlCellTypeLastCell).Row
  Redim Preserve sIdArray(sIdArrayCnt)
  sIdArray(sIDArrayCnt) = oWkBk.Cells(1,i)
  sIdArrayCnt = sIdArrayCnt + 1
Next
oWkBk.Saved = True
oWkBk.Close
oExcel.Quit

Writing would be handled in a similar way.

Set oExcel = CreateObject("Excel.Application")
Set oWkBk = oExcel.Workbooks.Open "C:\Temp\Your.xls"
For i = 0 to UBound(sIDArray)
  oWkBk.Cells(1,i) = sIDArray(i)
Next
oExcel.DisplayAlerts = False
oWkBk.SaveAs FileName:="C:\Temp\Your.xls"
oExcel.DisplayAlerts = True
oWkBk.Close
oExcel.Quit