Your first program

Lesson contents

Time for your first program.

The goal

You'll make a worksheet with one button on it, like this:

Button on worksheet

When the user clicks the button, a message will show:

Output

OK, that's what we want to happen. Now let's do it.

Big picture

Start up Excel, and make sure you add the Developer tab.

You'll have an empty worksheet.

We want to add a button that runs some code. There are three steps:

  • Make a place for the code to live.
  • Add the button.
  • Tie the button to the code.

Let's do the first one.

Make a place for the code to live

Most code lives in Subs. A sub is a chunk of code with a name. Here's a sub.

Code's home

This sub is called goatCode.

Now, we want some code to run when someone clicks a button:

Button on worksheet

So we could tie the button to goatCode. When the button is clicked, goatCode runs.

That's fine, except that goatCode isn't a great name. It's better to name subs so that you can tell how they're triggered. This would be a better name:

Better name

Let's make a sub called runClicked. You do that in VBE, the Visual Basic Editor.

There are a few ways to start VBE. The quickest way is to use the keyboard shortcut. On Windows is Alt+F11. There's a similar shortcut on the Mac.

Another way is to use the Developer tab, and click View Code:

View code

Start VBE now.

VBE opens, and looks something like this:

VBE

The window on the left is called the Project Explorer. Best to keep that open. If it's not there, or you accidentally close it, you can open it with the View menu.

Opening the Project Explorer

Notice the shortcut, Ctrl+R. Shortcuts are good.

The Project Explorer shows where you can put your subs. There are three common places:

Where code can go

Let's put all of our subs inside the sheet.

Where to put code

Double-click on Sheet1, and you'll see an editor for subs for Sheet1.

Sheet1

Here's what the editor will look like when there's some code in it.

Code

Ray
Ray

What happens if I accidentally put code in one of the other places?

No problem. Cut-and-paste the code into the Sheet1 place. Make sure you get the whole thing, from the Sub to the End Sub.

Cutting code

VBE starts

The line Option Explicit should be there, because you set the Require Variable Declaration option earlier:

Require Variable Declaration option

If you forgot to set the option, do it now with Tools | Options (select Options from the Tools menu). If you still don't see Option Explicit, type it in.

Important! Option Explicit should be the first line in every program. It helps avoid bugs. We'll talk about why later.

Now type this:

Sub code

OK, now your code has a home.

Now let's set things up so that when a button is clicked, the code in runClicked runs.

Add a button

Go to the Developer tab. Click Insert, and choose Button from the form controls.

Add button

Then click in the worksheet where you want the button to appear. I clicked in cell A2, but you can click somewhere else. You can move the button later, if you want.

You'll get a dialog window like this:

Assign macro

You want runClicked to run when the user clicks the button. Click on runClicked, and then click OK:

Choose your sub

Now you've linked the button to your code. So when someone clicks on the button, runClicked will run.

Adela
Adela

I notice it says Sheet1.runClicked.

Right! Good noticing.

Sheet1.runClicked

That's because an Excel workbook can have more than one worksheet. We'll usually have just one, called Sheet1.

After you click OK, you'll see the button on your worksheet. Something like this:

Button

Button caption

We want the word Run on the button, so let's fix that.

Right-click on the button, and select Edit text:

Edit button text

This will let you set the button's caption. Change it to Run.

Changing button text

The small circles show that you are editing the button. When you're done, click off the button, somewhere in the worksheet.

Click off the button

To move a button, right-click on it, and then press Escape to hide the menu. Move the mouse around until you see a move arrow.

Move arrow

Now drag the button where you want it.

Back to the code

Go back to VBE, and add the code that runs when the button is clicked:

Code

Try it. Click the Run button you made on the worksheet, and Hi there! should pop up.

Dialog

This is a modal dialog. Modal means that Excel is stuck, until you click OK.

For me, the mouse cursor changed into a blue ring.

Cursor

That's the I'm-waiting-for-something-to-happen cursor. Until you click OK, Excel won't do anything.

Try using the Excel menu. No dice. Try clicking in a cell in the worksheet. Nothing. You need to click OK before anything else will happen.

Showing the message on the worksheet

Suppose you wanted the message to show in cell C2 of the worksheet. Easy! Here's some code:

Setting a cell's value

This says to put the text "Hi there!" in the cell at row 2, column 3. Give it a try.

Save as XLSM

Excel workbooks with VBA in them need to be saved in a special way. When you save, choose Excel Macro-Enabled Workbook:

Save as Excel Macro-Enabled Workbook

Notice the extension: xlsm.

If you save a workbook as a regular Excel file (extension: xlsx), you could lose your code. Argh! Don't do that.

Opening Excel files containing VBA

When you open an XLSM file, you'll get warnings:

Warning

Warning options

You get to decide whether you trust the file. If it's code you wrote, it should be safe.

Time for another exercise.

Exercise

Your messages

Write a program that shows a message in a dialog box, and another on a worksheet. Your worksheet should look like this to start:

Start

The button should have the text Run on it. When the button is clicked, two things happen.

  • A message shows to the right of the button.
  • A different message shows in a dialog box.

For example:

Output

Hints:

  • You know how to show a message in a cell. You know how to show a message in a dialog. How do you put the two together?
  • Put the code that shows the message in the cell first, before the dialog code.

The messages must be different, but can be whatever you like.

Upload your worksheet.