命名范围的VBE智能感知

Question

从VBE Intellisense获取我的工作表的命名范围的良好实践是什么？

我已经创建了从工作表的类模块中获取命名范围的解决方案，但我认为这太费劲了，从下面的代码中可以看到，它位于Sheet1类模块中。有什么更少的代码可供选择？

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

编辑基于苏蒂姆·威廉姆斯的评论

下面是我如何生成我的报表的各种命名范围：

复制和粘贴以下过程到一个标准模块(我已经给它一个快速条形图标快捷链接)。

2-激活你想要得到它的所有命名范围的工作表。

3-运行程序，从VBE或单击que快速栏图标。

4-将剪贴板粘贴到活动样式表类模块中。

5-现在，输入"set rg = me.z"，并从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

下面有第二个表单代码，它使用冒号语句":“生成一个”紧凑“代码(每个属性一行代码)。

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

Answer 1

像这样吗？

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))
...

也许太笨重了？Enum很好，但仅限于数字。

Answer 2

创建一个专用模块，例如命名为VARS，其中定义公共字符串常量。

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

(按下UI中的F3以粘贴已定义名称的列表将生成所需的大部分语法)然后，在实际代码中，可以将范围变量设置为

Set rg = Range(VARS....)

您应该在VARS之后的第一个点之后获得所需的Intellisense。(然后您可以从Name Manager中删除它们，从而释放内存)