VBA Split-function

I want to use the Split function in a custom made function and want the
output to be used in a main procedure.

My problem is with the following code:

***************************************************
Public Function ExtractData(TextString as String, _
Separator as String, _
Optional Block_N as integer = 0)

Dim StringToArray As String

StringToArray = Split(Expression:=DataString, _
Delimiter:=Separator, _
Limit:=-1)

ExtractData = StringToArray

End Function
***************************************************

(This is not my original function, but it'll do for my question.)

My output of this function would be in BASE 0, and I want this in BASE 1.

I have tried placing "OPTION BASE 1" at the top of my procedure, but it
seems that the "Split"-function can only return an array with BASE 0.

Can any1 tell me if there is a way to obtain the result from above
function with BASE 1, without having to ReDim the ExtractData-variable
and filling it again with the items from the StringToArray-variable?

Hopefully someone can give me an answer to this one.

TIA,
CoRrRan

Try this modified SPlit function (with Option Base 1)

'-----------------------------------------------------------------
Function pSplit(Text As String, _
Optional Delimiter As String = ",") As Variant
'-----------------------------------------------------------------
Dim i As Long
Dim sFormula As String
Dim aryEval
Dim aryValues

If Delimiter = vbNullChar Then
Delimiter = Chr(7)
Text = Replace(Text, vbNullChar, Delimiter)
End If
sFormula = "{""" & Application.Substitute(Text, Delimiter, """,""") &
"""}"
aryEval = Evaluate(sFormula)
pSplit = aryEval

End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"CoRrRan" <CoRrRan~~[at]~~gmail~~[dot]~~com> wrote in message
news:Xns96001F833589CoRrRan@194.109.133.29...
> I want to use the Split function in a custom made function and want the
> output to be used in a main procedure.
>
> My problem is with the following code:
>
> ***************************************************
> Public Function ExtractData(TextString as String, _
> Separator as String, _
> Optional Block_N as integer = 0)
>
> Dim StringToArray As String
>
> StringToArray = Split(Expression:=DataString, _
> Delimiter:=Separator, _
> Limit:=-1)
>
> ExtractData = StringToArray
>
> End Function
> ***************************************************
>
> (This is not my original function, but it'll do for my question.)
>
> My output of this function would be in BASE 0, and I want this in BASE 1.
>
> I have tried placing "OPTION BASE 1" at the top of my procedure, but it
> seems that the "Split"-function can only return an array with BASE 0.
>
> Can any1 tell me if there is a way to obtain the result from above
> function with BASE 1, without having to ReDim the ExtractData-variable
> and filling it again with the items from the StringToArray-variable?
>
> Hopefully someone can give me an answer to this one.
>
> TIA,
> CoRrRan

This seems to work... however, I need this function to be fast, as it
will be used very often in the main-routine. Do you think both functions
are equally fast when called >1000 times in 1 procedure?

Thanks for the quick reply!

CoRrRan

P.S. The other option is (of course) to change the main procedure from
being mainly BASE 1 to BASE 0 for using the ExtractData-function. (But
this requires a lot of (debug-)time, which I do not have.

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
news:#WBmw5HFFHA.2180@TK2MSFTNGP10.phx.gbl:
> Try this modified SPlit function (with Option Base 1)
>
> '-----------------------------------------------------------------
> Function pSplit(Text As String, _
> Optional Delimiter As String = ",") As Variant
> '-----------------------------------------------------------------
> Dim i As Long
> Dim sFormula As String
> Dim aryEval
> Dim aryValues
>
> If Delimiter = vbNullChar Then
> Delimiter = Chr(7)
> Text = Replace(Text, vbNullChar, Delimiter)
> End If
> sFormula = "{""" & Application.Substitute(Text, Delimiter,
> """,""") &
> """}"
> aryEval = Evaluate(sFormula)
> pSplit = aryEval
>
> End Function
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "CoRrRan" <CoRrRan~~[at]~~gmail~~[dot]~~com> wrote in message
> news:Xns96001F833589CoRrRan@194.109.133.29...
>> I want to use the Split function in a custom made function and want
>> the output to be used in a main procedure.
>>
>> My problem is with the following code:
>>
>> ***************************************************
>> Public Function ExtractData(TextString as String, _
>> Separator as String, _
>> Optional Block_N as integer = 0)
>>
>> Dim StringToArray As String
>>
>> StringToArray = Split(Expression:=DataString, _
>> Delimiter:=Separator, _
>> Limit:=-1)
>>
>> ExtractData = StringToArray
>>
>> End Function
>> ***************************************************
>>
>> (This is not my original function, but it'll do for my question.)
>>
>> My output of this function would be in BASE 0, and I want this in
>> BASE 1.
>>
>> I have tried placing "OPTION BASE 1" at the top of my procedure, but
>> it seems that the "Split"-function can only return an array with BASE
>> 0.
>>
>> Can any1 tell me if there is a way to obtain the result from above
>> function with BASE 1, without having to ReDim the
>> ExtractData-variable and filling it again with the items from the
>> StringToArray-variable?
>>
>> Hopefully someone can give me an answer to this one.
>>
>> TIA,
>> CoRrRan

No, the pSplit routine will never be as fast as the inbuilt method.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"CoRrRan" <CoRrRan~~[at]~~gmail~~[dot]~~com> wrote in message
news:Xns96007B5D245CCoRrRan@194.109.133.29...
> This seems to work... however, I need this function to be fast, as it
> will be used very often in the main-routine. Do you think both functions
> are equally fast when called >1000 times in 1 procedure?
>
> Thanks for the quick reply!
>
> CoRrRan
>
> P.S. The other option is (of course) to change the main procedure from
> being mainly BASE 1 to BASE 0 for using the ExtractData-function. (But
> this requires a lot of (debug-)time, which I do not have.
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
> news:#WBmw5HFFHA.2180@TK2MSFTNGP10.phx.gbl:
> > Try this modified SPlit function (with Option Base 1)
> >
> > '-----------------------------------------------------------------
> > Function pSplit(Text As String, _
> > Optional Delimiter As String = ",") As Variant
> > '-----------------------------------------------------------------
> > Dim i As Long
> > Dim sFormula As String
> > Dim aryEval
> > Dim aryValues
> >
> > If Delimiter = vbNullChar Then
> > Delimiter = Chr(7)
> > Text = Replace(Text, vbNullChar, Delimiter)
> > End If
> > sFormula = "{""" & Application.Substitute(Text, Delimiter,
> > """,""") &
> > """}"
> > aryEval = Evaluate(sFormula)
> > pSplit = aryEval
> >
> > End Function
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "CoRrRan" <CoRrRan~~[at]~~gmail~~[dot]~~com> wrote in message
> > news:Xns96001F833589CoRrRan@194.109.133.29...
> >> I want to use the Split function in a custom made function and want
> >> the output to be used in a main procedure.
> >>
> >> My problem is with the following code:
> >>
> >> ***************************************************
> >> Public Function ExtractData(TextString as String, _
> >> Separator as String, _
> >> Optional Block_N as integer = 0)
> >>
> >> Dim StringToArray As String
> >>
> >> StringToArray = Split(Expression:=DataString, _
> >> Delimiter:=Separator, _
> >> Limit:=-1)
> >>
> >> ExtractData = StringToArray
> >>
> >> End Function
> >> ***************************************************
> >>
> >> (This is not my original function, but it'll do for my question.)
> >>
> >> My output of this function would be in BASE 0, and I want this in
> >> BASE 1.
> >>
> >> I have tried placing "OPTION BASE 1" at the top of my procedure, but
> >> it seems that the "Split"-function can only return an array with BASE
> >> 0.
> >>
> >> Can any1 tell me if there is a way to obtain the result from above
> >> function with BASE 1, without having to ReDim the
> >> ExtractData-variable and filling it again with the items from the
> >> StringToArray-variable?
> >>
> >> Hopefully someone can give me an answer to this one.
> >>
> >> TIA,
> >> CoRrRan

If you are feeling the need for speed you can put it into an addin. Addins
are precompiled and if I am correct are marginally faster. Bob you can
correct me if I am wrong.

"CoRrRan" wrote:

> This seems to work... however, I need this function to be fast, as it
> will be used very often in the main-routine. Do you think both functions
> are equally fast when called >1000 times in 1 procedure?
>
> Thanks for the quick reply!
>
> CoRrRan
>
> P.S. The other option is (of course) to change the main procedure from
> being mainly BASE 1 to BASE 0 for using the ExtractData-function. (But
> this requires a lot of (debug-)time, which I do not have.
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
> news:#WBmw5HFFHA.2180@TK2MSFTNGP10.phx.gbl:
> > Try this modified SPlit function (with Option Base 1)
> >
> > '-----------------------------------------------------------------
> > Function pSplit(Text As String, _
> > Optional Delimiter As String = ",") As Variant
> > '-----------------------------------------------------------------
> > Dim i As Long
> > Dim sFormula As String
> > Dim aryEval
> > Dim aryValues
> >
> > If Delimiter = vbNullChar Then
> > Delimiter = Chr(7)
> > Text = Replace(Text, vbNullChar, Delimiter)
> > End If
> > sFormula = "{""" & Application.Substitute(Text, Delimiter,
> > """,""") &
> > """}"
> > aryEval = Evaluate(sFormula)
> > pSplit = aryEval
> >
> > End Function
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "CoRrRan" <CoRrRan~~[at]~~gmail~~[dot]~~com> wrote in message
> > news:Xns96001F833589CoRrRan@194.109.133.29...
> >> I want to use the Split function in a custom made function and want
> >> the output to be used in a main procedure.
> >>
> >> My problem is with the following code:
> >>
> >> ***************************************************
> >> Public Function ExtractData(TextString as String, _
> >> Separator as String, _
> >> Optional Block_N as integer = 0)
> >>
> >> Dim StringToArray As String
> >>
> >> StringToArray = Split(Expression:=DataString, _
> >> Delimiter:=Separator, _
> >> Limit:=-1)
> >>
> >> ExtractData = StringToArray
> >>
> >> End Function
> >> ***************************************************
> >>
> >> (This is not my original function, but it'll do for my question.)
> >>
> >> My output of this function would be in BASE 0, and I want this in
> >> BASE 1.
> >>
> >> I have tried placing "OPTION BASE 1" at the top of my procedure, but
> >> it seems that the "Split"-function can only return an array with BASE
> >> 0.
> >>
> >> Can any1 tell me if there is a way to obtain the result from above
> >> function with BASE 1, without having to ReDim the
> >> ExtractData-variable and filling it again with the items from the
> >> StringToArray-variable?
> >>
> >> Hopefully someone can give me an answer to this one.
> >>
> >> TIA,
> >> CoRrRan
>

Jim,

My instinct tells me that you are correct, but I doubted it woiuld be
significant. So I did some tests.

I ran 4 tests,
- a standard Spli
- the pSplit routine
- pSplit as an addin
- a standard split with a re-basing of the array (0 to 1)

The results were interesting.

As expected, pSplit was substantially slower than Split, but even I wasmazed
at how much slower, a factor of 8-9 times as long.

The addin split, rather surprisingly, was slower that pSplit. Presumably,
the advantage of the pre-compiled code was lost in the time taken to invoke
the addin (I set a reference to the addin).

The standard Split, with rebasing was the second fastset, but again was 2.5
times as slow as a standard split.

If I were the OP and speed is that importnat, I would bite the bullet and
recut the code to be 0 based.

Here are the results.

Split - 8.683594
pSplit - 69.22656
Addin Split - 69.37109
Split & re-base - 19.82813

And here is the code I used to test it.

Sub test()
Dim str As String
Dim ary, ary2
Dim i As Long
Dim j As Long
Dim t

str = "1,2,3,4,5,6,7,8,9,10"

t = timer
For i = 1 To 1000000
ary = Split(str, ",")
Next i
Debug.Print "Split - " & timer - t

t = timer
For i = 1 To 1000000
ary = pSplit(str, ",")
Next i
Debug.Print "pSplit - " & timer - t

t = timer
For i = 1 To 1000000
ary = addinSplit(str, ",")
Next i
Debug.Print "Addin Split - " & timer - t

t = timer
For i = 1 To 1000000
ary = Split(str, ",")
ReDim ary2(1 To UBound(ary) + 1)
For j = 0 To UBound(ary)
ary2(j + 1) = ary(j)
Next j
Next i
Debug.Print "Split & re-base - " & timer - t

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Thomlinson" <JimThomlinson@discussions.microsoft.com> wrote in message
news:FFDCF4FD-2D7C-4C00-87B6-2DD7C8F07804@microsoft.com...
> If you are feeling the need for speed you can put it into an addin. Addins
> are precompiled and if I am correct are marginally faster. Bob you can
> correct me if I am wrong.
>
> "CoRrRan" wrote:
>
> > This seems to work... however, I need this function to be fast, as it
> > will be used very often in the main-routine. Do you think both functions
> > are equally fast when called >1000 times in 1 procedure?
> >
> > Thanks for the quick reply!
> >
> > CoRrRan
> >
> > P.S. The other option is (of course) to change the main procedure from
> > being mainly BASE 1 to BASE 0 for using the ExtractData-function. (But
> > this requires a lot of (debug-)time, which I do not have.
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
> > news:#WBmw5HFFHA.2180@TK2MSFTNGP10.phx.gbl:
> > > Try this modified SPlit function (with Option Base 1)
> > >
> > > '-----------------------------------------------------------------
> > > Function pSplit(Text As String, _
> > > Optional Delimiter As String = ",") As Variant
> > > '-----------------------------------------------------------------
> > > Dim i As Long
> > > Dim sFormula As String
> > > Dim aryEval
> > > Dim aryValues
> > >
> > > If Delimiter = vbNullChar Then
> > > Delimiter = Chr(7)
> > > Text = Replace(Text, vbNullChar, Delimiter)
> > > End If
> > > sFormula = "{""" & Application.Substitute(Text, Delimiter,
> > > """,""") &
> > > """}"
> > > aryEval = Evaluate(sFormula)
> > > pSplit = aryEval
> > >
> > > End Function
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "CoRrRan" <CoRrRan~~[at]~~gmail~~[dot]~~com> wrote in message
> > > news:Xns96001F833589CoRrRan@194.109.133.29...
> > >> I want to use the Split function in a custom made function and want
> > >> the output to be used in a main procedure.
> > >>
> > >> My problem is with the following code:
> > >>
> > >> ***************************************************
> > >> Public Function ExtractData(TextString as String, _
> > >> Separator as String, _
> > >> Optional Block_N as integer = 0)
> > >>
> > >> Dim StringToArray As String
> > >>
> > >> StringToArray = Split(Expression:=DataString, _
> > >> Delimiter:=Separator, _
> > >> Limit:=-1)
> > >>
> > >> ExtractData = StringToArray
> > >>
> > >> End Function
> > >> ***************************************************
> > >>
> > >> (This is not my original function, but it'll do for my question.)
> > >>
> > >> My output of this function would be in BASE 0, and I want this in
> > >> BASE 1.
> > >>
> > >> I have tried placing "OPTION BASE 1" at the top of my procedure, but
> > >> it seems that the "Split"-function can only return an array with BASE
> > >> 0.
> > >>
> > >> Can any1 tell me if there is a way to obtain the result from above
> > >> function with BASE 1, without having to ReDim the
> > >> ExtractData-variable and filling it again with the items from the
> > >> StringToArray-variable?
> > >>
> > >> Hopefully someone can give me an answer to this one.
> > >>
> > >> TIA,
> > >> CoRrRan
> >

Excellent stuff Bob (and Jim thanks for your input as well!)!

Thank you very much for performing such a study into the behaviour,
unfortunately I am not able to do this myself, as I have quite a lot of
work to do.

I will definately have a look at the main procedure and look if I can
change the BASE to 0 for the arrays that use the split function as
output.

Again, many thanks for your help!

Regards,
CoRrRan


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
news:uGljEcNFFHA.624@TK2MSFTNGP15.phx.gbl:

> Jim,
>
> My instinct tells me that you are correct, but I doubted it woiuld be
> significant. So I did some tests.
>
> I ran 4 tests,
> - a standard Spli
> - the pSplit routine
> - pSplit as an addin
> - a standard split with a re-basing of the array (0 to 1)
>
> The results were interesting.
>
> As expected, pSplit was substantially slower than Split, but even I
> wasmazed at how much slower, a factor of 8-9 times as long.
>
> The addin split, rather surprisingly, was slower that pSplit.
> Presumably, the advantage of the pre-compiled code was lost in the
> time taken to invoke the addin (I set a reference to the addin).
>
> The standard Split, with rebasing was the second fastset, but again
> was 2.5 times as slow as a standard split.
>
> If I were the OP and speed is that importnat, I would bite the bullet
> and recut the code to be 0 based.
>
> Here are the results.
>
> Split - 8.683594
> pSplit - 69.22656
> Addin Split - 69.37109
> Split & re-base - 19.82813
>
> And here is the code I used to test it.
>
> Sub test()
> Dim str As String
> Dim ary, ary2
> Dim i As Long
> Dim j As Long
> Dim t
>
> str = "1,2,3,4,5,6,7,8,9,10"
>
> t = timer
> For i = 1 To 1000000
> ary = Split(str, ",")
> Next i
> Debug.Print "Split - " & timer - t
>
> t = timer
> For i = 1 To 1000000
> ary = pSplit(str, ",")
> Next i
> Debug.Print "pSplit - " & timer - t
>
> t = timer
> For i = 1 To 1000000
> ary = addinSplit(str, ",")
> Next i
> Debug.Print "Addin Split - " & timer - t
>
> t = timer
> For i = 1 To 1000000
> ary = Split(str, ",")
> ReDim ary2(1 To UBound(ary) + 1)
> For j = 0 To UBound(ary)
> ary2(j + 1) = ary(j)
> Next j
> Next i
> Debug.Print "Split & re-base - " & timer - t
>
> End Sub
>