Extend Excel
Volume Number: 6
Issue Number: 1
Column Tag: Intermediate Mac'ing
Extending Excel 
By Vincent Parsons, Austin, TX
Note: Source code files accompanying article are located on MacTech CD-ROM orsource code disks.
Vincent Parsons is a junior in the Science Academy at LBJ High School in Austin,
Texas. He has experience in MacApp, HyperCard, SuperCard, and C, and when he’s not
in school or programming, he runs a Role Playing Game Bulletin Board System. He
would appreciate feedback on this article and would like to hear from anyone having
success implementing DLLs. He can be reached at AppleLink D1977.
A Little History
In November of 1988, an associate of mine attended the Microsoft Excel
Developers Conference. He returned with news that users of Excel for Windows 2.1
(and Excel 2.2 for the Macintosh at its release) have the ability to extend Excel using
Dynamic Link Libraries (DLLs). DLLs are dynamically linked external functions that
can be called directly from Excel macros.
The introduction of DLLs on the Mac opens up new markets for Macintosh
programmers, letting them use the Macintosh’s superior development environments,
and then with very little effort bring the same products to the MSDOS environment.
For this reason, I am discussing development of DLLs for Windows in conjunction with
development of DLLs on the Macintosh.
Microsoft Blues
I began investigating how to implement DLLs using the documentation my
associate had received at the conference. Soon I had written several very simple DLLs
for Windows. To my surprise very few worked correctly (two even crashed Windows).
After much correspondence with Microsoft Technical Support, who informed me that
my problems were due to bugs internal to Excel, I concluded that much of Microsoft’s
documentation on DLLs was incorrect.
I began using a trial subscription of Microsoft OnLine, Microsoft’s technical
support service. While response turn-around was usually fast, all too often the
answer was unacceptable. Answers were typically an acknowledgement that the
problem was internal to Excel. In fact, I was unable get a definite commitment that the
problems would be fixed within a reasonable time period (‘the next release’ is not
satisfactory), nor did Microsoft provide any work-arounds for Excel’s problems.
When asked about programming DLLs for Macintosh Excel 2.2 (in beta test at that
time), they stated that technical support could not support unreleased products. My
feeling is that, at least for this project, the $600 fee for one year of support would not
have been justified. When compared to Apple’s excellent technical support provided
through AppleLink (at a similar yearly rate), Microsoft’s technical support has a long
way to go to reach the standards Macintosh programmers expect.
Since I was reporting bugs, sending corrections to their documentation, and
getting little true support, I suggested that they send me an Excel T-Shirt as some
compensation for my toil. After some ribbing, Technical Support finally responded
telling me that since they currently didn’t have a T-Shirt program they would pass the
suggestion on to a supervisor.
When Macintosh Excel 2.2 was released, I was appalled to discover that the
documentation for it was incorrect also. In fact, the Macintosh version of the Functions
and Macros manual refers to those arcane MSDOS far pointers!
Actually, most forms of DLLs do work, but not as documented in the published
manuals. In this article, I will document how to implement DLLs in both the Macintosh
and Windows environments using as much common source code as possible.
So What Are They Really?
DLLs on the Macintosh are very similar to HyperCard’s XFCNs. They are CODE
resources in their own type of file. The main difference between DLLs and XFCNs is
that Excel DLLs have no call-back routines -- the only data to which the DLL has
access is what was passed to it. DLLs don’t have access to conversion routines such as
those in HyperCard.
Nearly anything thing modal can be done using DLLs. Custom functions can be
created giving Excel increased functionality. An example of this is complex arithmetic.
DLLs also have full access to the Macintosh or Windows toolbox. The possibilities are
endless.
The Excel Side
There are three macro functions that Excel uses to access DLLs -- Register, Call,
and Unregister. For an example of a macro (SampAAA, discussed later), see figure 1.
The Register function for the sample macro is contained in cell A2. The full macro and
spread sheets are included on the source disk.