Improving Performance of Macro That Has a Repetitive Function

• 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")

REFERENCES

For more information about the Now Function, choose the Search button in the Visual Basic Reference and type:

    now

    format