使用 VBA 退出 Excel 会导致运行时错误 424

Quitting Excel using VBA causes run-time error 424

提问by Gene

I have been writing a VBA macro that opens a HTML document within Excel (in order to perform various calculations on it). Excel will search for the HTML document within the current folder. If it can't find it there it will produce a file open box where the user can browse to the location of the HTML document manually. All good so far. However should the user select Cancel (rather than selecting a file), I want Excel to display a message and quit.

我一直在编写一个 VBA 宏，它可以在 Excel 中打开一个 HTML 文档（以便对其进行各种计算）。Excel 将在当前文件夹中搜索 HTML 文档。如果在那里找不到它，它将生成一个文件打开框，用户可以在其中手动浏览到 HTML 文档的位置。到目前为止一切都很好。但是，如果用户选择取消（而不是选择文件），我希望 Excel 显示一条消息并退出。

The message is produced but then the code stops with the following error:

该消息已生成，但随后代码停止并出现以下错误：

Run-time error '424': Object required.

运行时错误“424”：需要对象。

This doesn't sound like too much hassle, but I've been running into one brick wall after another trying to nail what is causing the problem.

这听起来并不太麻烦，但我一直在撞墙，试图找出导致问题的原因。

The sub that just doesn't seem to work is:

似乎不起作用的子程序是：

Sub ExitWithoutPrompt()

MsgBox "You failed to select a file, therefore Excel will now close.  Please refer to the readme file."
Excel.Application.DisplayAlerts = False
Excel.Application.Quit

End Sub

I'm using MS Excel 2002, but I'm keen for the solution to work on as many variants of Excel as possible.

我正在使用 MS Excel 2002，但我希望该解决方案能够处理尽可能多的 Excel 变体。

Any help gratefully received as to where I am going wrong. I'm a complete newbie by the way, so if at all possible please be long-winded with any guidance you might have for me...

任何有关我哪里出错的帮助都感激不尽。顺便说一下，我是一个完全的新手，所以如果可能的话，请冗长地提供您可能对我的任何指导......

As it might be of use included below (at the risk of making this post unwieldy) are the other two subs I am using in the macro:

因为它可能有用，所以我在宏中使用的另外两个 subs 包括在下面（冒着使这篇文章变得笨拙的风险）：

First sub:

第一个子：

Sub Endurance()

Call OpenHTML

Range("G27").Value = "Category"
Range("G28").Value = "Meat"
Range("G29").Value = "Veg"
Range("G30").Value = "PRP"
Range("F27").Value = "Fleet"
Range("E27").Value = "Consumption"

Range("E32").Value = "Endurance"

Range("E33").Value = "Lowest Category"
Range("E34").Value = "Fleet"
Range("E35").Value = "Consumption"

Range("E27, F27, G27, E32").Font.Bold = True
Range("F28").Value = WorksheetFunction.Sum(Range("E8,E9,E11,E14,E21"))
Range("E28").Value = WorksheetFunction.Sum(Range("G8,G9,G11,G14,G21"))
Range("F29").Value = WorksheetFunction.Sum(Range("E10,E16"))
Range("E29").Value = WorksheetFunction.Sum(Range("G10,G16"))
Range("F30").Value = WorksheetFunction.Sum(Range("E20,E22"))
Range("E30").Value = WorksheetFunction.Sum(Range("G20,G22"))

Columns("E:F").EntireColumn.AutoFit

Range("G28:G30, E27, F27, G27, G33").Select
    With Selection
        .HorizontalAlignment = xlRight
    End With

Range("E27:G30, E32:G35").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone


Dim Endurance As Double
Endurance = WorksheetFunction.Min(Range("F28:F30"))
Range("G34").Value = WorksheetFunction.RoundDown(Endurance, 0)

Endurance = WorksheetFunction.Min(Range("E28:E30"))
Range("G35").Value = WorksheetFunction.RoundDown(Endurance, 0)

Range("G33").Value = Endurance

Dim LowCat As String

LowCat = WorksheetFunction.VLookup(Endurance, Range("E28:G30"), 3, False)
Range("G33").Value = LowCat

ActiveSheet.PageSetup.PrintArea = "$A:$G"
ActiveSheet.PageSetup.Orientation = xlLandscape

Range("G36").Select

If MsgBox("Print endurance statement?", vbYesNo + vbDefaultButton2, "Print endurance") = vbYes Then
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Else
    Range("G36").Select
    End If


End Sub

And the second sub:

第二个子项：

Sub OpenHTML()

On Error GoTo MissingFile

Workbooks.Open FileName:=ThisWorkbook.Path & "\TRICAT Endurance Summary.html"


Exit Sub

MissingFile:

Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant

' Set up list of file filters
Finfo = "HTML Files (*.html),*.html," & _
        "All Files (*.*),*.*,"

' Display *.html by default
    FilterIndex = 1

' Set the dialog box caption
Title = "Select TRICAT Endurance Summary"

' Get the filename
FileName = Application.GetOpenFilename(FInfor, FilterIndex, Title)

' Handle Return info from dialog box
If FileName = False Then
    Call ExitWithoutPrompt
    Else
    MsgBox "You selected" & FileName
    Workbooks.Open FileName

End If

End Sub

If you've got this far, thanks for reading....

如果你已经到了这一步，感谢您的阅读......

回答by Patrick Cuff

Add a call to ActiveWorkbook.Closeto ExitWithoutPrompt:

呼叫加入ActiveWorkbook.Close到ExitWithoutPrompt：

Sub ExitWithoutPrompt()
    MsgBox "You failed to select a file, therefore Excel will now close.  Please refer to the readme file."
    Excel.Application.DisplayAlerts = False
    Excel.Application.Quit
    ActiveWorkbook.Close False
End Sub

This works for me under Excel 2003.

这在 Excel 2003 下对我有用。

For some reason, the order of calling Application.Quitand ActiveWorkbook.Closeis important. Counter-intuitively, at least to me, if you call ActiveWorkbook.Closebefore Application.Quityou still get the error.

出于某种原因，调用Application.Quitand的顺序ActiveWorkbook.Close很重要。与直觉相反，至少对我而言，如果您ActiveWorkbook.Close在Application.Quit仍然收到错误之前调用。