VBA提示“Sub或Function未定义”报错排查求助

问题分析 #

你遇到的“Sub或Function未定义”错误，核心原因是代码使用了VBA不支持的函数，同时存在几处逻辑和语法问题：

• VBA没有内置StringFormat函数，这是VB.NET专属函数，直接调用会触发未定义错误；
• 用FileSystemObject.GetFile获取的是文件系统对象，不是Excel工作簿，无法直接访问Worksheets属性；
• 变量objectFlieSys存在拼写错误（应为objectFileSys）；
• 错误处理逻辑不完整，变量声明位置不规范。

修正后的完整代码 #

Sub ExtractDataToDifferentSheets()
    On Error GoTo HandleError
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim rowNumber As Long ' 改用Long避免行数超出Integer上限
    Dim dRow As Long
    Dim NG As String, Lot As String
    Dim sourceWs As Worksheet
    Dim targetWb As Workbook
    Dim filePath As String
    
    ' 固定引用源工作表，避免ActiveSheet变动导致错误
    Set sourceWs = ThisWorkbook.Worksheets("Sheet1")
    rowNumber = sourceWs.UsedRange.Rows.Count
    
    For dRow = 2 To rowNumber
        NG = sourceWs.Cells(dRow, 1).Value
        Lot = sourceWs.Cells(dRow, 2).Value
        
        ' VBA中用&拼接字符串替代StringFormat
        filePath = "C:\Users\mmccarthy\Box\QC-QA\SOPS Quality System\Quality logs\Ingredient Release Forms Records\2022 INGREDIENT RELEASE FORM\" & NG & "_" & Lot & ".xlsx"
        
        ' 检查文件是否存在
        If Dir(filePath) <> "" Then
            ' 打开目标工作簿，设置为只读避免锁定
            Set targetWb = Workbooks.Open(Filename:=filePath, ReadOnly:=True)
            ' 读取指定单元格数据到源工作表
            sourceWs.Cells(dRow, 7).Value = targetWb.Worksheets("Sheet1").Cells(7, 7).Value
            ' 关闭工作簿不保存
            targetWb.Close SaveChanges:=False
            Set targetWb = Nothing
        Else
            ' 文件不存在时标记提示
            sourceWs.Cells(dRow, 7).Value = "文件不存在"
        End If
    Next dRow

HandleError:
    ' 恢复Excel设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    ' 捕获并提示错误
    If Err.Number <> 0 Then
        MsgBox "错误代码: " & Err.Number & vbCrLf & "错误描述: " & Err.Description, vbCritical
    End If
End Sub

关键修改说明 #

• 替换StringFormat：用VBA原生的&运算符拼接文件路径，替代不支持的StringFormat函数；
• 正确访问工作簿：用Workbooks.Open打开目标文件，获取合法的工作簿对象后再访问工作表；
• 添加文件检查：用Dir函数判断文件是否存在，避免因文件缺失触发错误；
• 变量优化：将Integer改为Long，避免行数超过32767时溢出；固定引用ThisWorkbook的工作表，避免ActiveSheet切换导致的错误；
• 完善错误处理：恢复Excel的屏幕更新和事件触发，同时捕获错误并提示具体信息；
• 简化逻辑：移除了不必要的FileSystemObject，直接用Dir和Workbooks.Open实现需求，更简洁高效。

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