Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Create a Macro
2.1.
Developer Tab
2.2.
Command Button
2.3.
Assign a Macro
3.
Run Code from a Module
4.
Swap Values
5.
Macro Recorder
6.
FormulaR1C1
7.
Use Relative References
7.1.
Recording in Absolute Mode
8.
Add a Macro to the Toolbar
9.
Enable Macros
10.
Protect Macro
11.
Frequently Asked Questions
11.1.
What do you mean by a macro?
11.2.
How can we open a Visual Basic Editor in Excel?
11.3.
How macros are helpful in a spreadsheet? Explain.
12.
Conclusion
Last Updated: Mar 27, 2024
Easy

Introduction to Macro

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

An Excel VBA macro is an action (or a set of actions) that you can give a name, record, save and run as many times as you want and whenever you want. Macros will help you save time on repetitive tasks involved in data and data information that must be done frequently.

 

Let's see how to create a macro in excel, which will be executed after clicking on a command button. 

 

Create a Macro

 

Developer Tab

Execute the following steps to turn on the Developer tab.

 

1. Right-click anywhere on the ribbon, and then click Customize the Ribbon.

 

 

Customize the Ribbon in Excel

 

2. Under Customize the Ribbon, on the right side of the dialog box, select "Main tabs."

3. Then check the "Developer check box."

 

Turn on the Developer Tab

4. Next step is to click the OK button.

5. You can find the "Developer tab" next to the View tab.

 

Developer Tab

Command Button

Execute the following steps if you want to place a "command button" on your spreadsheet/worksheet.

 

1. Go to the Developer tab, then click Insert.

2. In the "ActiveX Controls group," click the "Command Button."

 

Insert a command button control

3. Next is to drag a command button on your worksheet, as shown in the picture below.

 

Assign a Macro

 

Execute the following steps to assign a macro to the command button.

1. Right-click CommandButton1 (make sure Design Mode is selected).

2. Click View Code.

The Visual Basic Editor will highlight it.

3. Next step is to place your cursor between "Private Sub CommandButton1_Click()" and "End Sub."

4. Add the code line shown below in the picture.

 

Add Code Line

5. Next, close the Visual Basic Editor(VBE).

6. The last and final step is to click the command button on the sheet.

The result of the above steps is:

 

Excel Macro Result

Congratulations. You've just created a macro in Excel!

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Run Code from a Module

 

The example given below teaches you how to run code from a module.

1. Open the Visual Basic Editor.

2. Click Insert, Module.

3. Create a procedure (macro) called Cyan.

Sub Cyan()

End Sub

4. The "Sub" will change the background color of your worksheet to Cyan. To achieve this, add the following code line.

Cells.Interior.ColorIndex = 28

5. Click Macros.

6. Select Cyan and click Run.

Result:

 

Different Background Color

Swap Values

This example teaches you how to swap two values in Excel VBA.

 

Two values on your worksheet.

Swap Values in Excel VBA

Place a command button on your worksheet and add the following code lines:

 

  • Dim temp As Double
  • temp = Range("A1").Value
  • Range("A1").Value = Range("B1").Value
  • Range("B1").Value = temp

Click the command button two times.

Result:

Swap Values Result

Swap Values Result

Macro Recorder

The Macro Recorder is a very useful tool in Excel that records every task you perform with Excel VBA. All you have to do is record a specific task once, which you won't repeatedly do. Next, you can execute the task repeatedly with the click of a button. The Macro Recorder is also a great help when you don't know how to program a specific task in an Excel sheet and generate a macro. Simply open the Visual Basic Editor(VBE) after recording the task to see how it can be programmed instead of doing it again manually.

 

Pic by WallStreetmojo.com

 

FormulaR1C1

 

This example below illustrates the difference between A1, R1C1, and R[1]C[1] styles in Excel VBA.

 

1. Place a "command button" on your spreadsheet/worksheet and add the following code Range("D4").Formula = "=B3*10" in line (A1 style):

 

Result:

A1 Style

2. Add the following code Range("D4").FormulaR1C1 = "=R3C2*10" in line (R1C1 style):

R1C1 Style

Explanation: Cell D4 references cell B3, i.e., row 3 and column 2. This is an absolute reference ($ symbol in front of the row number and column letter).

3. Now, add the following code Range("D4").FormulaR1C1 = "=R[-1]C[-2]*10" in  line (R[1]C[1] style):

 

Result:

R[1]C[1] Style

 

4. Why are we learning about this? 

As the Macro Recorder uses the FormulaR1C1 property. The "Macro Recorder" button creates the following code lines if you enter the formula (=B3*10) into cell D4.

Excel Macro Recorder Result

Use Relative References

 

By default, Excel can record macros in absolute mode. It will place the recorded steps exactly in the cells where it was recorded. However, sometimes it is useful to record macros in relative mode. The program below will teach you how to record a macro in absolute mode; let's see how to do this.

Recording in Absolute Mode

Execute the following steps to record a macro in absolute mode.

 

1. Click "Record Macro."

2. Next is to select cell B3. Type Sales and then press enter.

3. Type Production and then press enter.

4. Type Logistics and then press enter.

The result of the above steps is shown below:

 

Recording in Absolute Mode

5. Next is to click "Stop Recording."

6. Empty the Range("B3:B5").

7. Select any cell on the sheet in Excel and run the recorded macro.

 

Recording in Absolute Mode Result

A macro recorded in absolute mode will always produce the same result.

 

Add a Macro to the Toolbar

 

It is used if you access a macro frequently, so you can add it to the Quick Access Toolbar. This is the way you can quickly access your macro anytime. The first thing is that we record an empty macro.

1. Then, on the Developer tab, click "Record Macro."

2. Then name the macro like MyName. Choose the location of storing the macro in the Personal Macro Workbook. The macro created will be available in all your files.

 

Store Macro in Personal Macro Workbook

3. Click OK.

4. Click Stop Recording.

Click Stop Recording

5. Open the Visual Basic Editor.

6. Create the macro using the same code and procedure mentioned above.

 

This macro places your name(mentioned) in the Active Cell.

7. Then, close the Visual Basic Editor(VBE).

8. Now, we can add this created macro to the "Quick Access Toolbar." Now, click the down arrow and click "More Commands," as shown in the picture below.

 

Customize Quick Access Toolbar

9. Under Choose commands, select Macros.

10. Select the macro and click Add.

11. You can modify the button added to the Quick Access Toolbar by clicking Modify. For example, choose a smiley.

 

Modify Button

12. Click the OK button twice.

13. Now, you can now execute the created macro. For example, select cell E2 and click on the smiley button; it will be added to the "Quick Access Toolbar."

14. When you close the Excel, it will ask you to save the changes you made to the "Personal Macro Workbook." Click the Save button to store this created macro in a hidden workbook that will open automatically when Excel starts.

 

Save Changes to the Personal Macro Workbook

Enable Macros

 You have to change your macro security settings in the "Trust Center" to enable macros in the Excel sheet when the message bar appears. The following steps are:

 

1. Now, when the message bar appears, click "Enable Content" to enable macros.

2. How will you change your macro security settings? To change your macro security settings, on the Developer tab, click "Macro Security."

Then the Trust Center opens.

1. The first option disables all macros.

2. The second option in the dialog box will ask you to enable a macro. If you download many Excel files from the internet, always use this security level. Don't click Enable Content (see the first screenshot on this page) if you don't trust the owner of the Excel file.

3. The third option in the dialog box only allows macros with a digital signature to run and asks you to enable others.

4. The fourth option in the dialog box will enable all macros. Always use this security level if you are a beginner; with this security level, you don't have to enable macros all the time and only type your macros at the moment. 

 

Enable All Macros

 

Protect Macro

 Nowadays, protection is a must to do a thing for every important stuff. Like this, you can protect worksheets and workbooks using a password and protect a macro in Excel from being executed.

For this, first of all, place a command button on your spreadsheet and add the following code lines written below:

1. Create a macro that you want to password-protect.

 

Code: Range("A1").Value = "This is secret code"

2. Next is to click Tools, VBAProject Properties.

3. Now, on the Protection tab, check "Lock project for viewing" and enter a password twice, just like you do while creating your accounts for safety.

4. Then click OK.

5. The next step is to save, close, and reopen the Excel file or worksheet. 

The following dialog box will appear:

 

Password Protected from being Viewed

You can still execute the code by clicking on the command button but you cannot view or edit the code anymore (unless you know the password). The password for the downloadable Excel file is "easy".

6. If you want to password-protect the macro from being viewed, add the following code lines given below:

 

Dim password As Variant
password = Application.InputBox("Enter Password", "Password Protected")

Select Case password
    Case Is = False
        'do nothing
    Case Is = "easy"
        Range("A1").Value = "This is secret code"
    Case Else
        MsgBox "Incorrect Password"
End Select

 

Password Protected from being Executed

 

Explanation of this code: If the user clicks Cancel, this method returns False, and nothing happens as macro uses the InputBox method. The secret code will be executed only when the user knows the password ("easy" again). If the entered password is shown as incorrect, a MsgBox is displayed. Note that the user cannot look at the password in the Visual Basic Editor because the project is protected from being viewed.

 

Note that the user cannot take a look at the password in the Visual Basic Editor because the project is protected from being viewed.

 

Frequently Asked Questions

 

What do you mean by a macro?

A macro is a set of actions or set of instructions that you can run as many times as you want. After you've created a macro, Excel will execute those instructions step by step.

How can we open a Visual Basic Editor in Excel?

The easiest way to open the Visual Basic editor is to use the keyboard shortcut – ALT + F11. It will open a separate window for the Visual Basic editor as soon as it is done.

How macros are helpful in a spreadsheet? Explain.

Macros help us save time on the repetitive tasks involved in manipulating the data that are required to be done frequently.

 

Conclusion

This blog has mainly covered the different functions we can apply on a macro in an excel sheet. For example, how to create a macro, How we can swap the two values, How to add a macro to a toolbar, How to record a task using a macro record, How to protect a macro and many more.

 

If you want to know about Excel before learning the details, check out this article.

 

Happy Learning!!!

 

 

Previous article
Introduction to Excel VBA
Next article
MsgBox and InputBox in VBA
Live masterclass