Monday, March 9, 2020

New ZIP file -- Version 2020.03.09

Because of the previously noted issues with Microsoft's changes to VBA's EVALUATE() function, I had to add some special processing for the add-in's use of that function.

So I just uploaded a new version of the ZIP archive.



New files on the web site:


Best practice is to exit EXCEL when updating SMF add-in files.

Sunday, March 8, 2020

Alert -- EXCEL changes for array-entered functions

Microsoft has made some interesting changes for functions that return multiple data items. If you don't array-enter the formula, EXCEL will now automatically expand the range of the formula, "spilling" it into additional rows and columns as needed.

For example, create a new workbook and enter this in cell B2:

=smfPricesByDates("MMM","12/31/2019","03/06/2020")

...and it should automatically fill the results in cells B2 and C2, "spilling" into cell C2.

Now try array-entering it into a single cell, B3, and you will see it doesn't expand the range to include the second returned item.

Now, put "A" in cell C4 and then put that formula in cell B4. It will TRY to expand the range to accommodate the two items being returned, but since C4 already has something in it, EXCEL will return a "#SPILL!" error to indicate it could not "spill" the returned results into more cells.

For the most part, this won't affect many of the SMF add-in array-entered functions. Most are designed to only return enough data to fill the range the function is array-entered over. So if you enter such a formula into a single cell, EXCEL has nothing to spill over.

For example, enter this in cell B6 and you'll only get a single data item (i.e. "Symbol"):

=smfGetYahooPortfolioView("MMM,IBM","01091523",,1)

However, enter this in cell D8 and you'll get a 3x4 range filled as EXCEL spills it over.

=smfGetYahooPortfolioView("MMM,IBM","01091523",,1,3,4)

That's because the last two parameters are telling the function to override it's default of only filling the range it was entered over and instead returning 3 rows and 4 columns of data.

A big advantage of allowing EXCEL to "spill" the function over the range is that it makes it a lot easier to edit. For example, just edit that formula in cell D8 so that the last two parameters are both 10:

=smfGetYahooPortfolioView("MMM,AAPL","01091523",,1,10,10)

After you hit enter, you should see that formula "spill" into a 10 by 10 range. You can edit it again, adding a ticker symbol and a field number:

=smfGetYahooPortfolioView("MMM,AAPL,IBM","0109152324",,1,10,10)

...and it will automatically update what it can in the 10 by 10 range.


Alert -- Issues with some RCHGetElementNumber() elements and some smfUpdateDownloadTable processing

Microsoft has made some changes to the VBA function EVALUATE(). Both the RCHGetElementNumber() function and the smfUpdateDownloadTable macro utilize the EVALUATE() function for some processing paths.

What is happening is that if an add-in function is the first thing in the string passed to the EVALUATE() string, it doesn't evaluate the string and return the result. It simply returns the string that was passed to it.

To see if your version of EXCEL does this, try the following add-in function:

=smfEval("smfStrExtr(""123"",""1"",""3"")")

If that returns a value of "2", you are not affected (yet). However, if it returns:

smfStrExtr("123","1","3")

...then you are affected.

I think I have a fix I can use, but I'm still thinking about whether it's the best way to do it.