VBA按首行值为Library工作表列命名时出现1004错误，求排查

错误根源分析 #

触发1004错误的常见原因如下：

• 表头违反Excel命名规则：Excel定义名称时，不能包含空格、冒号(:)、斜杠(/)、反斜杠()等特殊字符，不能以数字或下划线开头，也不能与单元格地址（如A1、B2）重名。如果第一行单元格值不符合规则，Names.Add会直接报错。
• 表头单元格为空：空值无法作为名称使用，尝试添加空名称会触发错误。
• 重复名称：多列表头内容相同时，重复添加同名名称会导致冲突报错。
• 范围引用逻辑错误：代码中Resize(.UsedRange.Rows.Count + 1, 1)的计算有误——UsedRange.Rows.Count包含了表头所在的第一行，从第二行开始的有效数据行数应为UsedRange.Rows.Count - 1，当前写法会导致引用范围超出实际数据区域，虽不一定直接触发1004，但会造成无效引用。

修正后的代码 #

With ThisWorkbook.Sheets("Library")
    Dim x As Long, rng As Range
    Dim colName As String
    
    For x = 1 To .UsedRange.Columns.Count
        colName = .Cells(1, x).Value
        ' 跳过空表头
        If Trim(colName) = "" Then GoTo NextCol
        ' 清理非法字符，替换空格为下划线，移除特殊符号
        colName = Replace(colName, " ", "_")
        colName = Replace(colName, ":", "")
        colName = Replace(colName, "/", "")
        colName = Replace(colName, "\", "")
        ' 处理以数字开头的表头，添加前缀避免违规
        If IsNumeric(Left(colName, 1)) Then colName = "Col_" & colName
        ' 检查名称是否已存在，避免重复添加
        On Error Resume Next
        Dim existingName As Name
        Set existingName = ThisWorkbook.Names(colName)
        On Error GoTo 0
        If Not existingName Is Nothing Then
            existingName.RefersTo = .Cells(2, x).Resize(.UsedRange.Rows.Count - 1, 1)
            Set existingName = Nothing
            GoTo NextCol
        End If
        ' 设置正确的数据范围
        Set rng = .Cells(2, x).Resize(.UsedRange.Rows.Count - 1, 1)
        ' 添加合法名称
        ThisWorkbook.Names.Add Name:=colName, RefersTo:=rng
        Set rng = Nothing
NextCol:
    Next
End With

修正说明 #

• 增加空表头判断，直接跳过无效列；
• 对表头名称做非法字符清理，确保符合Excel命名规则；
• 处理以数字开头的表头，自动添加前缀避免违规；
• 检查名称是否已存在，存在则更新引用而非重复添加；
• 修正范围引用的行数计算，确保仅引用有效数据区域。

内容的提问来源于stack exchange，提问作者Jia Hannah