Function with an error

[James]

Please help debug this. I'm trying to turn a text string deliminated with | into an array.

Option Compare Text
Function Txt_Ary(Txt As String) As Range 'Turns the string value to a multi value range
Dim Cnt As Integer
Dim n As Integer
Dim Tmp_Ary As Range
Dim Tmp_Txt As String
Dim Sel_Case As Integer

Txt_Ary = Tmp_Ary
With Application.WorksheetFunction

'Finds the number of values deliminated by a ;
Cnt = Len(Txt) - Len(.Substitute(Txt, "|", "")) + 1

'there is "" or only one value then select case 1 otherwise select case 2
If .IsError(.Find(Txt, "|")) Then
Sel_Case = 1
Else
Sel_Case = 2
End If
End With

Select Case Sel_Case

'Select if there are no | in the text string
Case 1
Txt_Ary = Txt
Exit Function

'Select if there is a | in the text string
Case 2
With Application.WorksheetFunction
Tmp_Txt = Txt
For n = 0 To Cnt
If .IsError(.Find(Tmp_Txt, "|")) Then
Tmp_Ary(n + 1) = Tmp_Txt
Else
Tmp_Ary(n + 1) = Left(Tmp_Txt, .Find("|", Tmp_Txt) - 1)
Tmp_Txt = .Substitute(Tmp_Txt, Left(Tmp_Txt, .Find("|", Tmp_Txt)), "")
End If
Next
End With
End Select
End Function


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Harlan Grove]

James wrote...

Please help debug this. I'm trying to turn a text string deliminated with | into an array.

....

Are you using Excel 2000 or later? If so, try

Function foo(s As String, Optional d As String = "|") As Variant
If d = "" Then foo = s Else foo = Split(s, d)
End Function

This allows you to use a different delimiter when necessary but
defaults to |. If the passed delimiter is "", it returns the passed
string. This returns a horizontal array, i.e.,

=foo("1|2|x")

returns {"1","2","x"} rather than {"1";"2";"x"}. If you want the
latter, use

=TRANSPOSE(foo("1|2|x"))

 

[Dave Peterson]

Without looking at your code...

xl2k added a Split command that you could use directly.

Tom Ogilvy uses this for lots of code in xl97:

Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

=====
These come from MS:

Public Function ReadUntil(ByRef sIn As String, _
sDelim As String, Optional bCompare As Long _
= vbBinaryCompare) As String
Dim nPos As String
nPos = InStr(1, sIn, sDelim, bCompare)
If nPos > 0 Then
ReadUntil = Left(sIn, nPos - 1)
sIn = Mid(sIn, nPos + Len(sDelim))
End If
End Function
Public Function Split97(ByVal sIn As String, Optional sDelim As _
String, Optional nLimit As Long = -1, Optional bCompare As _
Long = vbBinaryCompare) As Variant
Dim sRead As String, sOut() As String, nC As Integer
If sDelim = "" Then
Split97 = sIn
End If
sRead = ReadUntil(sIn, sDelim, bCompare)
Do
ReDim Preserve sOut(nC)
sOut(nC) = sRead
nC = nC + 1
If nLimit <> -1 And nC >= nLimit Then Exit Do
sRead = ReadUntil(sIn, sDelim)
Loop While sRead <> ""
ReDim Preserve sOut(nC)
sOut(nC) = sIn
Split97 = sOut
End Function

The readuntil and split97 functions were stolen from the MSKB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5