VBA运行报错1004：Global对象的Range方法调用失败求助

核心错误原因 #

你遇到的error 1004 in the method of 'Range' of the object 'Global'报错，核心是代码中Range对象构造、函数调用存在语法错误，具体问题和修复方法如下：

可直接复现报错的问题点 #

• Range构造语法错误
  原代码中Set Rango = Range("B6:B", Trim(Str(UltimaFila)))写法不符合VBA规则，你错误地给Range传入了两个不匹配的参数，正确的写法是将列标识和行号拼接为完整的地址字符串，修改为：

Set Rango = Range("B6:B" & UltimaFila)

• WorksheetFunction调用语法错误
  原代码中Set Maxima = Rango.Find(Application, WorksheetFunction.Max(Rango), LookIn:=xlValues, LookAt:=xlWhole)多写了一个逗号，误将Application对象作为Find方法的查找值参数，正确写法是将Application和WorksheetFunction作为整体调用，修改为：

Set Maxima = Rango.Find(Application.WorksheetFunction.Max(Rango), LookIn:=xlValues, LookAt:=xlWhole)

建议优化的潜在问题 #

• 变量冲突：模块顶部已经声明了全局变量Private Maxima As Integer，又在过程内重复声明Dim Maxima As Range，会引发变量优先级冲突，建议修改其中一个的命名，比如局部Range对象改名为MaxRng
• 未声明变量：UltimaFila、valor均为隐式声明，建议在模块顶部添加Option Explicit强制变量声明，避免未知错误
• 边界缺失：如果B6到最后一行的范围全为空、或者Find没有找到匹配值时，直接调用Maxima.Value会触发新的报错，建议添加空值判断逻辑

修复后完整代码 #

Option Explicit
Private Maxima As Integer

Private Sub lblCódigoClientes_Click()

End Sub

Private Sub UserForm_Initialize()
    Inicializo
End Sub

Public Sub Inicializo()
    Dim Rango As Range
    Dim MaxRng As Range
    Dim UltimaFila As Long
    Dim valor As Long
    
    UltimaFila = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
    txtNombre = ""
    txtApellido = ""
    txtTelefono = ""
    txtEmail = ""
    
    If UltimaFila = 5 Then
        lblCodigo.Caption = 1
        Exit Sub
    End If
    
    Set Rango = Range("B6:B" & UltimaFila)
    ' 先判断范围是否有数值
    If Application.WorksheetFunction.CountA(Rango) = 0 Then
        lblCodigo.Caption = 1
        Exit Sub
    End If
    
    Set MaxRng = Rango.Find(Application.WorksheetFunction.Max(Rango), LookIn:=xlValues, LookAt:=xlWhole)
    ' 判断是否找到最大值
    If Not MaxRng Is Nothing Then
        valor = MaxRng.Value
        lblCodigo.Caption = valor + 1
    Else
        lblCodigo.Caption = 1
    End If
End Sub

内容的提问来源于stack exchange，提问作者Geremías Lovera