|
|
|
This page is
a series of navigation macros presented in an abbreviated format in that
detailed instruction is not presented. If you have trouble with the instructions
provided, then review other exercises that present such detail.
Exercise 1
Moving the cell pointer:
Turn on the macro recorder,
with Use Relative References NOT checked. Click on any cell, or press an
arrow key. Stop the macro. Then activate the module containing the
macro. Your macro should look something like this:
Sub Macro1()
Range("A12").Select
End Sub
Note that when this macro is
run, the cell pointer will always jump to cell A12 and that pressing an
arrow, or clicking with the mouse when recording the macro, had no bearing
on which direction you actually moved to get there, the cell pointer simply
jumps directly to the same cell. This is Using Absolute References.
Absolute
meaning, you always want to go to this cell. This is very similar to the
manual GoTo command (activate with Edit GoTo in the menu or pressing Ctrl
+G or the F5 function key).
Repeat the above steps with
Use Relative References checked. This macro should look something
like:
Sub Macro2()
ActiveCell.Offset(1,
0).Range("A1").Select
End Sub
A totally different macro and
all you did was click on a cell! This macro jumped the cell pointer with
Relative Referencing. This means relative to the starting cell position.
Let's analyze this macro. First, we'll drop the Range("A1"), more on this
later:
ActiveCell.Offset(1,
0).Select
The offset command reads, move
down one row and stay in the same column. If you want to move down one
cell and to the right one cell, the offset would look like: offset(1,1)
What this step is saying
is: from the active cell, move down one cell and select it. You could
replace the word select with Copy or one of many other commands. To say
it another way: from the active cell, take action as it relates
to the cell one row down and in the same column; that action is to Select
it. The action could something else, for example Copy.
Most of your macro commands
will be in the Relative Referencing format, i.e. using the Offset("row,column").Select
format.
Examples:
Example 1
You have a large spreadsheet
with various interest computations and the interest computations refer
to a single cell containing the interest rate. Since this is always the
cell you want to go to, then the Absolute Referencing method will work.
So your macro may look like the first example above and as repeated here:
Sub GoToInterestRateCell()
Range("D5").Select
End Sub
What if you later reorganize
your worksheet and you have inserted a row above row five? Now the
macro will be taking you to the wrong cell. The more preferable method
of using this macro would be to name the cell and refer to the cell by
name. So then, your macro would look something like:
Sub GoToInterestRateCell()
Range("IntRate").Select
End Sub
These types of navigation (GoTo)
macros should be in this format, using range names, which are easier to
interpret and future problems (resulting from things like inserting a row)
are greatly reduced.
Example 2
You are making entries in
large list that is several hundred rows long and 40 columns wide.
You make entries in the next blank row and when you get to the last column,
you want to jump the cell pointer down one row and to the left 39 columns,
ready to make your next entry. You macro would look something like this:
Sub NextEntry()
ActiveCell.Offset(1,
-39).Select
End Sub
This macro jumps the cell pointer
as expected. Note the negative 39 to jump to the left.
It's time to get technical:
Let's look at this macro
again
ActiveCell.Offset(1,
0).Select
Above, I used language like:
from the active cell, move down one cell and select it. I then said we
were really performing action on the cell down one from the active cell.
And the action could by Copy, Delete, Select, etc.
The active cell is a Visual
Basic Object and Select, Copy and Delete are Methods. The
Offset is a part of the object. Therefore, this macro may be defined as
follows:
Object.Object.Method
ActiveCell.Offset.Select
So now the language is:
for the Object (the active cell), down one row, perform a Method,
such as Select, Delete, Copy, etc.
Don't worry too much about
these definitions for now as they are a bit confusing. They are being introduced
here, as most all VBA reference material uses this language.
What happened to the Range("A1")?
Above, we dropped the Range("A1")
from the macro.
Discussion:
When you recorded the macro,
it looked like this:
ActiveCell.Offset(1,
0).Range("A1").Select
As you get more experience in
writing macros, you will most likely edit this to drop the Range("A1")
so it looks like this:
ActiveCell.Offset(1,
0).Select
Explanation:
What the original macro is
doing is defining the active cell as A1, i.e. the upper left of the current
work area is the active cell. To test this, edit the macro to read:
ActiveCell.Offset(1,
0).Range("A5").Select
When you run the macro it actually
jumps down five cells. So what gives? The quick answer is that this
is a poorly designed feature that does have some functionality, but in
more advanced uses. Most all users drop it from the macro to avoid confusion.
Example 3
You have a large list of
data, let's say a database of daily sales figures. Today the list is, say,
100 rows long, tomorrow is could be 103, then 110 and so on. You desire
to jump the cell pointer to the next blank row at the end of the list.
First, how would you do this without a macro? You would:
-
Place your cell pointer within
the list
To ensure the next command
will work
-
Press Ctrl Down Arrow
Jumps the cell pointer
to the bottom of the list. It stops on the last cell containing data
-
Press the Down Arrow once
To place the cell pointer
on the next blank line
With the focus only on the
Ctrl Down Arrow portion of these steps, let's review the macro:
ActiveCell.End(xlDown).Select
This line would jump the cell
pointer to last entry in the list. So how do we get to the next blank row?
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
In English, jump to the bottom
of the list, then jump one more row down.
In order for the Ctrl Down
Arrow keys to work (and the xlDown macro), the cell pointer needs to be
in the list. So a line should be entered in the macro to force the cell
pointer to jump inside the list. Assuming the you gave the first cell in
the list a range name of ListTop, the completed macro would look like this:
Sub JumpToNextEntry()
Range("ListTop").Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1,
0).Select
End Sub
Some comments on this macro:
Recording this macro with
Relative References (instead of typing it) would result in the same commands.
Good macro writing takes
into account most reasonable problem areas, therefore, if the first line
is left out, the cell pointer will end up somewhere else most of the time
as you will most likely forget to position it inside the list before executing
the macro.
The first line is the Absolute
Reference method mentioned at the beginning of this exercise, using a range
name.
The cell reference inside
the Range parentheses must be in quotes. This is true if the reference
is a cell reference or a range name. (If the quotes are omitted, then the
macro is looking for a variable with that name.)
The ActiveCell object can
be replaced with Selection. Selection means the cell or range of
cells. ActiveCell means just that, the active cell. If a range
is highlighted, the "white" cell is the active cell. Many times these mean
the same thing, the cell pointer's position, but as you get more advanced,
the differences get more significant.
Remember how the Ctrl Arrow
works and the macro command End(xlDown). The cell pointer jumps down the
list until it finds a blank cell, and it stops on the last cell with data.
If your list has blank cells in the column that contains the cell pointer,
this feature will not work as desired. It is a good practice to ensure
the first column in a list contains no blank cells. Other articles discuss
the details of this.
Example 4
In Example 2 above, we had
this following macro
Sub NextEntry()
ActiveCell.Offset(1,
-39).Select
End Sub
Taking what we learned in Example
3, let's revise this macro as follows:
Sub NextEntry()
Selection.End(xlToLeft).Select
Selection.Offset(1, 0).Select
End Sub
By using this approach it makes
no difference how many columns are in the list, the cell pointer, jumps
back to the first column, then down one row. This is, of course, if there
are no blank cells in the row.
Example 5
Now, let's get fancy.
The above examples placed each step of the selection process on a separate
line. But we can combine them as follows:
Selection.End(xlToLeft).Offset(1,
0).Select
End Sub
From the current cell pointer
position, jump back to the left, to the first column in the list, then
down one row.
In Example 3, jumping to
the bottom of the list:
Sub NextEntry()
Range("ListTop").Select
Selection.End(xlDown).Offset(1,
0).Select
End Sub
Note how the End(xlDown) command
is combined with the offset.select. What about combining the Range() with
the rest of the macro? The two macros that follow are examples of what
you may try, however neither one of them will work.
This macro will not work
as there is no object (such as Selection or ActiveCell):
Range("ListTop").Select.End(xlDown).Offset(1,
0).Select
This macro doesn't work as Range
does not have a Selection property:
Range("ListTop").Selection.End(xlDown).Offset(1,
0).Select
What all this means is that
you can do a lot with the current selection, but with the Range as the
object, as in this case jumping to a range, the commands must go on two
or more lines.
The term Object, Methods,
and Properties are an integral part of VBA. Some commands can be more than
one of these, depending on how they are used. For example, Range can be
an Object or a Method. This is covered in-depth in other articles.
Related Topics:
-
Range Names
-
Spreadsheet Navigation (without
macros)
-
Selecting (highlighting) ranges
with macros
-
A Quick Review of Different
Methods of Moving
the Cell Pointer with Macros
-
How to Record a Macro
|
|
