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.

Tip -- 9.5 - FAQ: What is the smfForceRecalculation macro and how do I use it?

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

OverviewWhen the RCHGetElementNumber() and RCHGetTableCell() and RCHGetWebData() functions are invoked, they actually extract data from a copy of the web page that was saved by the add-in.  This is done to make the functions run more quickly, because the actual retrieval of a web page from the Internet is the part of the process that takes the most time.  For example, if you were to get 20 or 30 data items from the same web page, this process allows the web page to be retrieved once and then all extractions are done from that single retrieval of the web page from the Internet.  Otherwise, each invocation of those functions would need to grab a new web page.

In general, the process operates as follows:

• The "saved array" is first checked, to see if the web page the data is being extracted from has already been retrieved from the Internet.
• If an entry in the "saved array" is found, the data is extracted from the saved copy of the web page.
• If an entry is not found in the "saved array", the source code of the web page is retrieved from the Internet and then put into the "saved array", which is indexed by URL.  Then, the data is extracted from that saved copy of the web page.

What does the smfForceRecalculation macro do?

When you run this macro, it purges the "saved array" of web pages and then triggers a full recalculation within EXCEL.  Since the "saved array" of web pages has been emptied out, the add-in functions that use the "saved array" of web pages will no longer find a saved copy of a web page, and be forced to retrieve a fresh copy of the web page.  Note, however, that ONLY the first function invocation that uses a given URL will go to the Internet -- because it will save a new copy of the web page, which all other functions using that URL will use instead of going to the Internet to get the web page again.

In addition, this macro will also force all of the RCHGetElementNumber() element definitions to be reloaded.  That was actually the original reason the macro was written -- so I could modify an element definition and tell the add-in to reload the new element definitions and test them to see if the changes worked correctly.

How do I run the smfForceRecalculation macro?

You can use the keyboard shortcut alt-F8 to bring up EXCEL's macro dialog window.  Unfortunately, this window does NOT list macros that are part of an add-in.  That means you need to type in the macro name.  When you complete entering the macro name, you should see the various buttons activate.  You then click "Run" to run the macro.

I have actually assigned the macro to a keyboard shortcut (done from the "Options..." button, which is located several buttons below the "Run" button used above).  I also have added a button to my Quick Access Toolbar in EXCEL 2007 that runs the macro.  It is what I use most often.  In fact, I usually click on it each time before I open a new workbook, to reset the "saved array" of web pages for that workbook.  But you probably don't want to do that if you have a number of workbooks open that use add-in functions, since the macro will force ALL add-in functions in ALL workbooks to recalculate.

Additional Notes

The RCHGetYahooQuotes() and RCHGetYahooHistory() functions do NOT use the "saved array" of web pages.  That's because they both return an entire set of data with each invocation of the function.  So there is no advantage to saving the web page -- since all possible data is extracted from a single Internet access.  In fact, Yahoo is returning CSV files, not web pages.  So the functions just need to parse the CSV files and place the data into the range they were given access to when the formulas were array-entered.

Note, however, that the smfForceRecalculation macro WILL make those functions grab new copies of the CSV files and parse them out.

---

Tip -- 9.4.2 - FAQ: What do I need in addtion to smfForceRecalculation to get my data to refresh?

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

Another possible reason data is not refreshing is that you have your Internet Options set up to cache web pages. Since EXCEL and the add-in use the same Internet access engine as IE, it also utilizes IE's cookies and "Temporary Internet Files" folder.

To check/change your page caching settings for the "Temporary Internet Files" folder:

1. Click on your system's "Start" button.
2. Click on the "Control Panel" option.
3. Double-click on "Internet Options".
4. In the "Browser settings" section, click on the "Settings" button.
5. Under where it says "Check for newer versions of stored pages:", you want to toggle the "Every time I visit the web page" radio button. Without this, the add-in would just retrieve IE's stored copy of the web page instead of the Internet access engine getting a fresh copy of the web page from the Internet.
6. Click "OK" to save the settings and exit
7. Click "OK" to exit
8. Close the "Control Panel" window.

Did you need to change your setting there?