Chapter 2 of Automating Excel

The first question that any VBA beginner needs answering is - how do I access the data stored in the spreadsheet. The answer is easy, but it introduces ideas of objects and their properties and methods. This is where the real programming starts.

Automating Excel

• Chapter 1 - Getting started with Excel VBA
• Chapter 2 - Getting at the Data

Chapter Two

Getting at the Data

When you discover how easy VBA is to use you really want to rush on ahead and start doing something with it.

The trouble is that there seems to be a mystery barrier between VBA and the application that you are working with - Excel in this case.

It seems easy enough to add a module to a workbook (see the first part of this series - Getting started with Excel VBA ) and enter lines of VBA code that do something but how do you get at the data that is stored in a worksheet?

In normal spreadsheet use getting at the data is the most trivial of operations - you just point at it or you write a formula that references it.

For example,

=A1+A2 

entered into a cell tells the spreadsheet to add the data in cell A1 to the data in cell A2.

Most VBA users initially think that this sort of formula must be the way to get at worksheet data within a VBA program - it isn’t.

When they find out how it is done the usual reaction is to complain about how silly it is not to use the spreadsheet way of working. The reason for this apparent attempt at making things more difficult is that VBA is not a programming language designed for spreadsheets.

In principle you can use VBA with any type of application and the technique for getting at the data within that application remains the same. So we sacrifice simplicity for generality - but it isn’t that difficult, honest!

Step One - A single cell the hard way!

It is a good idea to start off by looking at the way that you can refer to the data stored in an individual cell. Be warned that this is the full unexpurgated version, there are simpler ways to write this.

For example, if you want to get the value stored in cell A1 you might write

temp= Application.Workbooks("Book1"). _
             Worksheets("Sheet1"). _
                    Range("A1").Value

Well I told you it was long! 

However if you think of it as a sort of “address” that works by moving from the more general to the more specific you should be able to follow what is going on.

Each part of the “address” is separated from the rest by a dot and you read it from left to right as usual. The Application part just means the Excel program itself.

A running copy of Excel can have a number of Workbooks open and so Workbooks(“Book1”) picks out the one called “Book1”.

Each Workbook is made up of a number of sheets and Worksheets(“Sheet1”) picks out the one called “Sheet1”.

Finally Range(“A1”) picks out the cell within the sheet that we are looking for.

The cell has a number of possible properties that we might be interested in - colour, height, width etc. - and so we also need Value to indicate that it is the value stored in it we want.

In practice we usually don’t need to quote a “full address” in this way to get at the data stored in a cell but you need to be aware how it appears it in its entirety and understand the principles.

If you would like to try this instruction out then open Excel and get to the VBA editor. Then use the Insert Module command and enter the lines:

Sub getvalue()
 temp = Application. _
  Workbooks("Book1"). _
    Worksheets("Sheet1"). _
     Range("A1").Value
 MsgBox temp
End Sub

Make sure that there is something stored in cell A1 and run this subroutine. You should see a message box appear displaying the correct value.

Line continuation.

You might be wondering what the underscore at the end of some of the lines is all about.

For example:

 temp = Application. _

The simple answer is that if you need to split a single command over more than one line you need to break the line with a space and then an underscore. This is the "continuation" character indicating that the instruction goes onto the next line.

Sometimes it is good to break lines in this way so that the meaning becomes clear.

Step Two - Objects and properties

The idea is that there are things called “objects” and an object can have properties.

For example, a spreadsheet cell is an object and one of its properties is its value. That is Range(“A1”) specifies the cell object at A1  and Range(“A1”).Value gives you its value property.

This sounds OK but how does it result in the long string of names and dots that we encountered above?

The answer is that objects are allowed to have other objects as properties.

Sounds complicated and to be honest you don’t have to think about it in this way if you don’t want to - but it is the right way to do it.

For example, the Application object can have a number of workbook objects among its properties, which in turn have worksheet objects and each of these have a collection of cell objects. 

You can think of this as an object “hierarchy” - application at the top, then workbook, then worksheet, then a cell range and so on.. 

Using VBA to control applications such as Excel is very much a matter of learning what objects there are and how they are arranged.

You will also discover that the VBA language includes an “object browser” that allows you to look at the hierarchy and select objects from it. For example, if you start the browser by pressing F2 you can look at Worksheet and discover that it has a Range property.

We will return to the object browser later.

<ASIN:1590599578>

<ASIN:0789731290>

<ASIN:0071627006>

<ASIN:013239667X>

<ASIN:0470046740>

<ASIN:0470503696>

<ASIN:0321508793>