Programming in Excel

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.read more 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.read more. 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.

Programming in Excel Example 1

Step 2: Under FILE, go to OPTIONS.

Example 1.1

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.read more.

Programming in Excel Example 1.2.0

Step 4: Check the box DEVELOPER to enable it. Click on OK.

 Example 1.3.0

Step 5: You will now be able to see the Developer tab.

Programming in Excel Example 1.4

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.

You can download this Programming Excel Template here – Programming Excel Template

Follow the below steps to learn how to record macros in excel.

Step 1: Under the Developer tab, hit the “Record Macro” option.

how to record macros in excel Example 1.5

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.

how to record macros in excel Example 1.7

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.

 how to record macros in excel Example 1.8

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.

how to record macros in excel Example 1.9

After the selection of the cell, I will type “Welcome to VBA” in that cell. This is the second activity in excel.

Example 1.10.0

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.

Example 1.11

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.

Programming in Excel Example 1.12.0

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”.

 Example 1.13

Shortcut excel key for hit “Visual Basic.”

Programming in Excel Example 1.14

After pressing the “Visual Basic” or shortcut key, we can see below the window.

how to record macros in excel 1.15.0

Double Click on Modules.

Programming in Excel Example 1.16

Now all the recordings we have done so far will appear on the right-hand side.

 how to record macros in excel Example 1.17

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.

how to record macros in excel Example 1.18

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”
Note: R1C1 is row 1, column 1.

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.

how to record macros in excel Example 1.19

“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.

Programming in Excel Example 1.20.1

Note: The shortcut key to run the code is F5.
Example 1.21.0

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.”

 Example 1.23.0

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.

Recommended Articles

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>