How to speed up my VBA application ?

[mg]

Hi
First of all i use XL97 .
I'm finishing my Vba application , and now I'm facing some optimalisation
issues . After few tests i found that two functions have major impact on
calculation time .
The first is Split97 , well known function (probably) written by Don Oglivy
, this function splits string into array .
The second function is CheckVal . This function checks if val is numeric
and if it's value is <> 0 .
To speed up my code i wrote my own verion of Split97 function ( I'm not
sure if someone did it before me ) . I'm not sure , but it seems to work
little bit faster than Split97 function . Is it possible ?
Have anyone any idea how to accelerate execution time for Splt or Split97
and CheckVal functions further ?

Here is the code of all functions i mentioned before .

Function Split97(sStr As String, sDelim As String) As Variant
Split97=Evaluate("{""" & Application.Substitute(sStr, sDelim, """,""") _
& """}")
End Function


Function CheckVal(val As Variant) As Boolean
On Error Resume Next
CheckVal = (CDbl(val) <> 0)
End Function


'my own version of Split97
Function Splt(str As String, Separator As String) As Variant
' function returns False if str=Null or str=""

Dim TempArray() As String
Dim TempStr As String
Dim i As Integer
Dim NextPos As Integer, CurPos As Integer

If (str = Null) Or (Len(str) = 0) Then
Splt = False
Exit Function
ElseIf InStr(1, str, Separator) = 0 Then
Splt = Array(str)
Else
NextPos = 0
CurPos = 1
i = 1
Do
ReDim Preserve TempArray(i)
NextPos = InStr(CurPos, str, Separator)
TempArray(i) = Mid(str, CurPos, NextPos - (CurPos))
CurPos = NextPos + 1
i = i + 1
Loop While InStr(NextPos + 1, str, Separator) <> 0
ReDim Preserve TempArray(i)
TempArray(i) = Right(str, Len(str) - (NextPos))
Splt = TempArray()
End If
End Function


Ps. Sorry for my terrible English .

 

[Tom Ogilvy]

Removing most of the Redim Preserve makes it a little faster, makes it less
flexible:

Function SpltAA(str As String, Separator As String) As Variant
' function returns False if str=Null or str=""

Dim TempArray() As String
Dim TempStr As String
Dim i As Integer
Dim NextPos As Integer, CurPos As Integer
ReDim TempArray(1 To 256)
If (str = Null) Or (Len(str) = 0) Then
SpltAA = False
Exit Function
ElseIf InStr(1, str, Separator) = 0 Then
SpltAA = Array(str)
Else
NextPos = 0
CurPos = 1
i = 1
Do
NextPos = InStr(CurPos, str, Separator)
TempArray(i) = Mid(str, CurPos, NextPos - (CurPos))
CurPos = NextPos + 1
i = i + 1
Loop While InStr(NextPos + 1, str, Separator) <> 0
ReDim Preserve TempArray(1 To i)
TempArray(i) = Right(str, Len(str) - (NextPos))
SpltAA = TempArray()
End If
End Function


Since the checkval is a single line, I would not use a function - just
repeat the code where you need it. As an example, if I used my Split97
inline (since it is a single line of code), it beats your Splt function
called as a function. So there is overhead in calling a function.