Step Three - what can you leave out?

Now that you know how to refer to the contents of a cell in a VBA program the next step is to make it all a little more practical!

When you write a reference to an object's properties you generally don’t bother to give its full name because anything you leave out is filled in by a default.

The defaults simply correspond to the currently active object of the required type.

For example, if you use

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

then the Excel Application is assumed because it is the “active” application.

If you were writing VBA code in Word say  then it would be the active application.

In the same way the currently active workbook is used and this is, of course, the workbook with the module sheet that contains the VBA code you are running.

You can even get away with removing the reference to the sheet but sometimes this goes wrong because the active worksheet isn't what you expect.

Step Four - the Active sheet.

As well as leaving out the application and workbook reference you can also leave out the Worksheet reference as in

temp = Range("A1").Value

But sometimes you want to run VBA code and make sure that the active sheet is the one you intend to use.

To do this you have to make use of the Activate method. Most objects have methods as well as properties and you can think of a method as something you ask an object to do.

For example,

worksheets(“Sheet1”).Activate

asks “Sheet1” to execute its Activate method - which of course makes it the active sheet. Notice that it is Sheet1 of the active workbook that is used so you are sill making an assumption.

So now if you try

Sub getvalue()
 Worksheets("Sheet1").Activate
 temp = Range("A1").Value
 MsgBox temp
End Sub

you should find that it works as expected and now you will see the contents of cell A1 on Sheet1 not matter what sheet you have selected before running the program. Also notice that Sheet1 will be selected after the program has run.

You can probably guess that there is a similar Active method for the Workbook object. You can make any Workbook active using code similar to:

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

This makes the Workbook with the name "Book1" the active Workbook so ensuring that the cell is in Book1 and Sheet1.

Step 5 - With

By making sure that the workbook and worksheet that you want to work with are active you can forget about having to mention them.

However you often want to work with multiple workbooks and multiple sheets and having to switch which one is active all the time is a bit of a waste of time.

Fortunately there is another way to avoid having to repeatedly write out long names - you can use the VBA commands With - End With.

The idea is that you quote a long string of names that you want to add to the front of any reference used within the With block.

For example,

Sub getvalue()
With Worksheets("Sheet1")
 temp = .Range("A1").Value
End With
 MsgBox temp
End Sub

After the "with" the reference

.Range(“A1”).Value

is expanded to

Worksheets(“Sheet1”).Range(“A1”).Value

Any reference  that you use between the With and End With commands and which starts with a dot would be expanded in the same way.

Think of the dot as a sort of invitation for the With command to add the rest of the name!

Notice that in this example the Workbook used is the currently active one. You can be more specify about the object that you want the With-End With to work with.

For example, to specify that you want Workbook Book1 and Sheet Sheet1 to be the default for all of the instructions between the With-End With you could use:

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

This is a perfectly general idea. Whenever you want an object to be the default use a With object .,. End With statement.

<ASIN:0470044012>

<ASIN:0470279591>

<ASIN:0470475358>

<ASIN:0471499226>

<ASIN:0596003595>

<ASIN:0495106836>

<ASIN:1840782714>

<ASIN:075066097X>