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
VBA programmingVBA ProgrammingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. is accessed by enabling the DEVELOPER tab in excelDEVELOPER Tab In ExcelEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.. By default, it is not enabled in MS Excel; we need to enable it. Below is the screenshot of the same.
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 ExcelRibbon In ExcelRibbons in Excel 2016 are designed to help you easily locate the command you want to use. Ribbons are organized into logical groups called Tabs, each of which has its own set of functions..
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 the enter key to stop typing, so I hit the 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 the “Visual Basic” or shortcut key, we can see below the window.
Double Click on Modules.
Now all the recordings 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 the 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 the 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 an “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 –