Excel 2007-Macros

[SueW]

Do I understand correctly that you need to know how to use VBA to use macros
in Excel? I am an educator and have an end user who wants to record macros,
but neither of us knows VBA. The end user cannot give me an examples of what
she wants to record as a macro, but would like to know the process. I don't
want to show her how if at some time in the future we need to use VBA.

 

[Martin Brown]

Do I understand correctly that you need to know how to use VBA to use macros
in Excel? I am an educator and have an end user who wants to record macros,
but neither of us knows VBA. The end user cannot give me an examples of what
she wants to record as a macro, but would like to know the process. I don't
want to show her how if at some time in the future we need to use VBA.

XL2007 macro recording is hopelessly broken. Use XL2003 and remain sane.

Regards,
Martin Brown

 

[ker_01]

Sue-

Macros are recorded in the programming language VBA (Visual Basic for
Applications). Knowing VBA can certainly help a user extend the functionality
of their macros, but in-depth knowledge of VBA is not generally required to
record and use simple macros.

However, in my experience, a very basic knowledge of the Excel object model
in VBA is helpful. When Excel records a macro, it records exactly what you do
in Excel, but does not always record enough 'context' information to make
every recorded macro work "right out of the box". So there may be situations
where your recorded macro doesn't work the first time you try to run it. If
this occurs, if you simply post your macro code to this newsgroup with a
clear explanation about what you were expecting it to do, and what it is
actually doing, then helpful contributors in this group will modify your
macro so it works properly, and usually with enough information that you'll
start learning a little VBA along the way

Best,
Keith

 

[SueW]

Thank you. I will pursue my education with the end user.

 

[Bob Umlas]

You understand correctly.

 

[Bob Umlas]

It's hopelessly broken only if you installed the beta of Excel 2010!
Otherwise, it works just fine.
Uninstall 2010, reinstall 2007 AND 2010, macros should be ok

 

[JLatham]

What do we call the pieces that don't work now, and haven't since the initial
release, such as macros recorded while working with charts? For example,
after creating a simple line chart with data markers and returning to it to
change the color of those markers in one series, this is all you get:

Sub Macro2()
'
' Macro2 Macro
'

'
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(3).Select
Range("D11").Select
End Sub

Which will not repeat the process later when run as a macro.

 

[JLatham]

Sue, I concur with ker_01's assessment. Macros, even in 2007, can be useful
for many tasks to be repeated exactly using the same sheet and cells and
processes used during the recording.
Because they do record everything you do during the recording session, they
often contain a lot of not-required-to-actually-get-the-job-done code, but
that just makes them a bit inefficient later, not inoperable.
I disagree that the macro recorder in 2007 is "hopelessly" broken (unless
you did install the 2010 Beta), but there are certain operations that it does
not record properly - most notably while working with charts/graphs and other
drawing objects.

 

[Martin Brown]

I'm an expert in VBA and often use the macro recorder to get me started
on a macro. Especially with commands I don't use often to get the
correct syntax. But I always modified the reocrded macro to remove the
Selection and Active properties.

The macro recorder tends to produce verbose code with excessive
selections and activations of objects that reflect what was done via
mouse or keyboard. Prior to XL2007 the resulting code was never very far
off being a valid executable but with a lot of dross.

The problem with some macro instructions is the recorder
activates/selects objects but doesn't correctly put the
activation/selection of an object into the macro.

XL2007 is a different kettle of fish. Apart from the banal and trivial
select a cell and grab contents it almost never produces valid code
leaving many important steps out and sometimes generating no code at
all. Beginners stand almost no chance of automating simple graph tasks.

You don't need to know VBA to use the recorder, but to get a macro that
is easily understandable and easy to upgrade you should always modifiy
and document the macro so you remember in the future what the macro
actual does. At least changge the name of the macro form Macro1 to
something that you will remember in the future.

The problem with XL2007 is that the macro recorder no longer works
correctly. That isn't such a big deal for an experienced programmer, but
it is a total menace for the average office user just wanting to speed
up some frequently done operation with a captured macro.

I stand by my assertion that in XL2007 the macro recorder is hopelessly
broken. YMMV

Regards,
Martin Brown

 

[JLatham]

joel,
After some 10 or 12 years of programming Excel VBA I consider myself
Knowledgable, not necessarily an expert. The code I put up was simply a
macro that was recorded in Excel 2007 while going through the process of
changing the color of the data point markers on one series of a line chart.
As you can see from the code recorded: it did not record anything about the
change of color!!

Like you, I often use the recorder as you do - as a learning tool or to
refresh my memory. In this instance, and many like it in XL 2007, the
recorder is not only useless, but frustrating in its incomplete recording.

And yes, I would typically either change the name of the macro if I modified
it, or delete it entirely once I had gleaned the needed information from it.
This is just one of several reasons I remain with XL 2003 as my primary tool
and have not moved to 2007 for anything except assisting others that have
made the move when I can.