Currency values returned by VBA to a report are multiplied by 10 or 100 with Multicurrency
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.
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, https://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