Improving Performance of Macro That Has a Repetitive Function
ID: Q115906
|
The information in this article applies to:
-
Microsoft Visual Basic programming system, Applications Edition, version 1.0
-
Microsoft Excel for Windows, version 5.0
-
Microsoft Excel for the Macintosh, version 5.0
-
Microsoft Project for Windows, version 4.0
SYMPTOMS
In a Microsoft Visual Basic Programming System, Applications Edition macro,
when you use the Format function to apply specific formatting to an
expression, and you call this function repeatedly in the macro, the
performance of the macro is slow relative to a macro that uses another
function to perform the same action.
WORKAROUND
You can decrease the amount of time required for a macro using the Format
function in a repetitive loop to run by using the Str function. It may be
beneficial to test each of the statements before deciding which function
will work faster in your macro. The following examples use the Now function
to compare the speed of using the Format function versus the Str function
to format a value.
Note that the Format function is more convenient for formatting a value
than the Str function. However, if you are using the Format function in a
loop, where the function is called repeatedly more than 100 times, the
macro may run faster when you use the Str function than when you use the
Format function.
Microsoft provides examples of Visual Basic procedures for illustration
only, without warranty either expressed or implied, including but not
limited to the implied warranties of merchantability and/or fitness for a
particular purpose. This Visual Basic procedure is provided "as is" and
Microsoft does not guarantee that it can be used in all situations.
Microsoft does not support modifications of this procedure to suit customer
requirements for a particular purpose. Note that a line that is preceded by
an apostrophe introduces a comment in the code--comments are provided to
explain what the code is doing at a particular point in the procedure. Note
also that an underscore character (_) indicates that code continues from
one line to the next. You can type lines that contain this character as one
logical line or you can divide the lines of code and include the line
continuation character. For more information about Visual Basic for
Applications programming style, see the "Programming Style in This Manual"
section in the "Document Conventions" section of the Visual Basic "User's
Guide."
Testing Performance of Str Function
Sub Test_Str_Function()
Const Mult = 100000
Dim i as Integer
Dim sVar As String
' Dimension variable dTimer as Double data type
Dim dTimer As Double
' Set value of dTimer to current computer time
dTimer = Now
' Repeat formatting commands 3000 times
For i = 1 To 3000
' Set value of sVar to 30
' Use Mid function to strip leading space added by Str
' (space is added for the sign of the value)
sVar = Mid(Str(30), 2)
' Pad result with leading 0's
sVar = String(4 - Len(sVar), "0") & sVar
Next
' Set value of dTimer to difference between start and end
dTimer = Now - dTimer
' Display amount of time in seconds it took to process loop
MsgBox Format(dTimer * Mult, "0.00000000")
End Sub
Testing Performance of Format Function
Sub Test_Format_Function()
Const Mult = 100000
Dim i as Integer
Dim sVar As String
' Dimension variable dTimer as Double data type
Dim dTimer As Double
dTimer = Now
' Repeat formatting commands 3000 times
For i = 1 To 3000
' Format value 30 as 0030
sVar = Format(30, "0000")
Next
' Set value of dTimer to difference between start and end
dTimer = Now - dTimer
' Display amount of time in seconds it took to process loop
MsgBox Format(dTimer * Mult, "0.00000000")
End Sub
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. We are researching this problem
and will post new information here in the Microsoft Knowledge Base as it
becomes available.
MORE INFORMATION
You can use the Format function in a Visual Basic module to format an
expression according to instructions contained in a format expression. For
example, you can format the number 100 as $100.00 using the following
Format function:
MyNum = Format(100,"$##.00")
If you use the Format function without specifying a format, the Format
function provides the same functionality as the Str function. However, when
you use the Format function to format a positive number as a string, there
is no leading space reserved for the sign of the value; when you use the
Str function, a positive number that is formatted as a string value has a
leading space reserved for the sign of the value.
REFERENCES
For more information about the Now Function, choose the Search button in
the Visual Basic Reference and type:
now
For more information about the Format Function, choose the Search button in
the Visual Basic Reference and type:
format
Additional query words:
efficient long length speed efficiency
Keywords :
Version : :1.0; MACINTOSH:5.0; WINDOWS:4.0,5.0
Platform : MACINTOSH WINDOWS
Issue type :