Navigating the Model

On complex Excel models with multiple, large worksheets, your users often need to "jump" to a specific location. For example, you may have defined a set of financial statements - Balance Sheet, Income Statement, etc. To improve their user's experience, power programmers usually set up some sort of navigation buttons or menus, providing an easy way to find the useful content in the model.

Named ranges are certainly helpful in this situation. You can define a named range which points to the top-left corner of the indicated content. The user can then GOTO that range, either by selecting it in the Name box, or using the GoTo menu command. Unfortunately, Excel doesn't know that we want the named range to the the top-left cell in our view, so it only scrolls enough to get the name "in view", even if that means being on the bottom of our visible range.

To get around this particular annoyance, and to ensure my users have the most pleasant experience possible when using my Excel applications, I developed the GoScroll method. This re-usable routine accepts a named range as it's only parameter. When called, it will activate the appropriate worksheet and jump to the indicated range, making sure to scroll the window such that the range ends up in the upper-left corner of the display. Here's the code:


Public Sub GoScroll(ByVal strRange As String)

Dim intPane As Integer, rng As Range

On Error GoTo Fail

Set rng = Range(strRange)
rng.Parent.Activate

intPane = ActiveWindow.Panes.Count

With ActiveWindow.Panes(intPane)
.SmallScroll Up:=.VisibleRange.Row - rng.Row, _
ToLeft:=.VisibleRange.Column - rng.Column
End With

rng.Select

Fail:
Set rng = Nothing

End Sub


This routine leverages the SmallScroll method of the ActiveWindow.Panes object. If you have split your window, it will have more than one "pane", so this code assumes it will be working with the last pane in the collection. Adjust this as required, depending on how your window splits are set up.

With this routine in place, I can now hook up navigation buttons or menus for any named range in the workbook. I call the code like this: GoScroll("Income_Statement") where "Income_Statement" is the named range pointing the the upper-left cell in my income statement section.

Note that this logic requires that the range must be defined globally for the workbook, not locally on a specific worksheet. I'll leave it as an exercise for the reader to figure out how to handle local names. I hope you find this useful. As always, I welcome your feedback.

Dynamic Names

One of Excel's most powerful features is the ability to define Names. A Name can refer to a single cell, a range, or a calculated value. You can even define a dynamic Name that refers to a changing range of cells.

You might need to refer to a list of items in your code, but you don't know how big the list will get or where someone might move it. To automatically sync the named range to the data, define a dynamic Name using the Insert -> Name -> Define menu, and this dialog:

The Refers To: field contains a couple of formulae instead of a cell reference, rephrased:
=OFFSET($B$2,1,0,COUNTA($B$2:$B$1000)-1,1)

The Offset formula defines a corner cell, how many rows away, columns away, how many rows to include, and columns to include.  

For the corner cell use the table header, not the first data item, because somebody might delete that cell. The CountA function in the How Many Rows parameter counts non-blank cells in the column.  The heading cell is included in the count, so subtract 1 from it's result to give the actual number of rows in the range.  This example sets an arbitrary limit of 998 items - your knowledge of the likely dataset and any use of the worksheet below the data table are factors in specifying this value.

Blank rows within the data would be a problem, so if that's a possibility, you can nest a few more functions to detect the range of cells you want... but it does get messy and there's no function help when editing names in the dialog, so be warned.

Anyway, now you can use that Named Range in code...

Dim rng as range
For Each rng in Range("Customer_List")
... some code
Next

And you're not putting any actual cell addresses in there so the code stays clean and safe.

An important factor when defining a Name this way is that your Reference formula needs to be safeguarded against possible edits to the worksheet. If a user deletes a cell which is part of the Name's RefersTo property, that Name could break, resolving to a #REF error for ever more. Good Excel programmers anticipate this possibility and set up Names (and Formula references) for maximum durability, helping workbooks live longer.