DecimalSeparator, DoubleReadValue
Function DoubleReadValue(varValue As Variant, _
strSourceDecimalSeparator As String, _
Optional strSourceThousandSeparator As String) As Variant
'DOESNT WORK IN ACCESS BECAUSE Application.International(xlDecimalSeparator) IS MISSING IN ACCESS
'variant:cell may be empty or non-numeric
'note: what separator is default is determined by International settings
'thus there may be a mismatch between default interpretation and intended
'in the source of data.
'example: value obtained from source contains .
'intended meaning is decimal separator
'on Dutch machine decimal separator is comma.
'Thus 13.00 is incorrectly interpreted as 1300
'We therefore need a function DoubleReadValue(Value, _
' SourceDecimalSeparator,
' SourceThousandSeparator) > Double
' SourceDecimalSeparator is determined for each source.
'I. remove thousandseparator, if any
'II. if SourceDecimalSeparator <> settings.DecimalSeparator then
' if present SourceDecimalSeparator then split in before and after decimal
'and assemble
Dim strLeft As String
Dim strRight As String
Dim strValue As String
Dim bytLenRight As Byte
On Error GoTo HandleErr
If IsEmpty(varValue) Then GoTo HandleExit
strValue = CStr(varValue)
If Len(strSourceThousandSeparator) > 0 Then
strValue = rStringPurgeChars(strValue, strSourceThousandSeparator)
End If
If strSourceDecimalSeparator <> Application.International(xlDecimalSeparator) Then
If True = rStringSplit(strValue, strSourceDecimalSeparator, strLeft, strRight) Then
DoubleReadValue = CDbl(strLeft)
bytLenRight = Len(strRight)
DoubleReadValue = DoubleReadValue + (CLng(strRight) / 10 ^ bytLenRight)
Else
DoubleReadValue = strValue
End If
Else
DoubleReadValue = CDbl(strValue)
End If
HandleExit:
Exit Function
HandleErr:
Resume HandleExit
End Function