Record Macro vs VBA

[whoopee]

Hi guys,

I am an accounting student, and for one of my subjects I have to write a report on the Record Macro and VBA methods of creating macros in excel.

I have no prior knowledge of macros and I was wondering if any of you would be so kind as to help me.

Basically, I need to know how the VBA language and the Record Macro facility in Excel can be used to create macros, and to identify the advantages and disadvantages of both methods.

Using a few things I've found on the internet, I am able to make a working example of a macro using both methods. However, being an inexperienced macro creator, I am only able to come up with a few advantages and disadvantages.

I was wondering if any of you guys, (whom are more experienced than I am ) can suggest some advantages and disadvanatages of using either method. And also, which method do you personally find better to use, and why.

Thanks in advance.

 

[Norie]

One disadvantage of recording macros is that every action is recorded.

 

[Von Pookie]

One disadvantage of recording macros is that every action is recorded.

For the most part, yes, but this is not always the case. There are some things that don't seem to generate code in the macro recorder. Off the top of my head I have no clue what they are, but run across one every so often.

 

[Norie]

I was referring to things like scrolling really.

 

[Von Pookie]

I figured as much--I was just throwing out the fact that there are some things that may not generate code if tried

 

[just_jon]

The main advantage of the macro recorder is that you really don't have to know anything much about code to use it. Record the step you wish to automate, and there you have it.

The biggest disadvantage is that the recorder is verbose. This is what copying A1 to B1 looks like from the recorder:

Sub Macro1()
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
End Sub

This is what you need/want:

Sub Macro1()
Range("A1").Copy Range("B1")
End Sub

The recorded version is rarely efficient code.

 

[shajueasow]

Hi,
Another thing that I had came across is that, a macro recorded in one version may not really run on earlier versions. For ex. A macro to find all occurances of a word in a WORKBOOK can be recorded in Excel 2002, but the same cannot be run on Excel 2000 as such (without tweaking). But such a thing can be done with VB knowledge.
All keystrokes cannot be recorded by recording a macro. One ex. get the NEW tab activated in the DATA FORM . But the same can be done via VBA method.