切换工作表时VBA打印PDF代码执行失败问题求助

• 本人是VBA新手，已自行研究但未解决问题。
• 财务程序里的Print to PDF子程序，在'Sales Invoice'工作表保存正常，但切换到'Part Exchange'工作表时保存失败。
• 调试发现Part Exchange对应的保存路径消息框为空，疑似路径名或发票编号生成环节出错。
• 工作表B4单元格用=Cell("Filename")公式识别当前工作簿用户，但切换工作表时该公式有时不更新。
• 工作簿私有无法提供，仅能提供测试工作表截图，求除放弃外的技术建议。

相关代码 #

Sub PrintPDF()
   Call Save_PDF
End Sub

Function Save_PDF() As Boolean
    Dim PathName As String
    Dim UserName As String
    Dim Inv_Number As Integer
    Dim svas As String
'Find the user name from Active sheet filename
    UserName = Range("B4") 'Gets the Cell("Filename") from the active sheet
    MsgBox Range("B4")
    
' Choose File Save Name from 2 options of different user accounts
   If InStr(UserName, "C:\Users\Martyn") Then
       PathName = Range("B3")       'User Martyns path to either Sales Invoice folder or Purchase
                                    'Invoice folder, depending on the sheet
    Else
        PathName = Range("B2")      'User Adams path as above
    End If
    
     Inv_Number = Range("H8")       'Gets the Invoice Number
     svas = PathName & "\" & Inv_Number & ".pdf" 'Full path to save PDF Both sheets save paths are correct
       
    MsgBox Dir(svas) 'Shows what the save path is this is fine in Sales Invoice but blank in Part Exchange 
        
    If Dir(svas) <> vbNullString Then 'Checks if the File is existing THIS IS A PROBLEM ON PART EXCHANGE
        If MsgBox("There is already a file of that name, do you wish to replace it?", vbYesNo, "Warning") = vbNo Then GoTo EndMacro
    End If
    
    
' Show PDF
    On Error GoTo OpenPDFError 'Always defaults to this line and debug goes to the following line when saving PART ;
'EXCHANGE SHEET
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=svas, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
    On Error GoTo 0
    
Save_File:
    MsgBox "A copy of this sheet has been successfully saved as a  .pdf  file: " & vbCrLf & vbCrLf & svas & _
       vbCrLf & "Please Review the .pdf document. If the document does NOT look good, adjust your printing parameters, and try again."
        
    Save_PDF = True
    GoTo EndMacro
    
OpenPDFError:
    MsgBox "Unable to save, there maybe an existing copy of invoice on file or open." & vbCrLf & "If you wish to" & _
    "overwrite this invoice then, please delet existing copy or close your PDF reader and try again", , "WARNING"
    
    Save_PDF = False
    
    
EndMacro:
End Function

1. 强制刷新单元格公式：切换工作表时=Cell("Filename")可能未自动更新，在读取B4前添加代码强制刷新：

   ' 刷新当前工作表所有公式
   ActiveSheet.Calculate
   ' 或者仅刷新B4单元格
   Range("B4").Calculate
   

   放在UserName = Range("B4")之前，确保获取到最新的文件名路径。

2. 明确指定工作表对象：当前代码中Range未绑定工作表，默认用ActiveSheet可能出现上下文混乱，修改为：

   Dim ws As Worksheet
   Set ws = ActiveSheet
   UserName = ws.Range("B4").Value
   If InStr(UserName, "C:\Users\Martyn") Then
       PathName = ws.Range("B3").Value
   Else
       PathName = ws.Range("B2").Value
   End If
   Inv_Number = ws.Range("H8").Value
   

   避免因工作表切换导致的引用错误。

3. 提前检查变量有效性：生成保存路径前，添加代码确认路径和发票编号不为空：

   If PathName = "" Or Inv_Number = 0 Then
       MsgBox "路径或发票编号为空，请检查B2/B3和H8单元格内容", vbCritical
       Save_PDF = False
       GoTo EndMacro
   End If
   

   提前拦截无效值，避免后续操作出错。

4. 优化调试信息：将MsgBox Range("B4")替换为更详细的输出，定位问题节点：

   MsgBox "当前工作表：" & ActiveSheet.Name & vbCrLf & _
          "B4内容：" & Range("B4").Value & vbCrLf & _
          "路径：" & PathName & vbCrLf & _
          "发票号：" & Inv_Number
   

5. 检查路径是否存在：Dir函数在路径不存在时返回空，可先验证路径有效性：

   If Dir(PathName, vbDirectory) = "" Then
       MsgBox "保存路径不存在，请检查B2/B3单元格设置", vbCritical
       Save_PDF = False
       GoTo EndMacro
   End If
   

6. 修复错误提示拼写：将错误提示中的delet改为delete，避免误导用户。

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