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

David Meego - Click for blog homepageLast week, I discussed an issue where Currency values returned by VBA to a report are multiplied by 10 or 100. To workaround the issue we used string calculated fields rather than currency calculated fields on the report and formatted our values with the FormatCurrency() or FormatNumber() functions (depending on if we wanted the currency symbol or not).

A comment on that post asked how do you handle multicurrency. The above method works great for functional currency, but what if you want to to show a multicurrency value that uses a different currency symbol.  Good question. 

The quick and nasty solution is to use FormatNumber() and drop the currency symbols entirely and then add the Currency ID onto the report to say "These numbers with no currency symbol are really XX currency".  Not a very nice solution.

There must be a nicer way.... Well after a bit of research I found there was.

Just before I explain the technique, I want to highlight the following Knowledge Base (KB) article which can be used to overcome errors when currency symbols interfere with calculations.

If you have any issues reading the values of multicurrency fields from a report, use this KB to strip out the non-numeric characters to get the numeric value. 

Note: My testing with Microsoft Dynamics GP 2010 showed that a currency field displayed as C$1,000.00 in the report was brought into VBA as a currency value of 1000.00 and not a string value of "C$1,000.00". So the workaround in the KB article might not be needed anymore. 


I started off with a plan to read a multicurrency field from the report and this would provide a string representation of what the currency symbol is.  The note above mentions that this does not work as the currency field is now returned to VBA as currency data and so I cannot get the formatted field.

After some searching, I found the Report Writer function rw_FormatCurrencyToString() in the Purchasing Series.  This RW function takes two parameters, a Currency Index and a currency value, and outputs a string with the formatted currency value. Perfect. Just what I needed.

So, I created a string calculated field defined as follows:

FUNCTION_SCRIPT(  rw_FormatCurrencyToString  Currency Index  -1000.00000  )

I used the currency constant of -1000.00000 so that I would be able to see if the thousands were grouped and how a negative value was represented.

Adding this field to the report and making it invisible (via Tools >> Field Options, Ctrl-F or double clicking).  I added this now hidden field and the other required fields to VBA and started working on the code to handle the currency formatting.

The VBA code uses a number of "Template" variables to describe the format.  What is the prefix? What is the suffix? How many decimal places? Is a negative value shown with parenthesis? Are the negative symbols shown after the suffix or before the prefix. Is the minus sign before or after the number? Are thousands grouped?

The ReadTemplateprocedure then analyzes the template as provided by the rw_FormatCurrencyToString() RW Function and sets the "Template" variables accordingly.

Then when I want to output a currency value formatted to the same template, I can call the FormatTemplate function.

To demonstrate the techniques I have updated the custom report from the previous post.  Below is the code used and the output produced. 

VBA Currency Test with Multicurrency Code Example

 Option Explicit

Dim TemplateStart As String
Dim TemplateEnd As String
Dim TemplateDecimals As Integer
Dim TemplateNegative As Integer
Dim TemplateNegEnd As Boolean
Dim TemplateNegStart As Boolean
Dim TemplateNegAfter As Boolean
Dim TemplateGroup As Integer

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

    ReadTemplate (CurrencyTemplate) ' Using amount of -1,000
    
    Value1 = Value1 + CCur(Currency4)
    Value2 = Value2 + CCur(Currency5)
    Value3 = Value3 + CCur(Currency6)

    Currency4 = Value1
    Currency5 = Value2
    Currency6 = Value3
    
    String4 = FormatTemplate(Value1)
    String5 = FormatTemplate(Value2)
    String6 = FormatTemplate(Value3)

End Sub

Private Sub ReadTemplate(Template As String)
    Dim i As Integer
    Dim Char As String
    Dim DPFound As Boolean
    Dim NoFound As Boolean
    
    TemplateStart = ""
    TemplateEnd = ""
    TemplateDecimals = 0
    TemplateNegative = vbFalse
    TemplateNegEnd = False
    TemplateNegStart = False
    TemplateNegAfter = False
    TemplateGroup = vbFalse
    
    DPFound = False
    NoFound = False
    For i = Len(Template) To 1 Step -1
        Char = Mid(Template, i, 1)
        If InStr("0123456789", Char) Then
            NoFound = True
            If Not DPFound Then
                TemplateDecimals = TemplateDecimals + 1
            Else
                ' Ignore
            End If
        ElseIf InStr(",.'", Char) Then
            If Not DPFound Then
                DPFound = True
            Else
                TemplateGroup = vbTrue
            End If
        ElseIf InStr("(", Char) Then
            TemplateNegative = vbTrue
            If i = 1 Then
                TemplateNegStart = True
            End If
        ElseIf InStr(")", Char) Then
            TemplateNegative = vbTrue
            If i = Len(Template) Then
                TemplateNegEnd = True
            End If
        ElseIf InStr("-", Char) Then
            If Not NoFound Then
                TemplateNegAfter = True
            End If
            If i = 1 Then
                TemplateNegStart = True
            ElseIf i = Len(Template) Then
                TemplateNegEnd = True
            End If
        Else
            If NoFound Then
                TemplateStart = Char + TemplateStart
            Else
                TemplateEnd = Char + TemplateEnd
            End If
        End If
    Next
End Sub

Private Function FormatTemplate(Value As Currency) As String
    FormatTemplate = FormatNumber(Abs(Value), TemplateDecimals, vbTrue, TemplateNegative, TemplateGroup)
    If Value < 0# Then
        If TemplateNegative = vbTrue Then
            If TemplateNegStart Then
                FormatTemplate = "(" & TemplateStart & FormatTemplate
            Else
                FormatTemplate = TemplateStart & "(" & FormatTemplate
            End If
            If TemplateNegEnd Then
                FormatTemplate = FormatTemplate & TemplateEnd & ")"
            Else
                FormatTemplate = FormatTemplate & ")" & TemplateEnd
            End If
        Else
            If TemplateNegAfter Then
                If TemplateNegEnd Then
                    FormatTemplate = TemplateStart & FormatTemplate & TemplateEnd & "-"
                Else
                    FormatTemplate = TemplateStart & FormatTemplate & "-" & TemplateEnd
                End If
            Else
                If TemplateNegStart Then
                    FormatTemplate = "-" & TemplateStart & FormatTemplate & TemplateEnd
                Else
                    FormatTemplate = TemplateStart & "-" & FormatTemplate & TemplateEnd
                End If
            End If
        End If
    Else
        FormatTemplate = TemplateStart & FormatTemplate & TemplateEnd
    End If
End Function

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

Report with Multicurrency 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
   
   
    Multicurrency                         Z-C$               Canadian Dollars
   
 1000.00 + 123.00                   C$1,123.00                     C$1,123.00
 1000.00 + 123.40                  C$11,234.00                     C$1,123.40
 1000.00 + 123.45                 C$112,345.00                     C$1,123.45

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

There might be a few currency format configurations that the VBA code cannot handle, for example: grouping thousands using spaces.  It is not perfect, but it can handle almost everything.

Hope this is useful. 

David

VBA Currency Test MC.zip