Microsoft KB Archive/115906

{|
 * width="100%"|

Improving Performance of Macro That Has a Repetitive Function

 * }

Q115906

-

The information in this article applies to:


 * Microsoft Visual Basic for Applications 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.