次の方法で共有

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

  • [アーティクル]

David Meego - Click for blog homepageToday, 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

Comments

  • Anonymous
    August 04, 2010
    Hi David, I am having a scenario. The FormatCurrency() function will display the amount with $ symbol. My question is how to display the amount with originating currency symbol. Currently, I'm fetching the originating currency symbol using the currency index and concatenating the same with the amount using Format() function in VBA. Is there any other better method available to overcome this issue? Thanks, Prakash

  • Anonymous
    August 04, 2010
    Hi Prakash You raised a good point. I spent some time looking into how to handle Multicurrency currency values and have come up with what I think is a great solution.  It is not 100% perfect, but should handle 99% of the multicurrency formatting options. I will post a blog article next week to explain the techniques I used. Thanks David

  • Anonymous
    August 04, 2010
    Thank you David and I'm eagerly waiting for the article. Thanks, Prakash

  • Anonymous
    August 05, 2010
    Posting by Mark Polino from DynamicAccounting.net msdynamicsgp.blogspot.com/.../currency-values-returned-by-vba-to.html

  • Anonymous
    August 08, 2010
    Posting from Vaidy Mohan at Dynamics GP - Learn & Discuss www.vaidy-dyngp.com/.../currency-values-returned-by-vba-to.html

  • Anonymous
    September 12, 2010
    Yeah, it happens and the solution is awesome. thnx for posting