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

Report Output

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 (6)

  1. Prakash Prasannam says:

    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

  2. David Musgrave says:

    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

  3. Prakash Prasannam says:

    Thank you David and I'm eagerly waiting for the article.

    Thanks,

    Prakash

  4. David Musgrave says:

    Posting by Mark Polino from DynamicAccounting.net

    msdynamicsgp.blogspot.com/…/currency-values-returned-by-vba-to.html

  5. David Musgrave says:

    Posting from Vaidy Mohan at Dynamics GP – Learn & Discuss

    http://www.vaidy-dyngp.com/…/currency-values-returned-by-vba-to.html

  6. Currency says:

    Yeah, it happens and the solution is awesome. thnx for posting

Skip to main content