Excel Macros Examples (Table of Contents)
Introduction to Excel Macros
There are some tasks in Excel that we do on a daily basis. It could be our routine tasks which may include, writing some specific formulas, copying and pasting some data or updating some columns or writing a text several times at different locations in a sheet. To avoid this repetition of tasks and save time we can use Macros in Excel. Macros are the tool that automates the tasks and saves time and reduces errors. A Macro is a program that copies keystrokes or mouse actions that are repeated and are common in the sheet.
In short, Excel Macros refers to the programming language in VBA. Macros are short programs written in VBA. We can record/write macros in VBA and can run a macro by clicking the Macros command on the Developer tab on the ribbon. Macros are written to automate tasks and save time and assure efficiency.
Methods to Use Macros in Excel
Below we will discuss the different methods to use Macros in excel.
Method 1 – Enabling Macros in Excel
For adding the Developer tab in the ribbon, open an Excel workbook from the File menu.
Click on Options.
In a window opening up named Excel Options, click on Customize Ribbon to access the ribbon customization options.
Select the Developer (Custom) option and then press on OK.
You will see the Developer tab on the ribbon.
Method 2 – Creating Macros using Record Button
We can record a macro and use it any number of times. We can record a task that we use on a daily basis or which is repetitive. Once the Macro is recorded, we can just run it and our work will be done.
Let’s take the example of recording a Macro.
Example:
We will record the name of companies and their sales. To begin, click the View button on the tab, then move to the right-hand side and you will see the “Macros” tab as shown below.
Now, click on the Macros tab, and you will find Record Macro option.
Click on it, to record whatever you want to, be it writing something or copying and pasting something. In our example, we will create/record a table of companies and their sales.
Click on Record Macros, and you will see the below window pop up. Now we will name the macro as Macro11. Ensure, there should be no space and special characters in it. Then we will assign a shortcut key to run the macro, I assign CTRL+V. We will then store the macro in “This Workbook”. Click on OK and the macro will start recording.
I have recorded this table.
Now go to Macros and click on Stop Recording.
The macros “Macro11” is stored on the Macros button. Click it to see the Macro window where you can select “Macro11” and click Run.
If you want to paste the data in sheet 2, just go to sheet 2 and press Ctrl+V or view macros.
Go to the Developer tab and click on the Insert tab. Then from Active X Controls, select the Command Button.
Once you click on the Command Button you can then assign the macro as shown below. Then click on OK.
And drag the mouse to outline/create a button, rename it to “Data”. Now just click the button and the macro will work.
Method 3 – How to Save a File with Macro?
We can only save the files with macros as Excel Macro-Enabled Template (file type).
It can’t be saved as a normal Excel file.
Method 4 – Writing Macros in VBA
We cannot always record macros because they have a limited scope. Sometimes we have to write macros as per our requirements in VBA. To get into VBA, click on the Developer tab and on the left-hand side you will see the first option as “Visual Basic”. Click on it or press ALT+ F11 to go to VBA.
Examples of Excel Macros
Below we will discuss the examples of Excel Macros.
Example #1 – Write a Program to get Text Hello
- We will now write a short program in VBA. We will write a program to get text Hello “your name” in the message box every time we enter a name in an input box.
- We will open the sheet and then go to the developer’s tab in the ribbon and press Alt+F11 and will get into VBA. Once we go into this page, we will click on the Insert and click module.
We will write our first program in Module 1. We always start our program with “Sub” and end it with End Sub in VBA.
Sub hello_word () Dim name As String name= InputBox ("Input your name") MsgBox "Hello" + name End Sub
So now we write the program as below:
Explanation:
- “Dim name as string” defines the name as a string.
- Then there is an inbuilt Inputbox function that will ask a name to be filled in, which will be stored in a variable name.
- Msgbox+name, it will display the name in msgbox. We will then create a command button by going to the Developer tab, then Inset tab. Then form control, command button.
We will then assign the macro to the button and then Click on OK key.
When we click on Button 1 which we will see as below:
I entered my name and it displayed.
Every time you press button 1, you will see the input box, just enter the name and you will see the “hello+name”
You can also run the code from the VBA window by pressing F5 if you have not created the command button.
Example #2 – Writing a Shortcode using For Inside a Loop
In this example, we will work with For. Suppose we want to fill cell A1 to A10 with 1,2,3….till 10. We can do this by writing a shortcode using For inside a Loop.
Press F5 and you will run the code. You can also run the macro by going to View -> Macros -> select “Numbers” from the list and then click Run. You will see that cells from A1 to A10 are filled from 1 to 10.
Example #3 – Display Total No.of Odd and Even Numbers
Using If we can write a logical macro. We will write a code in which we can create a table and at last, there will be msgbox displaying total no. of odd and total no. of even numbers. So we will write a code:
- We will start the program with Sub oddeven(name).
- We will then take x as input box to input a number. Then in variable a, we will store a loop from 1 to 10.
- Now in cell A1 of sheet 3, we will make a table by multiplying it with x in a loop till A10.
- We will then give a condition that if cell A1 will be even then it will be added to a variable y which is currently 0 and odd will be added to a variable z which is also 0. Then we will end if statement and write next A for loop to work.
- At the end, we will add a msgbox to display the sum of odds and its total number and sum of even and its total number. End the program.
Run the program with F5. I entered 6 in the input box.
Click OK and you will see the below result.
Example #4 – Write a Program to Pop as per Defined Age
In this example, we will use the sentence case to give results as per some specific conditions. In this example, a message will pop up as per defined age group of people, once you select a cell mentioning age.
We will write the program as below:
The program will start as Sub Agegroup.
- Cell value will be defined as an integer and that will be the active cell value, which means that one which is selected.
- We will then use a select case in different cases as per the age group. The message box will then appear according to age.
- If the age selected is not found in the select cases, it will give the message as unknown age.
End Select
End program
There are some numbers mentioned and once I select 12 and run the code. It will show the result as below:
It will give the age group as shown below.
These were some examples of VBA macros.
Things to Remember
- Always save the Excel file containing macros as an Excel Macro enabled template as the file type. Else the macros will not be saved.
- The program name should not contain any spaces.
- It is good to write programs in modules as it is not sheet specific.
- Be careful while recording macros because if you make a mistake or repeat a step it will be recorded as same and will not give true output.
Recommended Articles
This is a guide to Examples of Excel Macros. Here we discuss the Introduction to Excel Macros and the various Methods to Use Macros in Excel. You can also go through our other suggested articles –
