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_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.