☰

Como criar um intervalo nomeado em VBA com fórmula (erro em tempo de execução 438)

tag-icon • tag-icon microsoft-excel vba named-ranges
Como criar um intervalo nomeado em VBA com fórmula (erro em tempo de execução 438)

Estou tentando criar um intervalo nomeado usando VBA. Ambos FirstBodyCelle ColumnCodesão células únicas. Quero que o intervalo nomeado seja eventualmente definido como algo parecido com isto (dependendo da coluna em que FirstBodyCellestá):

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

Usando o sub abaixo aumenta

Erro em tempo de execução '438': o objeto não oferece suporte a esta propriedade ou método

e aponta para a linha com .Name = ColumnCode.Value. Alguma idéia do que há de errado com esse código?

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

Responder1

Workbook.Namesé uma propriedade que produz uma Namesclasse de coleção, que expõe membros como Add, Count, Parente Item.

Um Namesobjeto não possui uma Namepropriedade ou um RefersToR1C1membro (daí o erro 438 - membro/propriedade não é suportado/exposto pelo objeto).

Você deseja invocar o Addmembro desse objeto - essa função leva como parâmetros os valores que você está tentando atribuir a ele. Além disso, .Addressretornará um endereço que não está no formato R1C1. Tente usar RefersToem vez de 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

ObservaçãoScott Craneraponta que você provavelmente desejará usar os .Addressobjetos Rangeque está fornecendo lá, então o valor real RefersToR1C1seria:

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

informação relacionada