Using Variant Array containing Range Variables to populate Ranges to be later referenced by range Variable by name is failing

Question

Consider the Subroutine below. I almost have what I'm looking for, but it's not quite right.

Knowns to take into account: The Declarations statements for this Module have setup each variable (Totaling 63 - I've culled the majority to simplify the sample below) as Public Variables to be referenced at any time. I have done similar things with modifying Boolean Variable Values, but this is failing for some reason with Ranges and I think it has to do with the fact they are Objects.

The For Loop correctly pulls the relevant data and places it into the relevant placeholder within each Variant Array, meaning - the placeholder for StoreStateRng for example, holds the correct data frm the Ranges in the sheet, but when trying to manipulate the actual Range Variable stored inside the the placeholder, it fails to be referenced.

In theory - I would have expected this to physically set the Variable "StoreStateRng" to the range specified. . . It does indeed set rngVars(i) to the range specified, and gives it a Variant/Range Type when I inspect it on a Watch, but when inspecting the actual Variable at the same time, the Range Variable "StoreStateRng" is empty!

'Excerpt of Declarations Statement

'example of 3 of the 63 variables listed in the arrays below
Public Const StoreStateRng_Col As Integer = 23
Public StoreStateRng As Range
Public StoreStateRng_Val As Variant

'the Variant Arrays defined at the beginning of the sub
Public rngVar As Variant
Public rngVars() As Variant
Public rngVarStr() As Variant
Public rngVarCols() As Variant
Public rngVarColStr() As Variant
Public rngVarVals() As Variant
Public rngVarValStr() As Variant


Sub Get_Ranges()
  Set ws = ThisWorkbook.Sheets(wsNameMain)
  
  rngVars() = Array(StoreStateRng, StoreCityRng, StoreDateRng, StoreNumRng)
  rngVarStr() = Array("StoreStateRng", "StoreCityRng", "StoreDateRng", "StoreNumRng")
  rngVarCols() = Array(StoreStateRng_Col, StoreCityRng_Col, StoreDateRng_Col, StoreNumRng_Col)
  rngVarColStr() = Array("StoreStateRng_Col", "StoreCityRng_Col", "StoreDateRng_Col", "StoreNumRng_Col")

  'Get Known Range that never has blanks in data:
  Set StoreNumRng = ws.Range(Cells(2, StoreNumRng_Col), Cells(2, StoreNumRng_Col))
  Dim i As Integer
  i = 0
  'Get Full Range of Known data set to establish beginning and end rows
  rw = StoreNumRng.Row 'Pulled from the removed excerpt above
  EndRw = StoreNumRng.End(xlDown).Row 'Pulled from the removed excerpt above
  
  For i = LBound(rngVars) To UBound(rngVars)
    If VarType(rngVars(i)) = vbObject Then
      Set rngVar = ws.Range(Cells(rw, rngVarCols(i)), Cells(EndRw, rngVarCols(i)))
      Set rngVars(i) = rngVar
      Debug.Print "The Address for " & rngVarStr(i) & " = " & rngVars(i).Address
    End If
  Next i
  Debug.Print StoreStateRng(1, 1).Value 'Fails because I'm assuming this variable is not set.
  
  
End Sub

I did try modifying the .Name feature of the Variant's Placeholder and it references the SheetName$COL$ROW address properly (and even gives me a Named Range to Goto in the Sheet), and that Name.Name value matches the Name of the Variable - but the Debug.Print StoreStareRng(1,1).Value fails because the Object itself still is empty.

I feel like I'm really close to figuring it out, but having a hard time wrapping my head around what I'm missing.

The goal is to have named Variables with which to reference throughout the module. For example - the data set I'm working with is 20+ columns wide, offset roughly 20 columns from another existing set of data, and often between 10-30 rows deep. What I'm wanting to avoid is fullRange(3,6).Value for the 3rd row of the Sheet's Column 23. . . The Sheet's column 23 would be column 6 of the specified range). . . .

Instead, I would like to reference StoreStateRng(3,1).Value for this same exact range and use a Constant of the actual sheet's column to modify this should the scope of range change down the line vs trying to touch every single routine which may reference StoreStateRng . . .

I'm also hoping to avoid the cumbersome :

Sub Get_Ranges()
  Set ws = ThisWorkbook.Sheets(wsNameMain)
  ws.Activate
  Set StoreStateRng = ws.Range(Cells(rw, StoreStateRng_Col),Cells(endRw, StoreStateRng_Col))
  Set StoreCityRng = ws.Range(Cells(rw, StoreCityRng_Col),Cells(endRw, StoreCityRng_Col))
  Set StoreDateRng = ws.Range(Cells(rw, StoreDateRng_Col),Cells(endRw, StoreDateRng_Col))
  Set StoreNumRng = ws.Range(Cells(rw, StoreNumRng_Col),Cells(endRw, StoreNumRng_Col))
  'Repeat until all 21 needed ranges are populated
End Sub

Answer 1

When you do this

Set rngVars(i) = rngVar 

you're replacing the original Range variable with a different one, not assigning the original one to rngVar

Simpler version:

Sub Tester()

    Dim rngA As Range, rngB As Range, arrRanges As Variant
    
    arrRanges = Array(rngA, rngB)
    
    Set arrRanges(0) = Range("A1:A5")  'This *replaces* rngA...
    
    Debug.Print rngA.Address           'Error - rngA is still Nothing
    
End Sub

EDIT - your "cumbersome" example could be streamlined to be quite maintainable...

Sub SetRanges()
    Set ws = ThisWorkbook.Worksheets(wsNameMain)
    With ws.Range(ws.Cells(rw, "A"), ws.Cells(endRw, "A")).EntireRow
        Set StoreStateRng = .Columns(StoreStateRng_Col)
        Set StoreCityRng = .Columns(StoreCityRng_Col)
        Set StoreDateRng = .Columns(StoreDateRng_Col)
        Set StoreNumRng = .Columns(StoreNumRng_Col)
        '...
        '...
    End With
End Sub