Excel VBA Worksheet Range storage location best practices

Question

One of the issues I've never found a great solution for is defining the location of things in a sheet within a VBA project. If any of you have had to build a worksheet with VBA and worksheet functions, you know why this matters. It's incredibly frustrating to run a script and realize that the cell which held the result of your f-test moved from F20 to H20 because you had to add two columns of data and you didn't find all of the range references in your VBA that needed to be updated.

I'm looking for more ideas and best practices that have worked to systematically store VBA range references which are easy to update and keep in line with the evolving spreadsheet.

I'm listing what I think are the common options. I've done the first four before, but they just don't quite do the trick.

Option1: Define and program location variables at the top of each sub

This is generally what happens. At the top of each main sub, you define the range interface variables and then set them with fully qualified references. This is usually good enough for quick projects with one or two primary functions.

This becomes a problem when you start to have lots of nested functions. Do you re-define the headerRow and firstDataRow inside the moveDataRow() function? Or do you pass all of that info every time? What a pain. Manageable at small scale. Totally becomes a spaghetti monster and needle in the haystack as things grow (and we all know they usually do).

Option2: Define my own Worksheet object

I've gone to the lengths of defining my own worksheet object which has additional properties such as "headerRow" or "firstDataRow" so that I can store those properties in the worksheet code. Then when I define a worksheet object I use dim srcSheet as myWorksheet instead of dim srcSheet as Worksheet. This allows me to find the properties through the object model. ex: srcSheet.headerRow

This solution is AWESOME when you are dealing with lots of repeats of the same worksheet. It's a total waste of time if you have six sheets all doing something different with different properties needed. ex: one sheet might be a Monte Carlo control, while the other is running f-tests. I don't want to make a new worksheet object for each one.

Option 3: Dynamically find everything

This is probably the most robust, but incredible time consuming. Find a way to define every cell location based on it's content, formatting, and relative location. There is usually something specific about the cell you're trying to reference that won't change. Writing a script to find that thing makes things down the road much easier. however, this is a TON of work and isn't really feasible for every project. It's so much work it's a waste of time for most projects.

Option 4: Module level location variables

Use a new module for primary functions that interface with a subset of sheets. Then define all of the locations stuff once at the top of that module as private constants.

This works well for mid-sized projects. However, when you're dealing with multiple modules and moving data accross multiple worksheets as it's being processed, you start to have to define the same constants multiple times.

Option 5: Create a "locations" Module

This might work well since all locations will be in one place. I haven't tried it yet, but this is the reason I'm asking this question here. Anytime you're creating a location reference, it goes in the location module. Each sheet would have a defined prefix that identifies that specific sheet. This way, in a sub or function, instead of setting sheet range locations in the sub, you would call srcHeaderRow = dataHeaderRow to define that data headers are on row 5 instead of having to redefine the row each time you need to use srcHeaderRow.

What else? Anything you've tried that was just great?

What other methods have you used to keep cell/range locations for interfacing between VBA and worksheets going?

Answer 1

I don't know if these qualify as a best practices but here is how I do it.

Changing Worksheets' Code Name

Why dim srcSheet as myWorksheet when you can just change the Worksheet's Code Names?

Enumerate the Columns and Header Rows

I create an Enum for each Worksheet to enumerate the each column, [First Column], [Last Column], and [Header Row]. Enclosing Enum members in brackets and using special characters for their names will create hidden members. See sample code below. If I later decide to reorder the columns, add columns, or reposition the data, all I have to do is update the enum and the code will not break.

Right clicking the Object Browser gives you the option to show hidden members of Object and Enums.

Enums are available to Intellisense

Sample Data

I used the first 10 rows from Excel Sample Data for the data.

Demo Code

Public Enum EnumWSDataColumns
    dcOrderDate = 3
    dcRegion
    dcRep
    dcItem
    dcUnits
    dcUnitCost
    dcTotal
    [_dcFirstColumn] = dcOrderDate
    [_dcLastColumn] = dcTotal
    dcHeaderRow = 4
End Enum

Sub DemoEnum()
    Dim r As Long, c As Long
    Dim value As String * 10
    With wsData
        For r = dcHeaderRow To .Cells(.Rows.count, [_dcFirstColumn]).End(xlUp).Row
            For c = [_dcFirstColumn] To [_dcLastColumn]
                value = .Cells(r, c)
                Debug.Print value; "|";
            Next
            Dim n As Long
            n = ([_dcLastColumn] - [_dcFirstColumn] + 1) * (Len(value) + 1)
            Debug.Print
            Debug.Print String(n, "-")
        Next
    End With
End Sub

Output