TechKnowledge Content
Question: VBA code has been written that compares two currency values. Shown below is an example of this code:
If ccur(cost) > ccur(1000) then
When this line of code executes, it causes a datatype incompatibility or type mismatch type error message.The Windows Currency settings are currently defined as English "pounds" symbol for the currency symbol. If I change the currency symbol to "$", the code runs successfully. What is occurring here and is there any way to work around this problem?
Answer: In VBA, the Ccur() function always respects the Control Panel currency settings when evaluating this field. Since the Dynamics VBA implement will always pass its field values as strings, you will have this problem anytime a currency value is evaluated that does not match the currency settings in Windows.
In the VBA help file, it indicates that the data-type conversion functions should be used instead of Val to provide internationally aware conversions from one data type to another. For example, when you use CCur() function, different decimal separators, different thousand separators, and various currency options are properly recognized depending on the locale setting of your computer.
Under this understanding, note this about working with Microsoft Dynamics GP:
-
CCur("œ1000") will equal a type mismatch (i.e. this string is not a valid currency) if the Windows Currency setting is not set to "pound".
-
If it is set to "pound" then CCur("œ1000") will return a value of 1000, but then CCur("$1000") will equal a type mismatch error.
The best way to deal with currencies from VBA would be to write a function that will convert any string with non-numeric symbols in front to a numeric (or currency) value. For example, like this:
If CurrencyValueFromDynField(cost) > ccur(1000) then
where the function named CurrencyValueFromDynField is defined as follows. This code correctly handles negative numbers that have proceeding or trailing minus signs, that are surrounded by brackets, or that have spaces between the currency symbol and the number.
Public Function CurrencyValueFromDynField(FieldString As String) As Currency
Dim i As Integer
Dim TempChar, TempString As String
' Strip off all non-numeric characters
TempString = ""
For i = 1 To Len(FieldString)
TempChar = Mid(FieldString, i, 1)
If InStr("1234567890.,-()", TempChar) > 0 Then
TempString = TempString & TempChar
End If
Next
FieldString = TempString
' Return the numeric value of the string (as a currency data type )
CurrencyValueFromDynField = CCur(FieldString)
End Function
This article was TechKnowledge Document ID:10036
