What is Programming in Excel?
Programming refers to writing a set of instructions that tell Excel to perform one or more tasks. These instructions are written in the Visual Basic for Applications (VBA) as this is the language understandable to Excel. To write an instruction, one can either write a code in VBA or record a macro in Excel. A macro is recorded to automate repetitive tasks. When a macro is recorded, VBA generates a code in the background.
For example, to submit an Excel report, a table needs to undergo the same set of tasks each time. These tasks include applying a pre-defined border, color, alignment, and font. To program in Excel, a macro can be recorded for performing all these tasks.
The purpose of programming in Excel is to save the user from performing the same tasks again and again. Moreover, it helps accomplish multiple tasks at a great speed that would have taken a lot of time, had they been performed manually.
Table of contents
- What is Programming in Excel?
- Stages of Programming in Excel VBA
- Frequently Asked Questions
- Recommended Articles
Stages of Programming in Excel VBA
Programming in Excel VBA is carried out in the following stages:
- Enable the Developer tab in Excel
- Record a macro in Excel
- View and examine the VBA code generated by the recorded macro
- Test the VBA code of the recorded macro
- Save the recorded macro (or the VBA code)
Further in this article, every stage of programming ine xcel has been discussed one by one. The steps to be performed in each stage are also listed.
Stage 1–Enable the Developer Tab in Excel
Let us understand how to enable the Developer tabDEVELOPER 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. in Excel. This tab is enabled to record a macro and access VBA. When the Developer tab is enabled, it appears on the Excel ribbonRibbon 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.. However, by default, Excel does not display the Developer tab.
Note: To know an alternate method to record a macro and access VBA, refer to the “alternative to step 1” in stages 2 and 3.
The steps to enable the Developer tab in Excel are listed as follows:
Step 2: Select “options” shown in the following image.
Step 3: The “Excel options” window opens, as shown in the following image. Select “customize ribbon” displayed on the left side of this window.
Step 4: Under “choose commands from” (on the left side of the window), ensure that “popular commands” is selected. Under “customize the ribbon” (on the right side of the window), choose “main tabs” from the drop-down list.
Next, select the checkbox of “developer” and click “Ok.” This checkbox is shown in the following image.
Step 5: The Developer tab appears on the Excel ribbon, as shown in the following image.
Stage 2–Record a Macro in Excel
Let us learn how to record a macroRecording Macros in ExcelRecording macros is a method whereby excel stores the tasks performed by the user. Every time a macro is run, these exact actions are performed automatically. Macros are created in either the View tab (under the “macros” drop-down) or the Developer tab of Excel. in Excel. When a macro is recorded, all tasks performed by the user (within the workbook) are stored in the Macro Recorder until the “stop recording” option is clicked. Apart from Microsoft Excel, a macro can be recorded for all Office applications that support VBA.
The steps to record a macro in Excel are listed as follows:
Step 1: From the Developer tab, click “record macro” from the “code” group.
Alternative to step 1: One can also click “record macro” from the “macros” group of the View tab of Excel.
Step 2: The “record macro” window opens, as shown in the following image. In this window, one can name the macro before recording it. The default macro name given by Excel is “macro1.”
The rules governing a macro name are stated as follows:
- It should not contain any space ( ), period (.) or exclamation point (!).
- It cannot contain any special characters (like $, @, ^, #, *, &) except the underscore (_).
- It should not begin with a numerical value. Rather, it must start with a letter.
- It cannot exceed 255 characters in length.
Note: It is recommended to use short and meaningful names for macros. Further, one must assign a unique name to each macro. In VBA, two macros within the same code module cannot have the same names.
Step 3: In the “macro name” box, we have entered the name “recording_macro.”
Notice that an underscore has been used as a separator between the two strings (recording and macro) of the name. No spaces, periods or special characters have been used in this name.
Step 4: Click “Ok” to start recording the macro. Once “Ok” is clicked in the preceding window, the “record macro” button (in the Developer tab or the View tab) changes to the “stop recording” button.
Next, carry out the tasks to be recorded in the macro “recording_macro.”
Note: Since the Macro Recorder records every action performed by the user, ensure that the actions are executed in the right sequence. If the recorded sequence is correct, Excel will perform the tasks efficiently each time the macro is run.
However, if a sequencing error occurs, one must either re-record the actions or edit the VBA codeVBA CodeVBA 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. manually. If one is recording the macro for the first time, it is recommended to keep a copy of the workbook to prevent any undesired changes to the stored data.
In the following steps (step 4a to step 4c), the tasks to be recorded have been performed.
Step 4a: Select cell A1 of the worksheet. This is the first task that is recorded. The selection is shown in the following image.
Step 4b: Type “welcome to VBA” in cell A1. This is the second task that is recorded. Exclude the beginning and ending double quotation marks while typing.
Step 4c: Press the “Enter” key. As a result, the selection shifts from cell A1 to cell A2. This becomes the third task that is recorded.
The selection is shown in the following image.
Step 5: Click “stop recording” in the “code” group of the Developer tab. By clicking this option, Excel is told to refrain from recording any further tasks.
The “stop recording” option is shown in the following image.
Stage 3–View and Examine the VBA Code Generated by the Recorded Macro
Let us observe and study the VBA code generated by the macro recorded in stage 2. Remember that one can either directly write a code in the Visual Basic Editor (VBE or VB Editor) or let a macro do the same.
The VBE is a tool or program where VBA codes are written, edited, and maintained. When a macro is recorded, a code is automatically written in a new module of VBE. Note that VBA is the programming language of Excel.
The steps to view and study the code generated by the recorded macro are listed as follows:
Step 1: Click “visual basic” from the “code” group of the Developer tab. This option is shown in the following image.
Alternative to step 1: As a substitute for the preceding step, press the keys “Alt+F11” together. This is the shortcutShortcuts in ExcelAn Excel shortcut is a technique of performing a manual task in a quicker way. to open the VBE window.
Step 2: The Visual Basic Editor window opens, as shown in the following image.
To the left of the VBE window, the worksheet, workbook, and module are shown. This window on the left (named “Project-VBAProject”) is also known as the Project window or the Project Explorer of VBE.
Step 3: Double-click “modules” shown in the following image.
Note: “Modules” are folders shown in the Project window after recording a macro. They are not shown prior to recording a macro. “Modules” are also shown when a module is inserted manually from the Insert tab of the VBE.
Step 4: Double-click “module1” under modules. A code appears on the right side of the VBE window. This window on the right [named “Book1-Module1 (Code)”] is known as the module code window.
The code is displayed in the following image.
Note: The code generated by recording a macro can be checked in the “modules” folder (in the module code window). In a module code window, one can also write a code manually or copy-paste it from another source.
In the following steps (step 4a to step 4d), the code generated by the recorded macro has been studied.
Step 4a: The first word of the code is “Sub.” “Sub” stands for subroutine or procedure. At the start of the code, the word “Sub,” the macro name (recording_macro), and a pair of empty parentheses are displayed. This is followed by the statements to be executed by the code. These statements are:
ActiveCell.FormulaR1C1 = “Welcome to VBA”
Range (“A2”). Select
The code ends with “End Sub.”
The start or head [Sub Recording_Macro ()] and the end or tail [End Sub] of the code are shown in the following image.
Note 1: The words “macro” and “code” are often used interchangeably by several Excel users. However, some users also distinguish between these two words.
A VBA code is a command created either by writing a code directly in VBE or by recording a macro in Excel. In contrast, a macro consists of instructions that automate tasks in Excel. According to one’s choice, one can decide whether or not to differentiate between the two words.
Note 2: The “Sub” can be preceded by the words “Private,” “Public,” “Friend,” or “Static.” All these words set the scope of the subroutine. The default subroutine used in VBA is “Public Sub.” So, when “Sub” is written in a code, it implies “Public Sub.”
A “Public Sub” can be initiated by subroutines of different modules. However, a “Private Sub” cannot be initiated by subroutines of other modules.
Step 4b: The first activity we performed (in step 4a of stage 2) was to select cell A1. Accordingly, the following statement of the code tells Excel that the active cell is R1C1.
When a macro is recorded, VBA uses the R1C1 style for referring to cells. In this style, the letter R is followed by the row number and the letter C is followed by the column number. So, cell R1C1 implies that the row number is 1 and the column number is also 1. In other words, cell R1C1 is the same as cell A1 of Excel.
Step 4c: The second activity we performed (in step 4b of stage 2) was to type “welcome to VBA” in cell A1. So, the following statement of the code tells Excel that the value in cell R1C1 is “welcome to VBA.”
ActiveCell.FormulaR1C1 = “Welcome to VBA”
Step 4d: The third activity we performed (in step 4c of stage 2) was to press the “Enter” key. By pressing this key, the selection had shifted from cell A1 to cell A2. Therefore, the following statement tells Excel to select cell A2.
Range (“A2”). Select
This is the way VBA generates a code for all the activities performed under stage 2 of programming in Excel. Examining the code line-by-line makes it easier to interpret it.
Stage 4–Test the VBA Code of the Recorded Macro
Let us test the code when it is run multiple times. Note that a macro (or code) can be run as many times as one wants. Each time it runs, it performs the recorded tasks in Excel.
The steps to test the code that we examined in stage 3 are listed as follows:
Step 1: Delete the string “welcome to VBA” from cell A1 of Excel. Let A1 remain as a blank, selected cell. The following image shows the empty cell A1.
Note: To go back from VBE to Excel, press the toggle key “Alt+F11.”
Step 2: Go to VBE again by pressing the key “Alt+F11.” Click anywhere within the code. Next, click the “Run Sub/UserForm (F5)” button. This button is shown within a blue box in the following image.
Note: Alternatively, one can press the key F5 to run the VBA code.
Step 3: The output is shown in the following image. The preceding code enters the string “welcome to VBA” in cell A1. Thereafter, the selection shifts to cell A2. The string “welcome to VBA” has been entered in cell A1 because this cell was selected (in step 1) before running the code.
Each time the code is run, the currently selected cell (or the active cell) is filled with the string “welcome to VBA.” Then, the selection shifts to cell A2. So, if cell M10 is the active cell, running the code fills this cell with the stated string and selects cell A2 at the end.
However, had cell A2 been selected, running the code would have filled this cell with the string “welcome to VBA.” Moreover, in the end, cell A2 would have remained the selected cell.
Stage 5–Save the Recorded Macro (or the VBA Code)
Let us learn how to save a workbook containing a recorded macro. If a macro is saved, its VBA code is also saved.
The steps to save a workbook containing a macro (or a VBA code) are listed as follows:
- Click “save as” from the File tab of Excel. The “save as” dialog box opens, as shown in the following image.
- Assign a name to the Excel workbook in the “file name” box. We have entered the name “macro class.”
- Save the workbook with the “.xlsm” extension. So, in the “save as type” box, choose “Excel macro-enabled workbook.”
- Click “save” to save the workbook.
A workbook containing a macro should always be saved with the “.xlsm” extension. This extension ensures that the macro is saved and can be reused the next time the workbook is opened.
Note 1: The “save as” command is used when a workbook is saved for the first time. It is also used when a new copy of the workbook is to be created and, at the same time, the original copy is to be retained as well.
Note 2: If the workbook containing a macro is saved as a regular workbook (with the “.xlsx” extension), the macro will not be saved. Further, one may lose the code of the recorded macro.
Frequently Asked Questions
Programming refers to instructing Excel to perform one or more tasks. To instruct Excel, either a code can be written in the Visual Basic for Applications (VBA) or a macro can be recorded in Excel. Each time a macro is recorded, a code is generated by VBA in the background.
The steps to carry out programming in Excel are listed as follows:
a. Enable the Developer tab in Excel.
b. Record a macro in Excel. For recording, perform each activity in the sequence in which it should be recorded.
c. Save the code generated by the recorded macro and run it whenever required.
One can also carry out programming by writing a code manually and then saving and running it.
Note: To learn the details of programming in Excel, refer to the description of the different stages given in this article.
For programming in Excel, a code is written in Visual Basic Editor (VBE), which is a tool of VBA. The steps to write a code in VBE are listed as follows:
a. Open a blank Excel workbook.
b. Press the keys “Alt+F11” to open VBE.
c. Select any worksheet from “Microsoft Excel Objects” listed in the “Project-VBA Project window.”
d. Click the Insert tab and choose “module.” A folder named “modules” and an object named “module1” are created in the “Project-VBA Project window.” At the same time, a window opens on the right, which is titled “Book1-Module1 (Code).”
e. Enter the code in the “Book1-Module1 (Code)” window that has opened in the preceding step.
f. Click anywhere within the code once it has been written entirely.
g. Run the code by pressing F5 or clicking the “Run Sub/UserForm (F5)” button.
If the code has been entered correctly in step “e,” Excel will perform the tasks it has been instructed to. However, if there is an error in the code, an error message will appear.
Note: For saving a code, refer to stage 5 of programming in Excel given in this article.
To learn programming, one can learn how to record a macro in Excel. It is easier to learn macro recording than to create a code manually in VBE. Moreover, macro recording can be done even if one does not know VBA coding.
However, each time a macro is recorded, examine the generated code carefully. As one becomes proficient in macro recording, the codes too will become understandable. In this way, learning programming in Excel will no longer be a complicated task.
This has been a guide to Programming in Excel. Here we discuss how to record VBA macros along with practical examples and downloadable Excel templates. You can learn more from the following articles–
- Create Button Macro in ExcelCreate Button Macro In ExcelA Macro is nothing but a line of code to instruct the excel to do a specific task. Once the code is assigned to a button control through VBE you can execute the same task any time in the workbook. By just clicking on the button we can execute hundreds of line, it also automates the complicated Report.
- What is VBA Macros?
- Excel MacroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks.
- Code in Excel VBACode In Excel VBAVBA 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.