Erro de tempo de execução da função VBA do Excel 1004: erro definido pelo aplicativo ou definido pelo objeto

Question

Estou tentando aprender funções com o propósito de simplificar e reutilizar código sempre que necessário.

Comecei transformando algo que uso com frequência em uma função: Retornando o valor inteiro da última coluna não vazia em uma planilha.

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = Range(strColName).Offset(Rows.Count - 1, 0).End(xlUp).Row
End Function

Sub PracticeMacro()
    intItemCount = FindLastDataLine("A:A")
    MsgBox ("There are " & intItemCount & " rows of data in column A.")
End Sub

Quando executo isso, recebo o erro de runtime '1004' "Erro definido pelo aplicativo ou definido pelo objeto", que a Help ajuda a definir como "falha de outra pessoa" para citar não exatamente textualmente.

Onde posso estar errado?

    

Answer 1

Por que seu método não funciona: há dois motivos aqui. O primeiro, quando você coloca Rows.Count , não há referência para Rows - é uma propriedade de Range . Para corrigi-lo, basta referenciar o mesmo intervalo que você já é (basta anexá-lo antes de chamar Rows.Count desta forma:

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = Range(strColName).Offset(Range(strColName).Rows.Count - 1, 0).End(xlUp).Row
End Function

A segunda razão é que você está usando Offset . Offset literalmente muda um Range de quanto você diz. Você não quer deslocar todo o intervalo de células, mas encontrar a última célula no intervalo. Você pode fazer isso simplesmente alterando Offset para Cells , removendo a chamada Range() inicial (já que vamos escolher uma célula) e alterando 0 para a coluna desejada. No entanto, como você passa a coluna como "A:A" , isso não é possível, então você teria que substituí-la por Range(strColName).Column da seguinte forma:

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = Cells(Range(strColName).Rows.Count, Range(strColName).Column).End(xlUp).Row
End Function

Uma solução melhor: a solução a seguir funcionará em todas as versões recentes do MS Office (2003, 2007 e 2010) e lidará com erros. Você o chama passando a letra da coluna ou o número da coluna:

Function GetLastDataRow(col As Variant) As Long
    GetLastDataRow = -1
    If (IsNumeric(col) And col >= 1) Or Len(col) <= 2 Then
        On Error Resume Next
        GetLastDataRow = _
            Cells(Cells(1, col).EntireColumn.Rows.Count, col).End(xlUp).Row
        On Error GoTo 0
    End If
End Function

O seguinte ilustra como você chamaria essa função e alguns exemplos de saídas. Vamos supor que a planilha inteira esteja limpa, exceto por alguns dados aleatórios inseridos nas células B1 a B8 e B10 ( B9 é deixado em branco). Observe que você não insere a coluna como um intervalo, mas sim a letra da coluna ou o número da coluna (valores inválidos retornam -1):

GetLastDataRow(1)         =  1    GetLastDataRow("A")       =  1
GetLastDataRow(2)         = 10    GetLastDataRow("B")       = 10
GetLastDataRow("AX")      =  1    GetLastDataRow("A:X")     = -1
GetLastDataRow("Oops...") = -1    GetLastDataRow(200)       =  1

Como uma nota técnica, se o método Cells falhar, presume-se que a entrada seja inválida, portanto, a função retorna -1. Eu recomendo que você use esta prática (retorne valores inválidos se a entrada for inválida) em sua função, isso o ajudará muito a evitar erros no futuro.

Como isso funciona, ele encontra a última linha possível em qualquer coluna específica (depende da sua versão do MS Office) e, em seguida, usa o método End para localizar a última célula dessa coluna com dados.

Aqui está uma versão alternativa que retornará 0 se todas as células da coluna estiverem em branco:

Function GetLastDataRow(col As Variant) As Long
    GetLastDataRow = -1
    If (IsNumeric(col) And col >= 1) Or Len(col) <= 2 Then
        On Error Resume Next
        If IsEmpty(Cells(Cells(1, col).EntireColumn.Rows.Count, col).End(xlUp).Value) Then
            GetLastDataRow = 0
        Else
            GetLastDataRow = _
                Cells(Cells(1, col).EntireColumn.Rows.Count, col).End(xlUp).Row
        End If
        On Error GoTo 0
    End If
End Function

Exemplo de saída:

GetLastDataRow(1)         =  0    GetLastDataRow("A")       =  0
GetLastDataRow(2)         = 10    GetLastDataRow("B")       = 10
GetLastDataRow("AX")      =  0    GetLastDataRow("A:X")     = -1
GetLastDataRow("Oops...") = -1    GetLastDataRow(200)       =  0

    

Answer 2

Você escreveu "Retornando o valor inteiro da última linha não vazia em uma planilha." Mas parece que você está tentando obter a última linha não vazia em uma coluna específica de uma planilha.

Se você mudar sua função para isso, acho que funcionará:

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = ActiveSheet.Cells(ActiveSheet.Rows.Count, strColName).End(xlUp).Row
End Function

    

Answer 3

Pode não corrigir o seu erro, mas devolveria o ItemCount dentro do intervalo ...

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = Evaluate("COUNTA(" & strColName & ")")
End Function

Sub PracticeMacro()
    intItemCount = FindLastDataLine("A:A")
    MsgBox ("There are " & intItemCount & " rows of data in column A.")
End Sub

Observe que há outras maneiras de chamar essa função do COUNTA Excel ...

    

Answer 4

Tente:

FindLastDataLine = _
     Range(strColName).Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row

As linhas são uma propriedade.