VBA Split-function

[CoRrRan]

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

 

[Bob Phillips]

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]

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]

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

--

HTH

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

 

[Guest]

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.

 

[Bob Phillips]

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)

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.

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.

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
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

 

[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