Currency values returned by VBA to a report are multiplied by 10 or 100

Today, I wanted to highlight an issue that you might see when working with Visual Basic for Applications (VBA) and Report Writer.

The issue occurs when using VBA code to return a currency value to be displayed on the report and it appears to be randomly multiplied by 10 or 100.  This is a known issue (Problem Report 59380) and this post should help explain what the problem is and provide an alternative solution.

When working with variables in VBA and wanting to display them on a report, the usual method is to:

1. Create a calculated field of the matching data type and set it to a blank or zero constant value. 
2. Place the calculated field onto the report layout in the desired location.
3. Add the calculated field to Visual Basic.
4. Use VBA code to set the value of the field from your code.

This method works fine for most data types.  However, it does not work correctly when working with currency values.  The problem is that the decimal point is not being correctly interpreted.

For example:

123.00 shows as 123.00
123.40 shows as 1,234.00
123.45 shows as 12,345.00

This gives the appearance of randomly multiplying the value by 10 or 100.  It is not really random, it just depends on how many significant (non-zero) numbers there are after the decimal point.

The solution is to use a string calculated field on the report layout instead.  This field can be formatted to align it to right and populated from VBA using the FormatCurrency() or FormatNumber() functions.

This method is mentioned on the Using VBA with Report Writer post as a note.

To demonstrate the issue I have created a custom report which returns the above three currency values using both methods.  Below is the code used and the output produced. 

VBA Currency Test Code Example

 Dim Value1 As Currency
Dim Value2 As Currency
Dim Value3 As Currency

Private Sub Report_BeforeBody(SuppressBand As Boolean)
    Value1 = 123#
    Value2 = 123.4
    Value3 = 123.45
    
    Currency1.Value = Value1
    Currency2.Value = Value2
    Currency3.Value = Value3
    
    String1 = FormatCurrency(Value1, 2, vbTrue, vbTrue, vbTrue)
    String2 = FormatCurrency(Value2, 2, vbTrue, vbTrue, vbTrue)
    String3 = FormatCurrency(Value3, 2, vbTrue, vbTrue, vbTrue)
End Sub

' Copyright © Microsoft Corporation.  All Rights Reserved.
' This code released under the terms of the
' Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)

Report Output

    
                           VBA Currency Field Setting Test
   
   
  Value in VBA                  Currency Field             Formatted String
   
        123.00                         $123.00                      $123.00
        123.40                       $1,234.00                      $123.40
        123.45                      $12,345.00                      $123.45

NOTE: The package with the VBA Currency Test custom report is attached to the bottom of this post.

Hope this helps. 

David

09-Aug-2010: See follow-up article: Currency values returned by VBA to a report are multiplied by 10 or 100 with Multicurrency.

VBA Currency Test.zip