Split Function gives rn-time error

[davidm]

The following code testing the usage of the SPLIT FUNCTION gives me a
run-time
error (on xl2003).

Sub test()

Dim x
Dim txt as String

txt = "abc;xyz;456m;9a6d"
x= Split(txt,";")

Msgbox x

End sub

What am I doing wrong? x is supposed to reurn [abc xyz 456m 9a6d].


David

 

[davidm]

Many thanks Ross for your explanation. The Replace method works. But
could you also give *an illustration * of how the Split Fumction could
be used. The Help file is very reticent on this.


David

 

[davidm]

Many thanks Ross for your explanation. The Replace method works. Bu
could you also give *an illustration * of how the Split Fumction coul
be used. The Help file is very reticent on this.


Davi

 

[Leith Ross]

Hello David,

Here is an example of the Split Function...

Code:
--------------------

Sub test()

Dim Itm
Dim N As Long
Dim Txt As String
Dim X

txt = "abc;xyz;456m;9a6d"
x= Split(txt,";")

'X is now a variant holding and a String Array

'N = last element of the Array
N = UBound(X)

'Rebuild the Text String without the ";"s using For Each .. Next Loop
Txt = ""
For Each Itm In X
Txt = Txt & Itm & " "
Next Itm

'Example of standard For ... Next Loop
Txt = ""
For Itm = 0 To N
Txt = Txt & X(Itm) & " "
Next Itm

MsgBox Txt

End Sub

 

[Leith Ross]

Hello David,

Here is an example of the Split Function...

Code
-------------------

Sub test()

Dim Itm
Dim N As Long
Dim Txt As String
Dim X

txt = "abc;xyz;456m;9a6d"
x= Split(txt,";")

'X is now a variant holding and a String Array

'N = last element of the Array
N = UBound(X)

'Rebuild the Text String without the ";"s using For Each .. Next Loop
Txt = ""
For Each Itm In X
Txt = Txt & Itm & " "
Next Itm

'Example of standard For ... Next Loop
Txt = ""
For Itm = 0 To N
Txt = Txt & X(Itm) & " "
Next Itm

MsgBox Txt

End Sub

 

[davidm]

Thanks again, Ross. Your example is very clear and demonstrative. Neve
in my wildest dream could I have thought that the SPLIT FUNCTION coul
not be deployed on its own to remove delimiters. We are used t
functions (UDFs inclusive) being a quick-fix to deliver results. Not s
the SPLIT. If my observation is right, it will probably qualify as the
FUNCTION with the least utilty in Excel VBA. We could far mor
readily remove the delimiters with a standard routine like:

Function RemoveDelimiters(txt as string, d as string)

For i = 1 to Len(txt)
If Mid(txt,i,1) <> d then
k = k & Mid(txt,i,1)
Else
k = k & Chr(32)
End if
Next

RemoveDelimiters= k

End Function


Am I missing something in the greatness of the SPLIT FUNCTION?


David

 

[davidm]

Thanks again, Ross. Your example is very clear and demonstrative. Neve
in my wildest dream could I have thought that the SPLIT FUNCTION coul
not be deployed on its own to remove delimiters. We are used t
functions (UDFs inclusive) being a quick-fix to deliver results. Not s
the SPLIT. If my observation is right, it will probably qualify as the
FUNCTION with the least utilty in Excel VBA. We could far mor
readily remove the delimiters with a standard routine like:

Function RemoveDelimiters(txt as string, d as string)

For i = 1 to Len(txt)
If Mid(txt,i,1) <> d then
k = k & Mid(txt,i,1)
Else
k = k & Chr(32)
End if
Next

RemoveDelimiters= k

End Function


Am I missing something in the greatness of the SPLIT FUNCTION?


David

 

[Leith Ross]

Hello David,

The Split Function finds its main use in converting CSV files back into
Worksheets. Since the string data is in an array its a quick method
reading the delimited data and transfering it back to the worksheet.
You're right though, it doesn't have much common application in VBA.

Sincerely,
Leith Ross

 

[Dana DeLouis]

...We could far more

readily remove the delimiters with a standard routine like:

Just to mention...

Function RemoveDelimiters(txt As String, d As String)
RemoveDelimiters = Replace(txt, d, Chr(32))
End Function

If you wish to remove consecutive spaces, perhaps add another line to the
code above....
RemoveDelimiters = WorksheetFunction.Trim(RemoveDelimiters)

HTH :>)

 

[Tom Ogilvy]

The split function isn't designed to remove characters from a string - so no
surprise it would be a disappointment. The Replace function does that. If
you want to put the data delimited in an array, a very popular requirement,
then the split function works quite well and is offered as a solution to a
wide variety of problems posted in this group.

As Dana pointed out, your RemoveDelimiters function just reinvents the
wheel. Replace would work much faster.

Am I missing something in the greatness of the SPLIT FUNCTION?

Probably an understanding of how to use it.

here is some sample code where using split came in very handy:

Sub FixData()
Dim rng As Range, cell As Range
Dim sStr As String, s As String
Dim v As Variant, i As Long
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
For Each cell In rng
sStr = Replace(cell.Value, "<", "|(")
sStr = Replace(sStr, ">", ")|")
If Left(sStr, 1) = "|" Then _
sStr = Right(sStr, Len(sStr) - 1)
If Right(sStr, 1) = "|" Then _
sStr = Left(sStr, Len(sStr) - 1)
sStr = Replace(sStr, "||", "|")
v = Split(sStr, "|")
s = ""
For i = LBound(v) To UBound(v)
s = s & Replace(Replace(v(i), _
"(", "<"), ")", ">") & "<write:" _
& v(i) & ">"
Next i
cell.Offset(0, 1).Value = s
Next cell
End Sub

 

[davidm]

Thanks Tom, Ross and Dave for the light shed on the use of the Spli
Function.

It is now clear that in spite of what its name might suggest, th
Function only creates an Array medium to split text and does no
itself remove delimiters. Which brings home the notion that th
nomenclature used is misleading. Would that the function were name
*ArrayD* (or something along that line) to emphasize its primar
association with an *Array* of *D*elimited text, there would have been
much better clarity about what it does.

Just my thoughts.


Davi