Tuesday, May 19, 2020

Tip -- Number conversions for non U.S. regional settings

---In smf_addin@yahoogroups.com, <prosuite2000i@...> wrote :

Best way to resolve regional settings (working for months) is

' Replace all occurences of CDec to RegionalCDec in you code.
' and keep CDec unchanged in RegionalCDec function
' Add in modUtilities
Public Function RegionalCDec(ByVal varUS2Regional As Variant) As Variant
Dim varRegionalNumber As Variant
Dim strRegionalDecimalSep As String
On Error GoTo RegionalCDec_Error
If UCase$(varUS2Regional) = LCase$(varUS2Regional) Then
    ' Likely a number
    strRegionalDecimalSep = Mid$(1 / 2, 2, 1)
    If strRegionalDecimalSep <> "." Then
        ' Local is not US
        varRegionalNumber = varUS2Regional
        If InStrB(varRegionalNumber, ",") <> 0 Then
            ' Get rid of Thousand Separator
            varRegionalNumber = Replace$(varRegionalNumber, ",", vbNullString)
        End If
        If InStrB(varRegionalNumber, ".") <> 0 Then
            ' Replace US Decimal Separator to Local
            varRegionalNumber = Replace$(varRegionalNumber, ".", strRegionalDecimalSep)
        End If
        'varRegionalNumber = CDec(varRegionalNumber)
        If IsNumeric(varRegionalNumber) Then varUS2Regional = varRegionalNumber
    End If
End If
RegionalCDec = CDec(varUS2Regional)
End Function

Using CDate has the same side effects with Regional Settings.
' Replace CDate in RCHGetYahooHistory by RegionalCDate(vItem(0), "yyyy-mm-dd")
' Replace CDate in smfGetMSNHistory by RegionalCDate(vItem(0), "m/d/yyyy")
' Add in modUtilities
Public Function RegionalCDate(ByRef varUS2Regional As Variant, Optional ByVal strDateFormat As String = "mm/dd/yyyy") As Date
Dim varRegionalDate As Variant
Dim lngLen As Long
Dim strDay As String, strMonth As String, strYear As String
Dim lngDay As Long, lngMonth As Long, lngYear As Long
Dim lngPosDay As Long, lngPosMonth As Long, lngPosYear As Long
Dim lngLenDay As Long, lngLenMonth As Long, lngLenYear As Long
On Error GoTo RegionalCDate_Error
lngLen = Len(varUS2Regional)
If lngLen <> 0 Then
    If UCase$(varUS2Regional) = LCase$(varUS2Regional) Then
        ' Likely a date
        strDateFormat = LCase$(strDateFormat)
        lngLenDay = lngLen - Len(Replace(strDateFormat, "d", vbNullString))
        If lngLenDay <> 0 Then
            lngPosDay = InStr(strDateFormat, String$(lngLenDay, "d"))
            If lngPosDay <> 0 Then
                strDay = Mid$(varUS2Regional, lngPosDay, 2)
                If Not IsNumeric(strDay) Then _
                strDay = Mid$(varUS2Regional, lngPosDay, lngLenDay)
            End If
        End If
        lngLenMonth = lngLen - Len(Replace(strDateFormat, "m", vbNullString))
        If lngLenMonth <> 0 Then
            lngPosMonth = InStr(strDateFormat, String$(lngLenMonth, "m"))
            If lngPosMonth <> 0 Then
                strMonth = Mid$(varUS2Regional, lngPosMonth, 2)
                If Not IsNumeric(strDay) Then
                    strMonth = Mid$(varUS2Regional, lngPosMonth, lngLenMonth)
                    If Not IsNumeric(strMonth) Then strMonth = _
                    (InStrB("JanFebMarAprMayJunJulAugSepOctNovDec", strMonth) + 5) \ 6
                End If
            End If
        End If
        lngLenYear = lngLen - Len(Replace(strDateFormat, "y", vbNullString))
        If lngLenYear <> 0 Then
            lngPosYear = InStr(strDateFormat, String$(lngLenYear, "y"))
            If lngPosYear <> 0 Then strYear = Mid$(varUS2Regional, lngPosYear, lngLenYear)
        End If
        varRegionalDate = DateSerial(strYear, strMonth, strDay)
        If IsDate(varRegionalDate) Then varUS2Regional = varRegionalDate
    End If
End If
RegionalCDate = CDate(varUS2Regional)
End Function

--- On Mon, 10/11/10, Randy Harmelink <rharmelink@...> wrote:

I think there are only two ways to overcome it:

1. Change to the US regional settings so the VBA function does the conversion correctly.
2. Change the add-in conversion to do the process a different way with another function.

Since the method suggested to me for (2) didn't work for US regional settings, I didn't really look at it very long. See:


On Mon, Oct 11, 2010 at 5:38 AM, po_zi_2000 <zetelsl@...> wrote:

Do you know how to evercome this?
Is there anything I can do to fix it in my own templates?
Since I will normally work under European settings once fixed there won't be any conflicts with US settings.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.