How to Program in Excel?
VBA (Visual Basic for Applications) is a Microsoft Excel programming language. It is the platform we use in excel to program things or tasks of our routine work. We have a set of words to describe our needs and we need to learn those words to write the programming in excel.
VBA is the platform that we use to write the program and the line that we write as code is called the macro. Macro is just a piece of code that instructs the excel about what to do.
Enable Developer Tab First
If the “Developer” tab is not visible to you then follow the below steps to enable it in your system.
Step 1: Go to FILE.
Step 2: Under FILE go to OPTIONS.
Step 3: You will see all the “Excel Options” window. Select Customize Ribbon in Excel.
Step 4: Check the box DEVELOPER to enable it. Click on OK.
Step 5: You will now be able to see the Developer tab.
How to Record Macros in Excel?
The best way to start programming in excel is by recording the macro.
This is just like our tape recorder or video recorder. It records all the activities we do in the spreadsheet until we hit the stop recording option. Ok, let us start our recording process now.
Follow the below steps to learn how to record macros in excel.
Step 1: Under the Developer tab hit the “Record Macro” option.
Step 2: Name the macro
Once hit on Record Macro, we will see below the window which asks us to give a name to macro. While naming the macro we need to follow certain rules and those conditions which are as below.
- The macro name should not contain space characters.
- The macro name should not start with a numerical value.
- The macro name should not contain any special characters except underscore.
Step 3: Name the Macro Window
We need to give a proper name to the macro window. In the below window I had given a name as “Recording_Macro”. As we can see this doesn’t have any special character or space character.
Step 4: Click on OK to start the recording.
Step 5: Perform activities which are to be recorded in a macro.
The moment we press OK it starts recording the things we do in excel.
The first activity I’ll do in excel is I will select the cell A1. This is the first activity in this worksheet.
After the selection of the cell, I will type “Welcome to VBA” in that cell. This is the second activity in excel.
Once I finish typing, I need to hit enter key to stop typing, so I hit enter key. The moment I hit enter key selection will jump from cell A1 to A2. This is the third activity in excel.
Step 6: Click on Stop Recording under the Developer tab to stop macro recording.
After these three activities, I will hit the option stop recording under the developer tab.
The moment we hit “Stop Recording”, it stops recording the activities we do in excel.
Now we need to go through what it has recorded to understand how exactly programming works with excel. To see this code under the Developer tab hit the “Visual Basic” option or else you can press the shortcut key “ALT + F11”.
Shortcut excel key for hit “Visual Basic”
After pressing “Visual Basic” or shortcut key we can see below window.
Double Click on Modules.
Now all the recording we have done so far will appear on the right-hand side.
As we can see the first word is “SUB”. All the macro names start with either “Sub”, “Function”, “Private” and “Public”.
Every macro contains two parts with it, one is “Head” and the second one is “Tail”. Head is the name of the macro and tail is the end of the macro.
Between this head and tail of the macro, excel VBA has recorded all the activities we have done. Now let’s look at the code line by line.
Just remember what our first activity was once we start recording the macro i.e. we have selected the cell A1, so for that in VBA Programming code is Range (“A1”). Select
After selecting cell A11 we have entered the test as “Welcome to VBA”, so in VBA Programming code is
ActiveCell.FormulaR1C1 = “Welcome to VBA”
The final activity was we hit enter key to come out of cell A1, so it has jumped from cell A1 to A2 and the code is
Range (“A2”). Select
This is how “record macro” has recorded all the activities from the moment we started recording until we hit stop recording.
Now, this code can be executed at any time it repeats the same activity again and again. Now delete the word.
“Welcome to VBA” in cell A1.
Once you have deleted the word, go to Visual Basic. Place a cursor inside the code and click on the RUN button. Now once again we get the text “Welcome to VBA” in cell A1.
Like this using VBA coding, we can automate our daily routine work to save a lot of time and get rid of boring tasks every day.
Special Save Option for Macro Workbook
All macro code contained workbooks should be saved with special versions. While saving the workbook we need to select the “save as type” as “Excel Macro-Enabled Workbook”.
Things to Remember
- You need to record more activities to get the content of code and understand.
- Once you are familiar with macro coding then you can take up our “VBA Course” to start learning.
This has been a guide to programming in excel. Here we discuss how to record VBA macro programs along with practical examples and downloadable excel templates. You can learn more from the following articles –