Are you from non-technical background and want to learn VBA or Macro to automate your excel task? If yes, then you are at the right place.
This article will teach you the VBA in the most laymen langauge.
Please note, this article is for non-technical language. If you’re from a technical or engineering background than you may not find this article useful.
The intention behind writing this article is to teach VBA or Macro to the common employees doing corporate job.
But, before stating the article, I request you to please subscribe to my blog (either through email newsletter or social networking site) to stay updated and get instant notification when I publish the other chapters of this topic.
So, what are we waiting for? Let’s put on your seat belt on and sit back to learn the VBA in simple language
Difference Between VBA And Macro
Yes, you read it correctly. VBA is different from Macro.
Macro is a piece of code or script written to perform any particular and monotonous tasks automatically in the excel. In most of the cases, the Macros are the recorded scripts.
Whereas, the VBA refers to programming language that MS excel used to write the macros. Hence, VBA is a wider term than Macro.
How To Record A Macro In Excel?
Before moving forward, for a non-technical person it is very important to learn about the Recorded Macro.
So, let’s have a brief introduction on ‘Recorded Macro’ first.
As name suggest, it is a macro recorded using Macro Recorder (a piece of software that records users actions and play it latter)
To record a macro all you need to do is click on ‘Record Macro’ button from the ‘Developer’ tab and perform sets of activities to record it.
Next, click on ‘Stop Recording’ button when you finished with recording.
Note: If in case, the developer tab is not available in your excel than you can enable the same, through ‘Customize Ribbon’ option ( File>>Option>>Customize Ribbon>>Enable Developer).
Yes, that’s it. Your macro is now ready for your performed tasks.
Let us understand this with a easy example.
Suppose, at every month end, you work on a report where you need to delete the first row (say ‘A’) every time.
Now, you can automate this task using ‘Recorded Macro’.
All you need to do is –
- Click on ‘Record Macro’ button
- Delete Row ‘A’
- Click on ‘Stop Recording’ button.
That’s it. Now, every time, when you run this macro, it will automatically delete the row ‘A’ from the opened file.
I know, you’re wondering – If recording macro is so easy than Why people are not using it? This is because, the ‘Recorded Macro’ has its own sets of limitation as mentioned below.
- It only records the set of activities performed within the excel. If you perform any task outside excel than it will not get recorded in it.
- The recorded macro is very rigid in nature. It does not get change as per your file requirement. For example, a macro recorded on 1000 rows will not work on file with greater than 1000 rows. It will work only if your file is static every time.
- Recorded Macro may have more number of words and unnecessary actions. This will make your code much slower and less efficient. For example, while recording the macro, you by mistakenly deleted the ‘Row A’. Next, you immediately press the Ctrl+Z and undo the action. Though, the net impact of these activities is zero, this will even get recorded in the macro.
- You can’t create advance macro in recorded macro. For example, you can’t create the dialog box or input boxes, using recorded macro.
In short, the macro recorder is like a camera recording your set of activities (including errors and mistakes) performed within excel.
So, a question may arise? How to overcome these problems? The answer to this question is ‘Visual Basic Application (VBA).
You can overcome this problem by writing your own custom code using VBA programming language.
What is Visual Basic Application (VBA)
As mentioned earlier, VBA is a programming language used to write a macro.
Now, to write a VBA code, all you need to do is press Alt+F11 or click on ‘Macros’ button from ‘Developer’ Tab.
This will open the VBA window as shown in below image.
Next, go to Insert and click on ‘Module’, to insert a module within the VBA window. Or else, you can also use the already available ‘Module 1’ from the menu appearing on the left side of the window.
Now, write the VBA code in the module and thereby save the file with the macro enabled option on your computer.
That’s it. Your file is ready to run the macro.
To Sum Up
If I sum up the above article in few words than the below are few things that we have learned from it.
- VBA and Macro are different from each other
- The term Macro refers to piece of code or script for performing particular task in MS excel
- Whereas VBA refers to programming language to write the macro code.
- If you have the standard report than ‘Recorded Macro’ will work best.
- But, if in case you don’t have the standard report than you need to use VBA to write the custom code.
At last, feel free to contact us or else leave a message in below comment box if you don’t understand any part of the above article.