VBE Intellisense for named ranges-CodePudding

What is a good practice of getting the named ranges of my sheet from VBE Intellisense?

I have created a workaround of getting the named range from the class module of a sheet, but I think is is too laborious, as can be seen from the code below, which is inside Sheet1 class module. What could be less code alternative?

Sub MY_SUB()
    Dim rg As Excel.Range
    Set rg = Me.frFirstPayment
    rg.Select
End Sub

Function frFirstPayment() As Excel.Range
    Set frFirstPayment = Me.Range("FirstPayment")
End Function

Function frClientAddress() As Excel.Range
    Set frClientAddress = Me.Range("ClientAddress")
End Function

CodePudding user response：

Something like this?

Enum Ranges
    FirstPayment = 0
    ClientAddress = 1
End Enum

Property Get NamedRange(NameIndex As Integer) As String
  Dim RangeNames As Variant
  RangeNames = Array("FirstPayment", "ClientAddress")
  NamedRange = CStr(RangeNames(NameIndex))
End Property
    
...
    Set frFirstPayment = Me.Range(NamedRange(Ranges.ClientAddress))
...

Perhaps too clunky? Enums are great, but limited to numbers

CodePudding user response：

Create a dedicated module, e.g. named VARS where you define public String constants, e.g.

Const FirstPayment as String = "Sheet1!$A$1:$B:B10"

(pressing F3 in the UI to paste the list of defined names will generate most of the syntax required for this) Then, in your actual code you could set your Range variable as

Set rg = Range(VARS....)

and you should get the required Intellisense after the first dot following VARS. (you could then release memory by deleting them from Name Manager)