Learning VBA

[RickGreg]

I'm thinking about taking the dive to learn some basic VBA for Excel so I
don't need to pepper this board with questions.

Can anyone suggest a solid resource for someone starting from ZERO to learn
the basics of VBA?

Thx!

 

[Gordon Rainsford]

I'm thinking about taking the dive to learn some basic VBA for Excel so I
don't need to pepper this board with questions.

Can anyone suggest a solid resource for someone starting from ZERO to learn
the basics of VBA?

1) Record some macros, look at them and try editing them, use the Visual
Basic help pages.
2) "Excel VBA In Easy Steps"

 

[little_creature]

I use book excel visual basics for application step by step from Reed
Jacobson published by MS which is from my point of view very well prepared.

 

[Jim Gordon]

Hi everyone,

There's something you should know about VBA before you get too deeply
involved. Microsoft is discontinuing support for VBA.

On May 20, 2002 Richard McAniff, Corporate Vice President, said that
Office (presumably for Windows - they STILL don't think routinely
acknowledge the Mac version when appropriate) would continue to support
VBA at the same level for at least 2 additional development cycles.

The first big chink in that policy has appeared. PowerPoint 2007 does
not have a macro recorder. So the level of support has already slipped.

If it were up to me to advise you I would recommend that you consider
learning AppleScript for Mac automation or C# for cross-platform
applications.

-Jim Gordon
Mac MVP

 

[Jay]

What will that actually mean when it comes to Excel?

No macros, no VBA?

If so, what will replace it?

-Jay-

 

[Jim Gordon]

Hi Jay,

Unfortunately, I am unable to predict the future with certainty.

I can tell you that Microsoft has announced that they plan to
discontinue visual basic for applications on Office for Mac and Office
for Windows.

I can tell you that Microsoft Office sales depend heavily upon
programmability features.

I can tell you that Microsoft has stated publicly they plan to support
AppleScript in the future, so that is why I commented that I think
learning AppleScript for Mac Office automation is a safe bet.
AppleScript can replace VBA right now in Office 2004 and if Microsoft
keeps their word it will also be carried forward into the next version
of Mac Office.

-Jim

 

[Jim]

Very bad news indeed.

Hi Jay,

Unfortunately, I am unable to predict the future with certainty.

I can tell you that Microsoft has announced that they plan to
discontinue visual basic for applications on Office for Mac and Office
for Windows.

I can tell you that Microsoft Office sales depend heavily upon
programmability features.

I can tell you that Microsoft has stated publicly they plan to support
AppleScript in the future, so that is why I commented that I think
learning AppleScript for Mac Office automation is a safe bet.
AppleScript can replace VBA right now in Office 2004 and if Microsoft
keeps their word it will also be carried forward into the next version
of Mac Office.

-Jim

 

[RickGreg]

Hi Jay,

Unfortunately, I am unable to predict the future with certainty.

I can tell you that Microsoft has announced that they plan to
discontinue visual basic for applications on Office for Mac and Office
for Windows.

I can tell you that Microsoft Office sales depend heavily upon
programmability features.

I can tell you that Microsoft has stated publicly they plan to support
AppleScript in the future, so that is why I commented that I think
learning AppleScript for Mac Office automation is a safe bet.
AppleScript can replace VBA right now in Office 2004 and if Microsoft
keeps their word it will also be carried forward into the next version
of Mac Office.

-Jim

Jim-

Back to my original question.... Nearly all the VBA-driven Excel files I
create are cross-platform. Does apple-scripting carry over to Windows
Excel? Or is C# my only future option (as far as you can see right now)?

Huge bummer, and big dent for cross-platformers like me.

Thx. -Rick

 

[Bob Greenblatt]

Jim-

Back to my original question.... Nearly all the VBA-driven Excel files I
create are cross-platform. Does apple-scripting carry over to Windows
Excel? Or is C# my only future option (as far as you can see right now)?

Huge bummer, and big dent for cross-platformers like me.

Thx. -Rick

Rick,

PMJI, but I am a cross platform programmer also. Like Jim, I also have no
knowledge of the ultimate path Microsoft will take regarding this. However,
Apple script is unique to Macintosh and does not (and probably never will)
carry over to the windows operating system.

I feel safe in assuming that VBA code will work for several Office
iterations. The old XLM macro language still works fine in both current
Windows and Macintosh Excel versions. (And, in some cases, executes faster
than VBA.) What does seem to be in question is how and in what environment
the VBA code will get generated.

So, for the bottom line for your original question, VB will probably survive
for several (2, 3, 10?) more iterations of office. Whether it is worth your
time to invest in learning it or not for this finite time period is your
call.

 

[Jim Gordon]

Jim-

Back to my original question.... Nearly all the VBA-driven Excel files I
create are cross-platform. Does apple-scripting carry over to Windows
Excel? Or is C# my only future option (as far as you can see right now)?

Huge bummer, and big dent for cross-platformers like me.

Thx. -Rick

Hi Rick,

AppleScript is Mac only.

As for the future I see nothing is certain. When Microsoft announced
that VBA would not be supported there was an immediate backlash by VBA
developers, which caused Microsoft folks to think twice about their
decision, or at least the timetable for implementation.

So far Microsoft has not withdrawn the announcement that at some point
VBA support will end. But no cut-off date has been announced, either, so
Microsoft can keep their options open.

It's entirely possible that no decision about "when" has been made yet,
and certainly no news release about it has been made. Without a crystal
ball you and I can only guess when and/or if VBA going away will or
won't happen.

It could turn out the way Bob hints: VBA code will continue to work
with Office in the future, but the default programming environment could
change to some other programming language.

There are a zillion unknowns to consider. How will Windows and Apple's
upcoming Leopard interact? Might it be possible someday to run Visual
Studio on a Mac (within a Windows environment) and use that as the
developer interface for Mac Office somehow? Will AppleScript be able to
make compiled Code for Windows versions of Office? Stranger things have
happened.

My point is that VBA is likely to go away. There are hundreds of
possible scenarios for the future. Be ready for some big changes with
regard to office programmability for both Mac and PC. My hunch is that
whatever programming language becomes the default that the Mac and PC
versions of Office will have somewhat better cross-platform capabilities
than they do now. Keep in mind that Mac Office and Windows Office now
and in the future each have unique capabilities, so not everything will
always work on each platform.

So for now, it's OK to use VBA as your developer language. Much of what
you learn will still be applicable to whatever language takes its place.
Office will still have objects, methods, properties, actions, etc, but
you will get at them with different syntax sometime in the future.

Stick with VBA version 5 (that's the one supported on the Mac). Avoid
Active-X as if it were the plague and don't use anything that was new in
VBA 6 (there's work-arounds for the few new things). Try your code on
both Mac and Windows versions of Office. When something doesn't seem to
work as expected just post your code here and ask for help.

I don't want to discourage you from getting started on programming. Go
for it. Just keep an eye on what Microsoft is doing. Invest in learning
about Office objects, properties, methods etc and don't concentrate too
much on learning VBA syntax and the VBA editor more than what you need
to accomplish your immediate programming goals.

-Jim Gordon
Mac MVP

 

[Mike Barton]

Hi Rick,

AppleScript is Mac only.

As for the future I see nothing is certain. When Microsoft announced
that VBA would not be supported there was an immediate backlash by VBA
developers, which caused Microsoft folks to think twice about their
decision, or at least the timetable for implementation.

So far Microsoft has not withdrawn the announcement that at some point
VBA support will end. But no cut-off date has been announced, either, so
Microsoft can keep their options open.

It's entirely possible that no decision about "when" has been made yet,
and certainly no news release about it has been made. Without a crystal
ball you and I can only guess when and/or if VBA going away will or
won't happen.

It could turn out the way Bob hints: VBA code will continue to work
with Office in the future, but the default programming environment could
change to some other programming language.

There are a zillion unknowns to consider. How will Windows and Apple's
upcoming Leopard interact? Might it be possible someday to run Visual
Studio on a Mac (within a Windows environment) and use that as the
developer interface for Mac Office somehow? Will AppleScript be able to
make compiled Code for Windows versions of Office? Stranger things have
happened.

My point is that VBA is likely to go away. There are hundreds of
possible scenarios for the future. Be ready for some big changes with
regard to office programmability for both Mac and PC. My hunch is that
whatever programming language becomes the default that the Mac and PC
versions of Office will have somewhat better cross-platform capabilities
than they do now. Keep in mind that Mac Office and Windows Office now
and in the future each have unique capabilities, so not everything will
always work on each platform.

So for now, it's OK to use VBA as your developer language. Much of what
you learn will still be applicable to whatever language takes its place.
Office will still have objects, methods, properties, actions, etc, but
you will get at them with different syntax sometime in the future.

Stick with VBA version 5 (that's the one supported on the Mac). Avoid
Active-X as if it were the plague and don't use anything that was new in
VBA 6 (there's work-arounds for the few new things). Try your code on
both Mac and Windows versions of Office. When something doesn't seem to
work as expected just post your code here and ask for help.

I don't want to discourage you from getting started on programming. Go
for it. Just keep an eye on what Microsoft is doing. Invest in learning
about Office objects, properties, methods etc and don't concentrate too
much on learning VBA syntax and the VBA editor more than what you need
to accomplish your immediate programming goals.

-Jim Gordon
Mac MVP

Just caught up with this conversation. I also develop an Excel cross
platform package (80% Mac users) and am about to work on an upgrade.

My conclusion from the very helpful contributions is that I may as well
continue to develop in VBA for the immediate future (it was originally
written in XLM).

However, I know this is straying from the MS stable (which may make this a
problematic issue on an MS newsgroup), but I would welcome an evaluation of
RealBasic (which also provides cross-platform Office automation) vs VBA.
I've never needed it before but, as the future is unclear, I would imagine
that developers in our position would be giving it serious consideration.

Mike Barton

 

[trasto]

My point is that VBA is likely to go away. There are hundreds of
possible scenarios for the future. Be ready for some big changes with
regard to office programmability for both Mac and PC. My hunch is that
whatever programming language becomes the default that the Mac and PC
versions of Office will have somewhat better cross-platform capabilities
than they do now. Keep in mind that Mac Office and Windows Office now
and in the future each have unique capabilities, so not everything will
always work on each platform.

So for now, it's OK to use VBA as your developer language. Much of what
you learn will still be applicable to whatever language takes its place.
Office will still have objects, methods, properties, actions, etc, but
you will get at them with different syntax sometime in the future.

Stick with VBA version 5 (that's the one supported on the Mac). Avoid
Active-X as if it were the plague and don't use anything that was new in
VBA 6 (there's work-arounds for the few new things). Try your code on
both Mac and Windows versions of Office. When something doesn't seem to
work as expected just post your code here and ask for help.

I don't want to discourage you from getting started on programming. Go
for it. Just keep an eye on what Microsoft is doing. Invest in learning
about Office objects, properties, methods etc and don't concentrate too
much on learning VBA syntax and the VBA editor more than what you need
to accomplish your immediate programming goals.

-Jim Gordon
Mac MVP

Rather than start a new thread I think I could chime in on a real
experience with VBA in Excel, the frustration it is causing and hope
that maybe someone may have a solution for this mess.

My wife did an excel course via email (it was an educational thing run
through her education department) and was aimed at PC Excel versions.
They had no legal way to exclude my wife's mac Excel (under Office v.X)
and so she was able to conclude the course successfully after working
around and addressing many problems along the way (some of which were
VBA problems).

For the end of course project she enlisted the help of her brother who
has knowledge of of VB on Windows but no knowledge of Macs. They
managed to put something together that depended on VBA and worked on
both Mac OSX 10.2.8 with Office v.X and Windows XP. The thing was quite
complex but worked.

She has since upgraded to Office 2004 and Mac OSX 10.4.7 but that Excel
file will not work correctly on this platform (it still runs fine on XP
and current versions of Office for Windows.

The file itself is designed to simulate the hangman game (she had been
asked to be as creative as possible). The file opens but when you click
on a letter this message is returned:

Run time-error '5':
Invalid procedure call or argument

That message is clear enough but when she clicks the 'Debug' button to
see the offending lines, this is what is highlighted:

ActiveSheet.Shapes(Range("A8").Value).Visible = True

Let me put that into a bit more context:

Sub Hang_calcular_lletra()
ActiveSheet.Calculate
ActiveSheet.Shapes(Range("A8").Value).Visible = True
ActiveSheet.Shapes(Range("B8").Value).Visible = False
Range("D15").Select
End Sub

We've all but given up on finding a solution for this but it's strange
that the file still works without issue on Windows but fails on Office
2004 for Mac (it still runs fine on Office v.X and 10.2.8). Her brother
sees no valid reason for the file to fail on Office 2004 other than
'ActiveSheet' which he says may have changed on later versions of VBA
but shouldn't be enough for compatibility to break down.

I have no idea of VBA but this whole process has been full of mines. My
wife spent a huge amount of time putting this together and avoiding
lot's of potential areas for problems only to see it all break after
one upgrade. Applying all the Office 2004 updates hasn't helped.

If anyone has any idea of how to eliminate this problem I would be very
grateful.

 

[JE McGimpsey]

Run time-error '5':
Invalid procedure call or argument

That message is clear enough but when she clicks the 'Debug' button to
see the offending lines, this is what is highlighted:

ActiveSheet.Shapes(Range("A8").Value).Visible = True

That means that there is no shape on the active sheet with a name that's
the same as the value in the active sheet's A8 cell.

The code works fine as written for me in XL04, as long as the two shape
names in A8 and B8 are valid names for shapes on the active sheet.

What is the value in A8 when the error occurs? Are you certain that
there's a shape with that name?

When you say that the file runs fine in Office v.X, are you talking
about the same file (i.e., accessed over a network, or from a remote
drive)? Or has the file been modified on the machine running XL04?

I've posted a file that works fine in XLv.X and XL04 using your code
(and includes another macro that toggles the visibility of the two
shapes) at:

ftp://ftp.mcgimpsey.com/excel/trasto_demo.xls

 

[trasto]

JE McGimpsey ha escrito:

When you say that the file runs fine in Office v.X, are you talking
about the same file (i.e., accessed over a network, or from a remote
drive)? Or has the file been modified on the machine running XL04?

I've posted a file that works fine in XLv.X and XL04 using your code
(and includes another macro that toggles the visibility of the two
shapes) at:

ftp://ftp.mcgimpsey.com/excel/trasto_demo.xls

Thanks for the quick reply.

I'm not the expert on Excel or VBA so I can't answer those specific
questions right now. I'll be in touch with her brother sometime
tomorrow (it's late at night here and my wife is asleep). I'm acting as
a translator in this issue as both my wife and brother-in-law are
non-native English speakers.

However, I have downloaded your test file which runs on her Excel 2004
install without problems.

Her machine is a dual boot machine with OS X 10.2.8 on one partition
and OS X 10.4.7 on another. Her data files are on another (local)
partition which is where this particular Excel file resides. It's the
original file, unmodified being read from the same location. It works
fine when booted from 10.2.8 and using Office v.X but returns
runtime-errors when running from 10.4.7 using Office 2004.

I'll post back ASAP with answers to your other questions.

Thanls

 

[Jim Gordon]

Hi Trasto,

I see nothing inherently wrong with the code.

Excel modules sometimes get corrupt. Try copying the module code to some
other program (textedit, for example). Then delete the module. Then
make a new module and copy the text from the other program into the new
module.

Another way would be to paste the code into a Word document and save the
module as text only, then import the module back into Excel.

I'm a bit surprised that your VBA buddy didn't try this, but give it a
shot and see what happens.

-Jim Gordon
Mac MVP

 

[trasto]

Jim Gordon ha escrito:

Excel modules sometimes get corrupt. Try copying the module code to some
other program (textedit, for example). Then delete the module. Then
make a new module and copy the text from the other program into the new
module.

Thanks JM and Jim,

I'm afraid I'd need step by step instructions to create the module.
I've never used Excel in my life and my wife won't know either. Which
module(s) where are they etc?

As for JM's questions, I've spoken to my brother-in-law who says all
the logic of the file is OK (it runs fine on Office 2003 for Windows
and my wife can still run it fine under Jaguar/Office v.X. It seems
there is something in our version of Office 2004 that's not working
correctly which is in line with Jim's suggestion.

If anybody is willing, my wife has no objection to sending the file to
someone to see if it runs OK on their Office 2004. It's around 500KB in
size.

I think re-creating the module would be the best first step however,
but how exactly do I do this?

Many thanks

 

[trasto]

Some information that may be of interest.

I contacted my brother-in-law about the possible module corruption and
I have now successfully re-built all three of them. The underlying
problem persists however.

It's worth noting though, that the original Excel file was created in
the Spanish version of Excel in Office v.X. My brother-in-law thinks
this could be part of the problem. He says that on his Spanish Office
2003 for Windows, the visual basic items are NOT localised. He notes
that in the Spanish Excel for Mac they ARE localised. Our Office 2004
for Mac is my copy from England and is therefore in English.

So internal identifiers or labels are given localised names on our
Spanish Mac Excel v.X (this is where the original file was created).
For example, 'image_1' would automatically be 'imagen_1' in our
Spanish Mac Excel. The same thing happens for the modules, 'module_1'
would automatically be 'módulo_1' on our Spanish Mac Excel (note the
accent in there).

When my brother-in-law opens the file on the Windows Office 2003 Excel,
the accent on the module name is replaced by a dash but the file works
correctly in spite of the name change.

I find it hard to believe that Microsoft would allow this kind of
difference to break compatibilty so feel the problem lies elsewhere.

After rebuilding the modules on the English Mac Excel 2004 the names
are now Module 1, Module 2 and Module 3.

Regards.

 

[Bob Greenblatt]

Some information that may be of interest.

I contacted my brother-in-law about the possible module corruption and
I have now successfully re-built all three of them. The underlying
problem persists however.

It's worth noting though, that the original Excel file was created in
the Spanish version of Excel in Office v.X. My brother-in-law thinks
this could be part of the problem. He says that on his Spanish Office
2003 for Windows, the visual basic items are NOT localised. He notes
that in the Spanish Excel for Mac they ARE localised. Our Office 2004
for Mac is my copy from England and is therefore in English.

So internal identifiers or labels are given localised names on our
Spanish Mac Excel v.X (this is where the original file was created).
For example, 'image_1' would automatically be 'imagen_1' in our
Spanish Mac Excel. The same thing happens for the modules, 'module_1'
would automatically be 'módulo_1' on our Spanish Mac Excel (note the
accent in there).

When my brother-in-law opens the file on the Windows Office 2003 Excel,
the accent on the module name is replaced by a dash but the file works
correctly in spite of the name change.

I find it hard to believe that Microsoft would allow this kind of
difference to break compatibilty so feel the problem lies elsewhere.

After rebuilding the modules on the English Mac Excel 2004 the names
are now Module 1, Module 2 and Module 3.

Regards.

Make sure the proper type libraries are installed. The fact that the module
names changed indicate that you are using the English type library.
Additionally, row and column IDs change in localized versions. If these are
hard coded as literals ("r1c1" in English) they may not work properly in
nationalized versions.

 

[trasto]

Bob Greenblatt ha escrito:

Make sure the proper type libraries are installed. The fact that the module
names changed indicate that you are using the English type library.
Additionally, row and column IDs change in localized versions. If these are
hard coded as literals ("r1c1" in English) they may not work properly in
nationalized versions.

Thanks Bob, but what do I have to do to check the type libraries? I'll
look into the R/C IDs with my brother-in-law.

Regards