Excel Macros Tutorial 1.3 | Sub, Buttons & Message Box

Sub, Buttons & Message Box

In this tutorial we will learn how to create processes with VBA (Visual BASIC for Applications).

The objective is to create a button that displays a message for the user.

To create our program go to the Developer tab and click the Visual Basic button 

You will probably see an empty gray window like this one:

In case you don’t have the Project Explorer or properties window open, go to menu bar and click “View”.

Then activate both windows:

Go to the Project Explorer and click on the “+” next to your file to expand. This will display all your sheets modules and one for the Workbook.

Now go to the second icon from left to right of the top left corner (the one with the rulers) and insert a new module:

A new white page will pop out.

Just for your reference, there are two ways to define routines in visual BASIC. The first one is with “Function” and the other one is with “Sub”. They work almost the same; the only difference is that the function is used to return a value to another function or process, while Subs can be used to update a cell or perform a calculation, but the result can’t be returned to another sub or function.

In this tutorial we will use a Sub routine.

Type down the following:

sub Test1

And click enter.

You will see that excel automatically adds “()” and closes the sub routine with an “End Sub”.

Between these statements we have to write all the code for our process.

The instruction to display a message box is “MsgBox” followed by a space and the message we want to display between double quotes. For example:

MsgBox “Hello World!”

In the end the code will look like this:

Save the file.

** Note :  save the file as a macro-Enabled workbook (*.xlsm)

Now we need to insert a Form Control Button from the Developer tab as shown below:

Click and drag anywhere on the sheet to place the button and a new window will appear

Just select the macro we just created and click the OK button.

Now click the button and you’ll see the message box that pops out with “Hello World!”

Excel Macros Tutorial 1.2 | Recording Macros

Recording Macros

When we perform repetitive tasks in a workbook, the best thing we can do is to record a macro so we can do our task much faster. In this tutorial, we will record a macro that applies conditional formatting to a table of student grades.

Imagine we have multiple sheets with student grades, and we want to mark in red all grades below 8.

We can go page by page formatting all cells, or we can do it just once and record a macro to do it faster.

1

The first step is to select the grades range:

2

Now on the developer tab we can find the option “Record Macro” on the upper left corner.

Click the button and a new window will pop out asking you to input the following information:

Please note that you can create a shortcut to activate the macro (in this case I’ll use Ctrl+d). Now click OK and you will see that the “Record Macro” icon changes to a blue square that says “Stop Recording”.

3

Now let’s go to our Home tab, and click Conditional Formatting->Highlight Cells Rules->Less Than

4

Select to format cells that are less than 8, with a light red fill and dark red text. Click the OK button.

5

Now go to the developer tab and click on the “Stop Recording” button.

6

Your macro will be saved automatically. Now go to another sheet, select the grades and press Ctrl+d .

It will set the same conditional formatting.

7

To view the code of your recorded macro, just go to the developer tab, and click the Macros icon at the top left corner. A window with all your saved macros will appear.

Select the macro we just created and click the Edit button.

The VBA code will appear on a new window, where you can modify any command directly from there.

Excel Macros Tutorial 1.0 | Course Index

Index - Course 1

1.1- Setup

1.2- Record macros / buttons

1.3- SUB – Process & Buttons & Msgbox

1.4- Variables

1.5- Workbook, Sheets, Range, Cells

1.6- IF Conditional

1.7- FOR & WHILE Loops

Excel Macros Tutorial 1.1 | Excel Setup - Developer Tab

Excel Setup – Activate Developer Tab (1.1)

In this free course, you will learn the basics of recording and programming excel macros, how to use buttons user forms, and other tools that can help us achieve repetitive tasks in a very optimized and user friendly way.

Note that I’m going to use Excel 2013 for these tutorials.

The first thing we have to do, is to setup excel so we can visualize the developer tab. This is achieved by clicking on the top left corner “File”

Then we’ll have to click on “Options”. A new window will pop out.

Finally we have to click (1)“customize Ribbon” and check the (2)“Developer” tab.

When we click the OK button, we should now be able to see the Developer tab in our excel file:

Now we are ready to start developing Excel Macros.