Excel VBA Worksheet Range storage location best practices

Question

Defining the placement of items in a sheet within a VBA project is one of the problems for which I've never found a great answer. If you've ever had to construct a worksheet using VBA and worksheet functions, you understand why this matters. Running a script only to find out that you had to add two columns of data and missed some range references in your VBA that needed to be updated caused the cell that contained the result of your f-test to move from F20 to H20.

In order to systematically store VBA range references that are simple to update and stay up with the changing spreadsheet, I'm seeking additional suggestions and tried-and-true best practices.

Can someone please help me with this?

Answer 1

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

For each worksheet, I generate an Enum to enumerate the [First Column], [Last Column], and [Header Row] columns. Enum members can be made hidden by enclosing them in brackets and giving them names with special characters. Here is some sample code. Update the enum, and the code won't fail if I later decide to rearrange the columns, add columns, or move the data.

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

Enums are available to Intellisense

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