Re: Learning VBA

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 wrote:


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:

Active***.Shapes(Range("A8").Value).Visible = True

Let me put that into a bit more context:

Sub Hang_calcular_lletra()
Active***.Calculate
Active***.Shapes(Range("A8").Value).Visible = True
Active***.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
'Active***' 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.

.