VBA - Why Not Record Macro Every Time

By now we know that we can record actions you perform in Excel by using built-in macro recorder. If that is the case, why do we need to write code by ourselves? Well, there are few problems with code recorded by macro recorder and few advantages with writing the code yourself, let’s see few of them.

Lengthy Code

We saw in one our previous posts that code recorded by Excel will be very lengthy because it tries to give values to many default items. We saw how a 40+ line recorded code for simple action can be rewritten in 4+ lines. Lengthy code not only makes your project looks clumsy, it also reduces the execution speed.

Cannot Record All Actions

Yes. Macro recorder cannot record all actions you perform. Don’t believe me? Then do the below steps and see it for yourself. (I gave example of Excel 2007/2010 only)

• Click on Insert > SmartArt > select any diagram and click OK.
• Now start recording a macro (you can see here on how to record)
• Select any box in inserted smart art and change the format (you may try to change colours of the boxes by clicking on SmartArt Tools > Change Colors)
• Open the VBE and observe the code recorded. Can you see any code for change of formatting? I can see a simple one line code ActiveSheet.Shapes.Range(Array("Diagram 1")).Select

Looping

You cannot record a code that works in a loop. Think of a situation where in you have to create a new workbook for each sheet of your current file and save each of such workbook with the sheet name. You can record your macro to replicate saving one sheet and run the same macro on each sheet manually. That means if you have 10 sheets, you will have to run that macro 10 times. You cannot ask the macro to run for 10 times automatically. Whereas in VBE, you can write a line of code that makes the macro run to work on any number of sheets.

Variables

You can’t define variables and assign values to them using macro recorder.

Can’t Display Custom Messages

If you want to show an alert message to user with two buttons viz Proceed, Cancel; you simply can’t do this with macro recorder.

Create Custom Function

I have no clue on how to create a new function that works how I want using VBA recorder.

Well, you got an idea. There are many things you can do with VBE code that is not possible with recorder. But recorder is still a better place to start with for your code. That’s a start, having established that writing a code yourself has many advantages, we will see more about code and related stuff in future posts. Stay tuned!!