Joel on VBA for Macintosh

On or about 4/27/07 6:01 AM, thus spake "Christopher Smith"
<cbsmithyahoo-inc.com>:

> http://www.joelonsoftware.com/items/2007/04/25.html
>
> Given all the discussions about the removal of VBA from Macs, I thought
> folks would find this to be an interesting read.

It is historically interesting, but it fails to grapple with the main fact:
removing VBA from Excel is not like removing VBA from Word.

I remember when the languages changed *to* VBA (from the nameless Excel
macro language, and from WordBasic). It was just not a big deal. I rewrote
all my macros in the new language and that was that.

So if this were simply a matter of changing from VBA to AppleScript, it
would not be problematic.

The problem is, though, that in Excel, you do not merely script the
application from the outside, the way you do with Word. You write formulas
that determine, in real time, live, what appears in a cell. And at present
you write those formulas in VBA. And AppleScript cannot substitute for this,
either architecturally or mathematically. And it is utterly unclear what
Microsoft intends to do about this.

m.

--
matt neuburg, phd = matttidbits.com, http://www.tidbits.com/matt/
pantes anthropoi tou eidenai oregontai phusei
AppleScript: the Definitive Guide - Second Edition!
http://www.amazon.com/gp/product/0596102119
Take Control of Word 2004, Tiger, and more -
http://www.takecontrolbooks.com/tiger-customizing.html
Subscribe to TidBITS! It's free and smart. http://www.tidbits.com/

At 12:21 PM -0700 4/27/07, Matt Neuburg wrote:
>The problem is, though, that in Excel, you do not merely script the
>application from the outside, the way you do with Word. You write formulas
>that determine, in real time, live, what appears in a cell. And at present
>you write those formulas in VBA. ...

While this is true, it is only true for a vanishingly small proportion of users.
Very few users know of the possibility; even fewer make use of it. Continuing VBA on the Mac for this reason would be (the flea on?) the tail wagging the dog.

Craig

On 2007-04-28, at 01:01, Christopher Smith wrote:

> Given all the discussions about the removal of VBA from Macs, I
> thought
> folks would find this to be an interesting read.

I find Microsoft's reason that's VBA's too complicated to port
disingenuous given that OpenOffice (and hence NeoOffice) has recently
added support for Excel VBA.

--On April 27, 2007 12:21:18 PM -0700 Matt Neuburg <matttidbits.com> wrote:

> The problem is, though, that in Excel, you do not merely script the
> application from the outside, the way you do with Word. You write formulas
> that determine, in real time, live, what appears in a cell. And at present
> you write those formulas in VBA. And AppleScript cannot substitute for
> this, either architecturally or mathematically. And it is utterly unclear
> what Microsoft intends to do about this.

This isn't quite true. The formula language isn't based on VBA. It has no
loops, variables (it operates on values in cells only, so you can fake
variables by storing values in cells), and the IF constructs are very
weird. There are also no VBA objects in the formula language.

You can call VBA subroutines from Excel, but I see nothing that would
prevent Excel from doing the same in Applescript (although I'm far from an
Applescript expert.)

ThinkFree Office does not support VBA but supports a quite a bit of Excel's
formula language.

<http://product.thinkfree.com/desktop/calc/>

On 4/27/07 14:21, "Matt Neuburg" <matttidbits.com> wrote:

> The problem is, though, that in Excel, you do not merely script the
> application from the outside, the way you do with Word. You write formulas
> that determine, in real time, live, what appears in a cell. And at present
> you write those formulas in VBA. And AppleScript cannot substitute for this,
> either architecturally or mathematically. And it is utterly unclear what
> Microsoft intends to do about this.

IIRC, there are three ways in Excel 2004 to script the app: VBA, AppleScript
and Excel Macros. Only VBA is going away

--
John C. Welch Writer/Analyst
Bynkii.com Mac and other opinions
jwelchbynkii.com

On 4/27/07 19:46, "Tom Robinson" <barefootguruparadise.net.nz> wrote:

>> Given all the discussions about the removal of VBA from Macs, I
>> thought
>> folks would find this to be an interesting read.
>
> I find Microsoft's reason that's VBA's too complicated to port
> disingenuous given that OpenOffice (and hence NeoOffice) has recently
> added support for Excel VBA.

Has anyone bothered to do a study of that implementation, to see how
complete it is?

--
John C. Welch Writer/Analyst
Bynkii.com Mac and other opinions
jwelchbynkii.com

Excel for the Mac is, in many ways, a limited-edition version of Excel on
the PC. Although it's had some measure of VBA support for custom functions,
it is not up to par with the PC version, and many custom functions simply
will not work on the Mac that work fine on the PC. Additionally, Excel on
the Mac doesn't support importing data from tables on the web, from ODBC
databases, nor for generating and visualizing multidimensional data from
Microsoft Analysis Services data cubes.

These are all features that are extremely valuable in certain environments
(corporations with data warehouses or academic and private research), but
which are rarely, if ever, touched by "normal" users.

Microsoft seems to have decided that all the Mac Office applications are, in
fact, targeted at these "normal" users, and that anyone who wishes to use
Excel as a platform for more advanced analysis or as a piece in a larger
business intelligence strategy will just have to get themselves a copy of
Windows and Office for the PC. This is pretty much the rationale for not
making Visio or Access available for the Mac, and for that matter, might
underlie the lackluster Exchange support in Entourage.

As much as this grates on me as a Mac fan, I have to admit that nobody in my
company who uses a Mac (the art & design folks) have any need for these
advanced features except for me, and I already have a PC set up right next
to my Mac.

--
Nik :: gerberinik.net
Software picks, serious Mac geekery and productivity tips!
<http://iNik.net/>

Agree with most of what you've written; however, Excel for Mac does indeed support importing data from ODBC sources, albeit not out of the box.

Stefan

http://homepage.mac.com/keydel/plunjerbunni

On or about 4/28/07 5:19 PM, thus spake "John C. Welch" <jwelchbynkii.com>:

> IIRC, there are three ways in Excel 2004 to script the app: VBA, AppleScript
> and Excel Macros. Only VBA is going away

No - Excel macro is written in VBA. So when VBA goes away, internal macros
go away. Unless, as I said in my note, Microsoft has some architectural
solution I haven't been able to imagine.

On or about 4/28/07 5:19 PM, thus spake "Kevin van Haaren"
<kevinvanhaaren.net>:

> This isn't quite true. The formula language isn't based on VBA. It has no
> loops, variables (it operates on values in cells only

No - you're equivocating on the term "formula". We're talking about two
different things. I'm not talking about the "language" that allows you to
put this kind of thing into a cell:

=SUM(A1:A4)

I'm talking about the language that allows you to put *this* kind of thing
into a cell:

=taxOn(A3)

where taxOn() is a function defined elsewhere. Right now, "elsewhere" is "in
a macro", and that macro is written in VBA. In the same way, add-ins are
written in VBA.

So, without VBA, Excel will lose functionality that many spreadsheets depend
on. And unless there is some architectural solution for hooking another
language into its position, it won't even be possible to "translate" the
spreadsheet into some other language.

m.

--
matt neuburg, phd = matttidbits.com, http://www.tidbits.com/matt/
pantes anthropoi tou eidenai oregontai phusei
AppleScript: the Definitive Guide - Second Edition!
http://www.amazon.com/gp/product/0596102119
Take Control of Word 2004, Tiger, and more -
http://www.takecontrolbooks.com/tiger-customizing.html
Subscribe to TidBITS! It's free and smart. http://www.tidbits.com/

Matt Neuburg wrote:
> On or about 4/28/07 5:19 PM, thus spake "John C. Welch" <jwelchbynkii.com>:
>
>
>> IIRC, there are three ways in Excel 2004 to script the app: VBA, AppleScript
>> and Excel Macros. Only VBA is going away
>>
>
> No - Excel macro is written in VBA. So when VBA goes away, internal macros
> go away. Unless, as I said in my note, Microsoft has some architectural
> solution I haven't been able to imagine.
>
From an implementation standpoint, there is a fair bit of difference
between Excel macros and VBA. You can safely assume that Excel macros
will remain. Indeed, a spreadsheet without macros really isn't really a
spreadsheet at all.

--Chris

Quoting Matt Neuburg <matttidbits.com>:
> I'm talking about the language that allows you to put *this* kind of thing
> into a cell:
>
> =taxOn(A3)
>
> where taxOn() is a function defined elsewhere. Right now, "elsewhere" is "in
> a macro", and that macro is written in VBA. In the same way, add-ins are
> written in VBA.
>
> So, without VBA, Excel will lose functionality that many spreadsheets depend
> on. And unless there is some architectural solution for hooking another
> language into its position, it won't even be possible to "translate" the
> spreadsheet into some other language.

add-ins for Excel can also be written in C or C++.
http://support.microsoft.com/kb/178474
http://msdn2.microsoft.com/en-us/library/aa730920.aspx

Your statement that formulas are written in VBA is bit like claiming
Applescript is written in a shell language because it can call shell
scripts from Applescripts. Forumla's can be extended with VBA, and
that is probably the most common way it currently it is extended, but
there is no reason it can't be replaced with something else.

One of the main points behind Microsoft's .Net is allowing any
language to call functions from any other language, so they aren't
exactly novices at accomplishing type of cross-language
interoperability.

Guys,

John Welch is right about what are the best ways to script Excel. At the end of the day, the only one that makes sense for MOST situations is AppleScript ... as there's so much good stuff you get with AppleScript that you don't get with the others.

Why do I say this? Because we've done a TON of work on this -- having just completed MacTech's VBA to AppleScript Transition Guide (see http://www.mactech.com/vba-transition-guide/).

I definitely would not recommend relying on Excel's old macro language ... no telling what the future is there.

The reason that I play with CrossOver Mac is so that I shall be able to
run a very few outstanding Windows Progs when needed: including Excel
with any brilliant programming done by a genius.

There is a foil properties calculation sheet in Excel that gets a
sailing boat closer to the wind and to steer better. That's worth
temporary defection to another religion when we can always come back to
TidBits to explain our folly and the hell it was... :-P Winning a
sailing race is worth any time in purgatory needed; with a beer to cool
the flames.

Although I won't be writing the scripts, in any language, I can sure
make use of pre-programmed Excel worksheets. Do I have to install VBA
in CrossOver to do that? Does Excel run VBA as is?

George