Start by opening up Excel. Depending on the version you have, you need to enable the Developer tools. I use Excel 2007, so the following instructions are for the 2007 version only. Click the Office button in the to left corner of the window and select "Excel Options". The "Popular" tab will automaticallybe highlighted. The third checkbox down from the top, labelled "Show Developer tab in the ribbon", should be checked (see screenshot below) - if it isn't make sure it is and then click OK.
Now you should have the "Developer" tab visible next to the other tabs (e.g. "Home", "Insert", etc.) Click on the Developer tab. In later tutorials I'll go through these controls in more detail, but for now we'll just take a quick look. The two sections of this tabe that will interest us the most are the "Code" and "Controls" sections. The controls allow us to add various, err, controls, to the workbook with which the user can interact. The Properties and View Code buttons allow us to modify those controls to look and behave the way we want.
The "Code" section contains a few different ways of viewing and editing the code in the macros. It also contains a button labelled "Macro Security", which we're going to click now. This brings up a box displaying macro settings. By default macros will be disabled and so you will not be able to run any. Select either "Disable all macros with notification" or "Enable all macros". The former option will ask for permission to run macros whenever you try to do so, whereas the latter will allow all macros to run. While enabling all macros could potentially be dangerous, if you aren't running macros from other sources there's no danger. Click OK.
We're now ready to start making our first macro!
Hello, World!
This macro will display a message box and a message when the user clicks a button.
The first step is to create the button. Under the "Developer" tab, click on "Insert" and choose a Button (the top left control, represented by a grey rectangle). Your mouse will change to crosshairs which you use to draw and position the new button. Click and hold anywhere in your worksheet and drag the mouse to create a button of the desired size. When you release the mouse button, a new window will show up, asking you to assign a macro to the button. For now just click Cancel.
The default caption of the button is "Button 1" - to change this, right click on the button and select "Edit Text". Delete the name and replace it with "Hello, World!", then click outside of the button. If you right click on the button again and select Format Control, further display options allow you to customize the button to look the way you want. Play around with them if you want to, then proceed to the next step.
Click on the button again (this time left click to just select the button without entering the Edit Text mode), then click on "View Code" in the Controls section of the Developer tab. This creates a new module (Module 1) and sub routine, Button1_Click(). In later tutorials I will explain more about sub routines and the VBA editing screen, but for now lets go ahead and code.
Between the two automatically generated lines "Sub Button1_Click()" and "End Sub", type the following code:
MsgBox "Hello, World!", vbOKOnly, "Hello, World!"
Your VBA editor window should now look like this:
Close the VBA editor window, save the worksheet somewhere, and then click on the "Hello, World!" button. If everything behaves as it's supposed to, you should now be looking at a message box saying "Hello, World!" - congratulations, you've programmed your first Excel macro!
The "Code" section contains a few different ways of viewing and editing the code in the macros. It also contains a button labelled "Macro Security", which we're going to click now. This brings up a box displaying macro settings. By default macros will be disabled and so you will not be able to run any. Select either "Disable all macros with notification" or "Enable all macros". The former option will ask for permission to run macros whenever you try to do so, whereas the latter will allow all macros to run. While enabling all macros could potentially be dangerous, if you aren't running macros from other sources there's no danger. Click OK.
We're now ready to start making our first macro!
Hello, World!
This macro will display a message box and a message when the user clicks a button.
The first step is to create the button. Under the "Developer" tab, click on "Insert" and choose a Button (the top left control, represented by a grey rectangle). Your mouse will change to crosshairs which you use to draw and position the new button. Click and hold anywhere in your worksheet and drag the mouse to create a button of the desired size. When you release the mouse button, a new window will show up, asking you to assign a macro to the button. For now just click Cancel.
The default caption of the button is "Button 1" - to change this, right click on the button and select "Edit Text". Delete the name and replace it with "Hello, World!", then click outside of the button. If you right click on the button again and select Format Control, further display options allow you to customize the button to look the way you want. Play around with them if you want to, then proceed to the next step.
Click on the button again (this time left click to just select the button without entering the Edit Text mode), then click on "View Code" in the Controls section of the Developer tab. This creates a new module (Module 1) and sub routine, Button1_Click(). In later tutorials I will explain more about sub routines and the VBA editing screen, but for now lets go ahead and code.
Between the two automatically generated lines "Sub Button1_Click()" and "End Sub", type the following code:
MsgBox "Hello, World!", vbOKOnly, "Hello, World!"
Your VBA editor window should now look like this:
Close the VBA editor window, save the worksheet somewhere, and then click on the "Hello, World!" button. If everything behaves as it's supposed to, you should now be looking at a message box saying "Hello, World!" - congratulations, you've programmed your first Excel macro!
Hello There. I found your blog using msn. This is an extremely well written article. I will be sure to bookmark it and return to read more of your useful information. Thanks for the post. I’ll certainly comeback.
ReplyDeleteexcel vba courses london