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