Create a VBA Macros | Create a VBA Module | Record a VBA Macro | Run a recorded VBA Macro | Customize the VBA Macro security settings |  Add a VBA Macro to the Ribbon |  Protect VBA Macro

This chapter will guide you how to create a VBA Macros in Microsoft Excel. How to create a VBA Module in Excel? How to record a VBA Macro? How to customize the VBA macro security settings? How to add a VBA Macro to the Ribbon bar? How to protect VBA Macro in Microsoft Excel 2013.

• Create a VBA Macros
• Create a VBA Module
• Record a VBA Macro
• Run a recorded VBA Macro
• Customize the VBA Macro security settings
• Add a VBA Macro to the Ribbon
• Protect VBA Macro

Create a VBA Macros

This section will describe that how to create a VBA Macros from the below steps:

1# display the “Developer” Tab in ribbon.

2# click “Insert” button under “Controls” group, then click “Button” from controls.

3# drag button on your worksheet, then “Assign Macro” window will appear.

4# input the Macro Name (mybutton) that you want to define, then click “New” button

5# The Visual Basic editor will appear. Add your code in the Code window. Then close the Visual Basic Editor.

6# you will see that a button will appear in your current worksheet.

7# click that button, the value of cell C5 will be set to “excelhow.net”.

Create a VBA Module

This section will teach you how to create a new VBA module in excel. And the VBA module is available to the whole Excel workbook. The code in worksheet is only available to the current worksheet.

1# Click “Insert” menu in the Visual Basic Editor and then click on “Module” from the drop down list.

2# create a sub procedure, enter the below codes into the code window:

Sub fontSize()

ActiveCell.Font.Size = 50

End Sub

This sub procedure will set the font size to 50 for the current Active Cell.

3# Click “Macros” button in Code group, choose “fontSize” Macro from “Macro” window, then click “Run” button.

4# you will see that the font size will set to 50 for the current active cell.

Record a VBA Macro

If you don’t know how to write a VBA program in VBA, then Record Macro may be is another good choice to achieve your goal, it will save each of the commands you perform into the Macro so that you can play them again.

1# Click on “Record Macro” button in “Code” group

2# enter into a Macro name, such as: “MyMacro1”, select “This Workbook” form the drop down list under “Store macro in:”, then click “OK”

Note: if you select “This Workbook”, it means that this macro is only available to the current workbook.

3# now try to perform some actions, such as: formatting the current active cell (set font color to green, set font style to Bold, set fond size to 48).

4# click on “stop Recording”

Run a recorded VBA Macro

The below steps will show you how to run a recorded VBA Macro.

1# select the range “A2:A4”

2# click “Macros” button in “Code” group, then select one Macro that you want to run, click “Run” button.

3# you will see that the active cells will be formatted.

Customize the VBA Macro security settings

VBA code can control or execute the external program, we maybe don’t know if this program is safe or not, it can be as virus to attack our system, so it’s very dangerous for users.

Four options of Macro security settings can be choose as below:

1 – Disable all macros without notification

2 – Disable all macros with notification

3 – Disable all macros except digitally signed macros

4 – Enable all macros

By default, we choose the second option, when Open a workbook with macros, this setting will prevent macros and pop-up a notification and also give you a choice if enable this macro.

1# Click on “Macro Security” in “Code” group under “DEVELOPER” Tab.

2# click “Macro Settings”, you will see that four option can be choose.

3#Record a macro in the current workbook

4# save workbook and choose a macro-enabled file type in the File Type list.

5# reopen the above macro-enabled workbook, you will a popup notification.

6# click “Enable Content” button to enable Macros or click “close” button to ignore this notification.

Use Relative Mode to Record Macro

By default, recording macro will use absolute mode, it means that the recorded macros is only available to the initial selected cells.

If you want to use Macro to others cells, then you need to record macro in relative mode. The below steps will guide you how to use relative mode to record macro.

1# enable “Use Relative References” setting in “Code” group under “DEVELOPER” Tab.

2# click any single cell (A1), then click “Record Macro” in “Code” group

3# right click on the cell, click “Format cell…”, set font size to 48, set font style to “Bold”.

4# click “Stop Recording”

5# select any other cell (A3), then run the above recorded macro.

6# let’s see that last result.

Add a Macro to the Toolbar

If you want to run a macro or recorded macro quickly, excel allow you add a macro to the Ribbon or quick access toolbar. Just following the below steps:

1# right click on the ribbon, then click “Customize the Ribbon”

2# select “Macros” under “choose commands from” drop-down list.

3# select one macro that you want to add, click “Add>>” button.

4# click “Ok”, macro should be add to Ribbon.

Macro Protection

You can protect a macro with password in excel.

1# open Visual Basic Editor, click “Tools”->”VBAProject Properties…”

2# click “Protection” tab in “VBAProject properties” window.

3# check “Lock project for viewing”, and enter password, then click “OK”

4# close the current workbook and then reopen it.

5# open Visual Basic Editor, then try to view project. A “VBAProject Password” dialog box will pop-up.