CODEWITHSUNDEEP
excelvbavbe
sergio trajano
sergio trajano

Reputation: 311

VBE Intellisense for named ranges

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

Edit based on SO Tim Williams's comment

Here is how I am generating the various named ranges of my report sheets:

1 - Copy and paste the following procedure into a standard module (I have given it a quick bar icon shortcut link).

2 - Activate the sheet you want to get all its named ranges.

3 - Run the procedure, from the VBE or clicking que quick bar icon.

4 - Paste the clipboard into the activesheet class module.

5 - Now, there, type, for example, "set rg = me.z", and select the desired named range from the Intellisense.

Sub Named_Ranges_Properties_Sheet_Class_Module_To_Clipboard()
    Dim r As Name, propName As String, strOut As String, ws As Worksheet, obj As New DataObject
    Set ws = ActiveSheet
    'BUILD THE STRING OUTPUT
    For Each r In ActiveWorkbook.Names
        If Excel.Range(r).Parent.Name = ws.Name Then
            propName = "z" & r.Name
            strOut = strOut & _
            "Property Get " & propName & "() As Excel.Range" & vbNewLine & _
            vbTab & "Set " & propName & " = Me.Range(""" & r.Name & """)" & vbNewLine & _
            "End Property" & vbNewLine
        End If
    Next r
    'UPLOAD TO THE CLIPBOARD
    If Len(strOut) > 0 Then
        obj.SetText strOut
        obj.PutInClipboard
        MsgBox "Ok"
    Else
        MsgBox "Activesheet has no named range."
    End If
End Sub

Below we have a second form code, which generates a "compact" code (one line of code per property), using the colon statement ":".

Sub NAMED_RANGES_PROPERTIES_SHEET_CLASS_MODULE_TO_CLIPBOARD_()
    Dim r As Name, propName As String, strOut As String, ws As Worksheet, obj As New DataObject, k As Integer
    Set ws = ActiveSheet
    'BUILD THE STRING OUTPUT
    For Each r In ActiveWorkbook.Names
        If Excel.Range(r).Parent.Name = ws.Name Then
            k = k + 1
            propName = "z" & r.Name
            strOut = strOut & _
            "Property Get " & propName & "() As Excel.Range: Set " & propName & " = Me.Range(""" & r.Name & """)" & ": End Property" & vbNewLine
        End If
    Next r
    'UPLOAD TO THE CLIPBOARD
    If k > 0 Then
        obj.SetText strOut
        obj.PutInClipboard
    End If
    'MESSAGE BOX
    MsgBox k & " named rage(s) found."
End Sub

Upvotes: 3

Views: 154

Answers (2)

Spectral Instance
Spectral Instance

Reputation: 2494

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)

Upvotes: 1

Sam
Sam

Reputation: 5731

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

Upvotes: 1

Related Questions