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