如何使用公式在 VBA 中建立命名範圍（運行時錯誤 438）

Question

我正在嘗試使用 VBA 建立命名範圍。和FirstBodyCell都是ColumnCode單細胞。我希望命名範圍最終被定義為如下所示的內容（取決於它所FirstBodyCell在的列）：

=Offset(Sheet1!$E$8,0,0,COUNTA(Sheet1!$E:$E)-COUNTA(Sheet1!$E$1:$E$7),1)

使用下面的子加註

運行時錯誤“438”：物件不支援此屬性或方法

並指向 的行.Name = ColumnCode.Value。知道這段程式碼有什麼問題嗎？

Sub CreateNamedRange(FirstBodyCell As Range, ColumnCode As Range)
    With ActiveWorkbook.Names
            .Name = "col_" & ColumnCode.Value
            .RefersToR1C1 = "=Offset(" & FirstBodyCell & ",0,0," & _
                          "COUNTA(" & Columns(FirstBodyCell.Column) & ")-COUNTA(" & _
                          Range(Cells(1, FirstBodyCell.Column), FirstBodyCell.Offset(-1, 0)) & _
                          "),1)"
    End With
End Sub

Answer 1

Workbook.Names是一個產生集合類別的屬性，該集合類別公開、、和Names等成員。AddCountParentItem

物件Names沒有Name屬性或RefersToR1C1成員（因此錯誤 438 - 物件不支援/公開成員/屬性）。

您想要呼叫Add該物件的成員 - 該函數將您嘗試指派的值作為參數。另外，.Address將會傳回一個非 R1C1 格式的位址。嘗試使用RefersTo而不是RefersToR1C1.

With ActiveWorkbook.Names.Add( _
    Name:="col_" & ColumnCode.Value, _
    RefersTo:="=Offset(" & FirstBodyCell & ",0,0," & _
                      "COUNTA(" & Columns(FirstBodyCell.Column) & ")-COUNTA(" & _
                      Range(Cells(1, FirstBodyCell.Column), FirstBodyCell.Offset(-1, 0)) & _
                      "),1)")
    'the With block variable is the Name object that was created+added to the Names collection:
    Debug.Print .Name
End With

筆記史考特·克萊納指出您可能想要使用您在那裡提供的.Address對象Range，因此實際值RefersToR1C1是：

  RefersTo:="=Offset(" & FirstBodyCell & ",0,0," & _
                    "COUNTA(" & Columns(FirstBodyCell.Column).Address & ")-COUNTA(" & _
                    Range(Cells(1, FirstBodyCell.Column), FirstBodyCell.Offset(-1, 0)).Address & _
                    "),1)")