How do I add VBA capability via AppleScript To Numbers 4

Question

Level 1

9 points

How do I add VBA capability via AppleScript To Numbers 4

I have Sierra and Number4 (latest versions). The VBA addin is available for MS Excel 2011 but 32 bit support is going away after High Sierra. I do not want to spend north of $125 for MS Office 2016 download or a 365 subscription since I only need Excel for a financial VBA addin.

I don't code VBA nor AppleScript and could use some help doing this. I have an old version of MS Windows Office with the VBA addin (financial stuff) and a few spreadsheets for example of usage.

I short where do I start?

TIA, Mike

MacBook Pro with Retina display, macOS Sierra (10.12.6)

Posted on Feb 19, 2018 5:00 PM

Reply

Answer 1

SGIII

Level 8

37,562 points

Feb 24, 2018 4:49 AM in response to Mike Noonan

Hi Mike,

The question remains, what do you want to do in Numbers? (These discussions are not for Excel, though some of us use Excel as well as Numbers).

VBA, as you no doubt know, is for Excel and the rest of the MS Office suite. VBA works quite well in recent versions of Excel on the Mac. (A lot has happened since your 2011 version). Automation of Numbers for the Mac is accomplished not via VBA but via AppleScript (and for the hardy few, JXA or JavaScript for Applications).

What do you need in Numbers that you can't get using the built-in STOCK() and STOCKH() functions? You can see an example of how these two functions work in the 'My Stocks' template at File > New in your menu. They provide functionality similar to the UDF (user defined functions) that SMF appears to add (I haven't tried to test it) to Excel.

STOCK() and STOCKH() and their foreign exchange cousins CURRENCY() and CURRENCYH() retrieve data from Yahoo! Finance. Unfortunately for us, Yahoo! Finance was recently acquired by Verizon, which decided that it would no longer provide near real-time prices. You can still, however, retrieve previous day's closing price and other data via the built-in functions.

If you need data that the built-in functions don't provide but is available via the Yahoo! Finance API then you might still be successful in getting an AppleScript to work. There are many examples of scripts posted on one of the longest and most-read threads in these Numbers for Mac discussions, here.

If you know of other data sources that provide an API that might be accessed via scripting, let us all know.

SG

Reply

Answer 2

jaxjason

Level 5

5,063 points

Feb 21, 2018 11:58 AM in response to Mike Noonan

We need a bit more information. first, VBA is not an addin, but you might have a VBa based financial addin for excel, which is what it sounds like.

What we need is to know what functions from this financial addin you are using.

Once you do that we might be able to find either formula solutions that do the same thing, or would better be able to direct you to applescript support areas that could assist with those specific things.

Thanks

Jason

Reply

Answer 3

SGIII

Level 8

37,562 points

Feb 19, 2018 7:28 PM in response to Mike Noonan

This area is for questions related to Numbers for the Mac. Best pose Excel questions on one of the many forums devoted to Excel for the Mac.

SG

Reply

Answer 4

Feb 20, 2018 6:16 AM in response to SGIII

SGIII wrote:

This area is for questions related to Numbers for the Mac. Best pose Excel questions on one of the many forums devoted to Excel for the Mac.

SG

It sounds, based on the subject line, that the OP wants to add VBA type capabilities to Numbers using AppleScript. If I'm correct, this is the right place. And, you're probably the right person to answer it!

Reply

Answer 5

SGIII

Level 8

37,562 points

Feb 20, 2018 5:51 PM in response to IdrisSeabright

IdrisSeabright wrote:

It sounds, based on the subject line, that the OP wants to add VBA type capabilities to Numbers using AppleScript.

You may be right. If so perhaps the OP can provide specifics on what he is trying to do.🙂

SG

Reply

Answer 6

Mike Noonan Author

Level 1

9 points

Feb 20, 2018 9:33 PM in response to SGIII

At a minimum I'd like to convert a VBA addin that retrieves stock market information, e.g., prices for current or historical purposes, etc. I have the addin and using the VBA editor I assume I can get to the VBA code (a guess). From there, are there AppleScript templates, snips of routine(s), etc. available to translate the VBA code. Or what?

I hope this is specific enough. If not, ask your questions and we'll peel back the onion.

TIA, Mike

Reply

Answer 7

Mike Noonan Author

Level 1

9 points

Feb 23, 2018 10:46 PM in response to jaxjason

Rather than trying to describe what I'm looking for, this website might help. http://ogres-crypt.com/SMF/

SMF is for Stock Market Functions and these are a rich collection of calls made the the SMF addin written in VBA.

Check out the documentation and template links to get a sense of the scope of this ongoing software. I used these some of these functions almost 20 years ago.

One of the big features of SMF concerns performance. Rather than call a Stock()-like function in every cell in a complex of stock tickers (rows) and a set of values (columns), this system will cache the calls to Yahoo, for example, retrieving and caching an entire set of stock records and cache them, e.g., get the same ticker on different dates or different stocks on the same date. Each cell in a row then retrieves its desired data from the cache.

Historically, it would take 20-30 minutes to fill a simple array of 30 tickers by 10 elements over a 58 mbps phone line. And there was some concern that Yahoo might cut you off for hogging access to Yahoo servers.

BTW - I installed VBA into 2011 Excel and tried to run the SMF addin. No joy; an error message occurred looking for the Windows Registry. There is a Mac version available but no one in the Yahoo Group has stepped up to try it, i.e., help debug it.

feedback pls. Thx. Mike

Reply

Answer 8

SGIII

Level 8

37,562 points

Feb 21, 2018 2:16 AM in response to Mike Noonan

What kind of stock information? From what source? Something other than what you can get via the STOCK() and STOCKH() functions in Numbers?

SG

Reply

How do I add VBA capability via AppleScript To Numbers 4

Similar questions