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_Error:
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_Error:
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:
http://finance.groups.yahoo.com/group/smf_addin/message/6537
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.
|
