Excel Class NYC Blog – Learn Excel VBA To Navigate

To learn excel VBA is to relearn Excel from the ground up. In Excel you are comfortable selecting cells, sheets and workbooks using your keyboard or mouse. Our previous tutorial introduced the importance of Excel VBA variables. This tutorial covers navigation techniques.

Navigate From Cell To Cell

To navigate to a cell you can use the range object, the cell object, the activecell object, or the selection object. The range syntax takes the form

Range(“A5”).Activate

Note that A5 is surrounded by quotes, as all text in VBA needs to be surrounded by quotation marks (excluding variables). Also note the range object alone won’t move the active cell, but it is the .activate method that’s like saying now click that cell. The range object also allows you to select multiple cells as in
Range(“A5:B6”).activate
VBA purists will rightly argue that you should really never need to select a range. You can dimension a range variable, then assign the range to the variable to manipulate the cells in memory (which is faster than reading and writing to a sheet). For example, you could also write
Dim rmyrange as range
Set rmyrange=range(“A5:B6”)
Now it’s easier shorthand to refer to rmyrange. Note that when working with object variables like ranges you must use the set keyword to assign the range to the object.

Navigate Using The Cell Object.

At times it may be easier to navigate using the cell object. The reason you would use the cell object instead of the range object is because the cell object refers to columns by number instead of by letter. The cell object syntax is
Cells(rownum, colnum)
Imagine you’ve used the cells.find(“my text”).activate command to locate a column label. You could tell what column number you are in using the activecell.column command, or substitute that command into the cells() command in place of the column reference.

Navigate Using The Arrow Keys

To navigate programmatically using the arrow keys use the offset method (prefaced by an object like range, activecell, or selection object.
The syntax for the offset method is
Object.Offset(rowcount, columncount).Activate
Where object is range, activecell, or selection. Note the to navigate up or left, use a negative symbol (a hyphen) in front of the rowcount or columncount parameter.
Another useful navigation technique in Excel is to use the ctrl+arrow key to jump through a contiguous range of cells. The Excel VBA equivalent is the end method. The end method requires a direction. The syntax is
Object.end(xldirection).Activate
Where object is a range, activecell, or selection. xldirection is either xlup, xldown, xltoright, or xltoleft.

VBA to Navigate Through Worksheets

In addition to navigating within a worksheet, you will also need to use VBA to navigate from sheet to sheet.
One technique to select a sheet is by name. The syntax would say
Worksheets(“sheetname”).Activate
Where sheetname is the actual name on the sheet tab enclosed in quotation marks. Note that worksheets does not include chart sheets. If you need to select a chartsheet, use the sheets object. The syntax is
Sheets(“my sheetname”).Activate
You can also navigate to a worksheet by indicating its index number. The index number is the numeric position of the sheet in the workbook. The syntax is
Worksheets(123).Activate
Where 123 is the sheets numeric position and has to be a number less than or equal to worksheets.count
A neat way to navigate from sheet to sheet in excel is to press ctrl+pgdn to move to the right one sheet tab, while ctrl+pgup moves one sheet to the left. In VBA you can accomplish this with
Activesheet.Next.Activate
Activesheet.Previous.Activate
These are not the only techniques to navigate, we encourage you to add comments with your tips. You can learn more about our Excel VBA Class NYC tutorials here