Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to speed up my VBA application ?
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 . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to speed up my VBA application ?
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. -- Regards, Tom Ogilvy "mg" wrote in message .. . 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Speed up | Excel Discussion (Misc queries) | |||
Can you speed UP drag speed? | Excel Discussion (Misc queries) | |||
macro to close excel application other than application.quit | Excel Programming | |||
need for speed! | Excel Programming | |||
application.quit will not shut off application | Excel Programming |