ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

Visual Basic for Excel 2007 and Excel 2010 - A Beginner’s Guide

Updated on August 17, 2013

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

Example of a basic piece of Visual Basic code created in Excel 2007 and Excel 2010.
Example of a basic piece of Visual Basic code created in Excel 2007 and Excel 2010. | Source

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:

http://robbiecwilson.hubpages.com/hub/Guide-to-recording-Macros-in-Excel-2007

  • 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:

http://robbiecwilson.hubpages.com/hub/How-to-create-a-thematic-or-Choropleth-map-in-Excel-2007-and-Excel-2010

Thematic map created using Visual Basic code in Excel 2007 and Excel 2010.
Thematic map created using Visual Basic code in Excel 2007 and Excel 2010. | Source
  • 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:

http://robbiecwilson.hubpages.com/hub/User-Interface-design-using-a-UserForm-in-Excel-2007-and-Excel-2010

Hotel Reservation User Interface created using Visual Basic code in Excel 2007 and Excel 2010.
Hotel Reservation User Interface created using Visual Basic code in Excel 2007 and Excel 2010. | Source
Hotel Reservations created automatically by the Hotel Reservation User Interface (above) in Excel 2007 and Excel 2010.
Hotel Reservations created automatically by the Hotel Reservation User Interface (above) in Excel 2007 and Excel 2010. | Source

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

How to enable the Developer tab in Excel 2007.
How to enable the Developer tab in Excel 2007. | Source

For those using Excel 2010:

  • Select the File menu
  • Choose Options
  • Click the Customize Ribbon tab
  • Under Main Tabs tick Developer as shown below

Enabling the Developer tab in Excel 2010.
Enabling the Developer tab in Excel 2010. | Source

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

Microsoft Visual Basic modules in Excel 2007 and Excel 2010.
Microsoft Visual Basic modules in Excel 2007 and Excel 2010. | Source
  • 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

How to move between Visual Basic modules in Excel 2007 and Excel 2010.
How to move between Visual Basic modules in Excel 2007 and Excel 2010. | Source

Before we begin with writing the code itself; there is one more buttons you should be familiar with on this screen.

The Run button in a Visual Basic Module in Excel 2007 and Excel 2010.
The Run button in a Visual Basic Module in Excel 2007 and Excel 2010. | Source

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 Test_Macro()

  • 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.

Example of a line with an error in a Visual Basic Module in Excel 2007 and Excel 2010.
Example of a line with an error in a Visual Basic Module in Excel 2007 and Excel 2010. | Source
  • 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.

Excel 2007 and Excel 2010 showing an error exists in the Visual Basic code that was run.
Excel 2007 and Excel 2010 showing an error exists in the Visual Basic code that was run. | Source

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:

Using Microsoft Visual Basic Help to assist you  in writing Visual Basic code in Excel 2007 and Excel 2010.
Using Microsoft Visual Basic Help to assist you in writing Visual Basic code in Excel 2007 and Excel 2010. | Source

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:

Dim Number

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).

Worksheets("Visual Basic").Activate

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)


End If


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.

Shape that Visual Basic code will colour based on the contents of a cell linked to the shape in Excel 2007 and Excel 2010.
Shape that Visual Basic code will colour based on the contents of a cell linked to the shape in Excel 2007 and Excel 2010. | Source

.Fill.ForeColor.RGB=RGB(204,0,0)

Tells Excel what to do with my shape (change the Fore Colour to red)

We finish the IF statement with

End If

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!

Illustration of Visual Basic code colouring the shape based on the contents of cell A2 in Excel 2007 and Excel 2010.
Illustration of Visual Basic code colouring the shape based on the contents of cell A2 in Excel 2007 and Excel 2010. | Source

Conclusion

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!

Comments

    0 of 8192 characters used
    Post Comment

    • profile image
      Author

      Robbie C Wilson 3 years ago

      Hi Joy,

      Glad that you found my hub useful.

    • joym7 profile image

      Joy 3 years ago from United States

      Very helpful. Great hub

    • profile image
      Author

      Robbie C Wilson 3 years ago

      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.

    • epbooks profile image

      Elizabeth Parker 3 years ago from Las Vegas, NV

      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.

    Click to Rate This Article