Create a Macro Function in MS Excel

In this tutorial, you will learn how to Create a Macro Function in an MS Excel document. Macros are disabled by default due to security reasons. You must save the Excel Workbook with macros and the Macro-enables Save option.

An Excel Macro is a set of instructions that automates repetitive tasks in Microsoft Excel. A macro is like a recording of your actions in Excel — clicking cells, typing, formatting, etc. Once recorded, you can replay those actions any time with a single click or shortcut.

Create a Macro Function

You can create macros in two ways:

• Using the Macro Recorder (no coding needed)
• Using VBA Editor (for advanced users)

Using Macro Recorder

Open Start Menu and click the Excel application icon.

 

Open an Excel Document. Click the Blank workbook option.

This will open a new blank Excel workbook.

 

 

Click on the Developer tab. ->> Macros button in the Developer ribbon menu.

In case, if the Developer tab is not present in the menu. Follow the steps outlined here:

• https://www.testingdocs.com/customize-the-ribbon-in-ms-excel/

We can find the Macros button in the Developer ribbon.

 

 

Enter the Macro name. Click on the Create button to create the macro.

Choose the scope of the Macro:

• All Open Workbooks
• This Workbook

We will create a macro function to print “Hello World”. The macro name is MyFirstMacro()

Add the following simple macro code:

 

Function MyFirstMacro()
MyFirstMacro = "Hello World"
End Function

 

Invoke the Macro

In the Excel sheet, Choose a cell to call the macro function. Start typing the function name.

 

 

Excel would prompt with the function. Select it.

=MyFirstMacro()

 

 

That’s it. A successful macro would print the Hello World message in the excel sheet cell.