Learn to code in VBA in Excel 2007 and Excel 2010
Hi, and welcome to my latest hub on Excel. Today, I will look at how to create code using Visual Basic or Visual Basic for Applications (also known as VB or VBA). Macros in Excel are written in Visual Basic and it is through running macros that you will use your VB code. You can assign a macro to objects such as Combo boxes or shapes and they can also be run using short cut keys.
Broadly speaking there are two reasons to create macros in Excel:
- To automate repetitive tasks such as formatting data
- To add functionality to Excel spreadsheets e.g. linking a cell to a shape and changing the colour of that shape based on the value in the linked cell
There are two ways of creating Visual Basic code in Excel:
- The first is to record a macro.
When you perform a task while Excel is recording a macro, for example, turning grid-lines on and off. You can then see the Visual Basic code that Excel uses to preform that task. For a thorough guide on recording macros, click here:
- The second method is to write the code from scratch in a Visual Basic module within Excel
This method is the focus of this hub. In today’s hub, we will write code that will recolour a shape when it is clicked.
I have used Visual Basic code to complete two projects in Excel.
- The first is a thematic map (a chart that shows themes or variations across a geographical area, such as rainfall across the United States)
This uses shapes that change colour based on the contents of the reference data. Visual Basic code was used to link the reference data to the individual shapes that constitute the overall map. If you would like to learn how to create a thematic map in Excel, I have a hub that goes into creating one in further detail which can be found here:
- The second is a Hotel Reservation User Interface
This utilises Text boxes, Combo buttons, a Command button, Spin buttons, and Option or Radio buttons to create a User Interface that, using Visual Basic code, takes the output from it and copies it to the spreadsheet. To discover more about the design of the Hotel Reservation UserForm and the Visual Basic code behind it:
Enabling the Developer tab in Excel 2007 and Excel 2010
Before we begin to create Visual Basic code and macros, we need to enable the Developer tab. The methods are different depending on what version you are running.
If you are using Excel 2007:
- Navigate to the Excel button
- Choose Excel Options
- Under Popular, check Show Developer tab in the Ribbon
For those using Excel 2010:
- Select the File menu
- Choose Options
- Click the Customize Ribbon tab
- Under Main Tabs tick Developer as shown below
Working in the Microsoft Visual Basic window in Excel 2007 and Excel 2010
When working with Visual Basic code, you will be working within modules as shown below. There are two ways of accessing Visual Basic modules:
- Select the Developer tab and click the View Code button in the Controls group
- Click on the Macros button in the Code group on the Developer tab and select a macro you want to work on and select Edit
- To create a new module, click on Insert within the Microsoft Visual Basic window and select Module
- This will open a new (blank) module
- To move between modules (macros), double click on the a module in the Modules section
Before we begin with writing the code itself; there is one more buttons you should be familiar with on this screen.
The button highlighted above with the red arrow is the Run button. This allows you to test your code to ensure that there are no errors. Excel will tell you if there is an error in your code and helpfully which line it is on.
Basics of writing Visual Basic code in Excel 2007 and Excel 2010
The first step for all macros is to tell Excel the name of your Macro and that your code is starting. The syntax for this is:
- Sub is the start of the subroutine (program) and Test_Macro is the name of our macro
- Excel automatically adds End Sub to a subsequent line to complete the macro
Now that we understand how to name and start our code, we need to look at how Excel lets us know if there are errors in your code.
You can see from the figure below that the second line in my code is coloured red.
- The red lettering is telling us that there is something missing from that line
If when you run it by clicking the Run button, there is still an error in your code, Excel will give you an an error and then highlight the line containing the error in yellow. You can then check that line for the error and fix it.
Use the Microsoft Visual Basic Help to assist you with building your Visual Basic code in Excel 2007 and Excel 2010
The help available when you are working in Microsoft Visual Basic is very good indeed. While researching this hub, I learnt how to define the active worksheet and also how to change the colour of my shape directly from the help.
I had to change the code to suit my needs, but I learnt the basics from the help itself.
I found out that I needed to define it because without a defined active worksheet, Excel did not know which A2 I was referring to. So I searched in the help for “Active Sheet” and found the following:
Likewise, to find the code I needed to fill my shape, I searched for “shape fill” and used that code for the basis of the IF statements I created
Writing a macro using Visual Basic in Excel 2007 and Excel 2010
Now that we understand the basics, it is time to write the code for our macro. The first step is to define a variable (we will call it Number) to hold the contents of our cell we would like to link to our shape:
Now we need to tell which Excel which worksheet we want it to work off (I have a large number of worksheets, if you only have one you can skip this line).
Visual basic is the name of the worksheet I am working off and we have asked Excel to use it as the active worksheet.
Now we tell Excel what to put into our variable Number.
Number = Cells(2, "A").Value
This tells Excel that Number equals the contents of cell A2 and that it is a value
Now we come to the heart of our code, telling Excel what to do with the value held in number.
If Number >= 50 Then
ActiveSheet.Shapes("Freeform 1").Fill.ForeColor.RGB = RGB(204, 0, 0)
If Number >= 100 Then
ActiveSheet.Shapes("Freeform 1").Fill.ForeColor.RGB = RGB(0, 255, 0)
What this does is:
If the variable Number (which is linked to cell A2) is greater than 50 then do what is on the next line, if not do nothing and go to the next IF statement.
The ActiveSheet.Shapes(“Freeform1”) part of the next line is the name of my shape. To find out what your shape is called click on it and note down its name which will appear in the box next to the formula box.
Tells Excel what to do with my shape (change the Fore Colour to red)
We finish the IF statement with
The next IF statement does virtually the same thing, except that we are going to change the colour of the shape only if the number in A2 is above 100.
Assigning a Macro to shapes or objects in Excel 2007 and Excel 2010
The final step is to assign our new macro to the shape itself. The reason for doing this is to allow you to run the macro by simply clicking on the shape itself. To do this:
- Select your shape or object
- Choose Assign Macro
- In the list of Macros, choose the macro you want to assign to the shape and click OK
Now that we have completed our code and assigned it to our shape we need to test it. To test:
- First enter 20 into cell A2 and click your shape to confirm that nothing happens
- Next, enter 50 into A2 and click the shape again. It should now be filled red
- Finally, enter 110 into A2 and click the shape one last time. It should now change to a bright green!
Visual Basic allows you much more flexibility to do things in Excel that are beyond the tools that are hard coded into it. Your Visual Basic code is available via macros which can run via short cut keys or assigned to a shape or object. In today’s hub, we have covered the basics of coding in VB:
- First, we looked at the enabling the Developer tab and how to access Microsoft Visual Basic from within Excel
- Then, we examined the basics of writing Visual Basic code as well as how Excel lets us know if we have errors in our code
- Next, I showed how to use the excellent help available within Microsoft Visual Basic in Excel to help you build your code
- After that, we went through line by line building code to change the colour of my shape depending on the contents of cell A2
- Finally, we looked at how to associate macros with shapes or objects on your worksheet!
I wish you success in writing your own code in Visual Basic to fulfil any needs you may have within your worksheets. Please feel free to leave any comments you may have below. Many thanks for reading!
Robbie C Wilson (author) on September 14, 2013:
Glad that you found my hub useful.
Joy from United States on September 03, 2013:
Very helpful. Great hub
Robbie C Wilson (author) on August 24, 2013:
Hi EP Books,
Thanks for your comment. Visual Basic is great for adding functionality to your workbooks. I am so glad that you found it useful.
Elizabeth Parker from Las Vegas, NV on August 21, 2013:
I've never used VB in Excel, but I'm familiar with using it on its own or in MS Access (although it's been years). It's a great tool to have knowledge of and so useful for automating commands. Bookmarking your hub for future use! Voted up.