Excel VBA运行时错误‘1004’：无法设置PageSetup类属性求助

你遇到的运行时错误1004是因为Excel的PageSetup对象依赖系统中的打印机驱动。当电脑未安装任何打印机（包括虚拟打印机）时，Excel无法获取打印设备的页面参数，导致无法设置页眉、页脚等PageSetup属性。

解决方案 #

• 安装虚拟打印机驱动（推荐）：
  直接安装Windows自带的「Microsoft Print to PDF」或「Microsoft XPS Document Writer」，系统默认自带无需额外下载。安装后Excel会自动识别该打印设备，PageSetup相关操作即可正常执行。

• 代码层面处理：
  如果无法安装打印机，可在代码中添加错误捕获逻辑，跳过PageSetup设置或提示用户：

  On Error Resume Next
  ' 尝试设置PageSetup属性
  oSheet.PageSetup.CenterHeader = "&""Calibri"" &B&6 " & cHeaderColour & cHeaderText
  oSheet.PageSetup.LeftFooter = "&""Calibri"" &6 " & "J.nr.:" & tJournal
  oSheet.PageSetup.CenterFooter = "&""Calibri"" &6 " & Format(Now, "d. mmmm yyyy")
  
  oDescription.PageSetup.CenterHeader = "&""Calibri"" &B&6 " & cHeaderColour & cHeaderText
  oDescription.PageSetup.LeftFooter = "&""Calibri"" &6 " & "J.nr.:" & tJournal
  oDescription.PageSetup.CenterFooter = "&""Calibri"" &6 " & Format(Now, "d. mmmm yyyy")
  On Error GoTo 0
  
  ' 可选：检查是否发生错误并提示
  If Err.Number = 1004 Then
      MsgBox "无法设置页面属性，请安装打印机驱动", vbExclamation
  End If
  

优化后的代码示例（减少冗余） #

原代码多次重复操作同一Range，用With语句合并属性设置可提升执行效率：

Dim oSheet As Worksheet
Dim oDescription As Worksheet

' 合并表头格式设置
With Range(oSheet.Cells(1, 1), oSheet.Cells(1, zIdxMediaContents))
    .Font.Name = "Calibri"
    .Orientation = 45
    .HorizontalAlignment = xlHAlignLeft
    .Font.Size = 6
    .Font.Color = 0
    .Font.Bold = True
End With

With Range(oSheet.Cells(1, 1), oSheet.Cells(1, zIdxMediaContents + 1))
    .VerticalAlignment = xlVAlignBottom
End With

With Range(oSheet.Cells(1, 1), oSheet.Cells(2, zIdxMediaContents + 1))
    .Interior.Color = &HFFFFFF
End With

Range(oSheet.Cells(2, 1), oSheet.Cells(2, zIdxMediaContents + 1)).Borders(xlEdgeBottom).Weight = xlThick

' PageSetup设置（建议配合错误捕获逻辑）
With oSheet.PageSetup
    .CenterHeader = "&""Calibri"" &B&6 " & cHeaderColour & cHeaderText
    .LeftFooter = "&""Calibri"" &6 " & "J.nr.:" & tJournal
    .CenterFooter = "&""Calibri"" &6 " & Format(Now, "d. mmmm yyyy")
End With

With oDescription.PageSetup
    .CenterHeader = "&""Calibri"" &B&6 " & cHeaderColour & cHeaderText
    .LeftFooter = "&""Calibri"" &6 " & "J.nr.:" & tJournal
    .CenterFooter = "&""Calibri"" &6 " & Format(Now, "d. mmmm yyyy")
End With

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