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

This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

Last 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.

• Type mismatch error comparing currency fields in VBA (KB 843656) 

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

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.