Excel VBA Macros

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.

Create a VBA Macros

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

Create a VBA Macros

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

Create a VBA Macros

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

Create a VBA Macros

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

Create a VBA Macros

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

Create a VBA Macros

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.

Create a VBA Module

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

Create a VBA Module

Create a VBA Module

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

Create a VBA Module

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

Record a VBA Macro

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

Record a VBA Macro

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

Record a VBA Macro

Record a VBA Macro

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

Run a recorded VBA Macro1

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

Run a recorded VBA Macro1

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

Run a recorded VBA Macro1

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.

Macro security

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

Macro security

3#Record a macro in the current workbook

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

Macro security

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

Macro security

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.

Use Relative Mode to Record Macro1

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

Use Relative Mode to Record Macro1

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

Use Relative Mode to Record Macro1

Use Relative Mode to Record Macro1

4# click “Stop Recording

Use Relative Mode to Record Macro1

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

Use Relative Mode to Record Macro1

6# let’s see that last result.

Use Relative Mode to Record Macro1

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

Add a Macro to the Toolbar

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

Add a Macro to the Toolbar

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

Add a Macro to the Toolbar

Add a Macro to the Toolbar

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

Add a Macro to the Toolbar

Macro Protection

You can protect a macro with password in excel.

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

Macro Protection

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

Macro Protection

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

Macro Protection

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.

Macro Protection

Leave a Reply