Time for your first program.
You'll make a worksheet with one button on it, like this:
When the user clicks the button, a message will show:
OK, that's what we want to happen. Now let's do it.
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.
This sub is called
Now, we want some code to run when someone clicks a button:
So we could tie the button to
goatCode. When the button is clicked,
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:
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:
Start VBE now.
VBE opens, and looks something like this:
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.
Notice the shortcut, Ctrl+R. Shortcuts are good.
The Project Explorer shows where you can put your subs. There are three common places:
Let's put all of our subs inside the sheet.
Double-click on Sheet1, and you'll see an editor for subs for Sheet1.
Here's what the editor will look like when there's some code in it.
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
Option Explicit should be there, because you set the Require Variable Declaration option earlier:
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.
Option Explicit should be the first line in every program. It helps avoid bugs. We'll talk about why later.
Now type this:
OK, now your code has a home.
Now let's set things up so that when a button is clicked, the code in
Add a button
Go to the Developer tab. Click Insert, and choose Button from the form controls.
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:
runClicked to run when the user clicks the button. Click on
runClicked, and then click OK:
Now you've linked the button to your code. So when someone clicks on the button,
runClicked will run.
I notice it says
Right! Good noticing.
That's because an Excel workbook can have more than one worksheet. We'll usually have just one, called
After you click OK, you'll see the button on your worksheet. Something like this:
We want the word Run on the button, so let's fix that.
Right-click on the button, and select Edit text:
This will let you set the button's caption. Change it to Run.
The small circles show that you are editing the button. When you're done, click off the button, somewhere in the worksheet.
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.
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:
Try it. Click the Run button you made on the worksheet, and
Hi there! should pop up.
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.
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:
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:
Notice the extension:
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:
You get to decide whether you trust the file. If it's code you wrote, it should be safe.
Time for another exercise.
Write a program that shows a message in a dialog box, and another on a worksheet. Your worksheet should look like this to 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.
- 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.