关闭工作簿时，子例程意外结束 [英] Subroutine unexpectedly ends when a Workbook is closed

查看：57
本文介绍了关闭工作簿时，子例程意外结束的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我今天的问题是一个子例程的一部分，当 Workbook 关闭时，该子例程会莫名其妙地中断其执行.
我已经编写了以下代码:

my problem today is a part of a subroutine that inexplicably breaks its execution when a Workbook is closed.
I have written the following code: 

Public Const Pi As Double = 3.14159265358979
Public Const Rad As Double = Pi / 180 
Public CalcBook As Workbook
Public FilePath As String, Files() As String
Public FreqArray() As Integer

Sub Main()

Dim ChooseFolder As Object, FilePath As String, StrFile As String
Dim i As Integer, j As Integer, k As Integer, x As Integer
Dim DirNum As Integer, HNum As Integer, VNum As Integer
Dim DirColShift As Integer, HColShift As Integer, VColShift As Integer
Dim TheStart As Date, TheEnd As Date, TotalTime As Date

Set ChooseFolder = Application.FileDialog(msoFileDialogFolderPicker)

With ChooseFolder
    .AllowMultiSelect = False
    .Title = "Please choose a folder containing .txt files"
    If .Show = -1 Then
        FilePath = .SelectedItems(1) & "\"
    Else
        Set ChooseFolder = Nothing
        Exit Sub
    End If
End With
Set ChooseFolder = Nothing

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False

' Stores only files containing an AntennaName + "_T" + any number of characters + "_?_?45.xls" string
' (where "?" is a single character and "*" is any number). Checks if the number of files is correct too.

StrFile = Dir(FilePath & "*_T*_?_?45.txt")
Do While Len(StrFile) > 0
    ReDim Preserve Files(i)
    Files(i) = FilePath & StrFile
    i = i + 1
    StrFile = Dir
Loop


If Not (UBound(Files) + 1) / 6 = Int((UBound(Files) + 1) / 6) Then GoTo FileError
For i = 0 To UBound(Files)
    Select Case Right(Files(i), 9)
    Case "D_+45.txt", "D_-45.txt"
        DirNum = DirNum + 1
    Case "H_+45.txt", "H_-45.txt"
        HNum = HNum + 1
    Case "V_+45.txt", "V_-45.txt"
        VNum = VNum + 1
    End Select
Next
If Not (DirNum / 2 = Int(DirNum / 2) And HNum / 2 = Int(HNum / 2) And VNum / 2 = Int(VNum / 2) And DirNum = HNum And HNum = VNum) Then
FileError:
    MsgBox "Failed to properly load files. Looks like a wrong number of them is at dispose", vbCritical, "Check the import-files"
    Exit Sub
End If

' Imports files in Excel for better data access

Set CalcBook = Application.Workbooks.Add

' FROM HERE ON THE DATA IS PROCESSED IN ORDER TO OBTAIN AN EXCEL WORKBOOK WITH 3 SHEETS CALLED "Directivity", "Horizontal" and "Vertical".

Application.ScreenUpdating = True
Options.Show

TheStart = Now

Application.ScreenUpdating = False
If Options.OnlyEval = False Then PolarCharts
If Options.OnlyCharts = False Then Auswertung
Application.DisplayAlerts = False
CalcBook.Close savechanges:=False
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Set CalcBook = Nothing

TheEnd = Now
TotalTime = TheEnd - TheStart
MsgBox Format(TotalTime, "HH:MM:SS"), vbInformation, "Computing Time"

Unload Options

End Sub

选项是我需要的一种形式，用于访问 PolarCharts 和 Auswertung 的数据.这些Subs正确执行(我知道，因为它们保存的数据也是正确的).

Options is a form which I need in order to access data for the PolarCharts and Auswertung. These Subs are correctly executed (I know that because the data they save is correct too).

我试图删除 .ScreenUpdating 和 .DisplayAlerts 命令以及 Unload 命令，以为它们可能会给某些东西带来麻烦，但是结果没变

I tried deleting the .ScreenUpdating and .DisplayAlerts commands, as well as the Unload thinking that they could bugging something, but the result hasn't changed.

也要知道我要关闭的工作簿根本不包含任何代码(并且没有其他地址指向".Close"，因此不可能在.Close事件上执行任何操作).

Know also that the Workbook I'm closing contains NO CODE at all (and nothing else addresses a ".Close" so it's impossible that something is executed on the .Close event).

在我的选项"代码下方:

Below my "Options" code:

Private Sub Cancel_Click()
    End
End Sub

Private Sub UserForm_Terminate()
    End
End Sub

Private Sub Ok_Click()

    If Me.OnlyCharts = False Then

        ReDim SubFreq(4)

        If Not (Me.Start1.ListIndex = -1 And Me.Stop1.ListIndex = -1) Then SubFreq(0) = Me.Start1.List(Me.Start1.ListIndex) & "-" & Me.Stop1.List(Me.Stop1.ListIndex)
        If Not (Me.Start2.ListIndex = -1 And Me.Stop2.ListIndex = -1) Then SubFreq(1) = Me.Start2.List(Me.Start2.ListIndex) & "-" & Me.Stop2.List(Me.Stop2.ListIndex)
        If Not (Me.Start3.ListIndex = -1 And Me.Stop3.ListIndex = -1) Then SubFreq(2) = Me.Start3.List(Me.Start3.ListIndex) & "-" & Me.Stop3.List(Me.Stop3.ListIndex)
        If Not (Me.Start4.ListIndex = -1 And Me.Stop4.ListIndex = -1) Then SubFreq(3) = Me.Start4.List(Me.Start4.ListIndex) & "-" & Me.Stop4.List(Me.Stop4.ListIndex)
        If Not (Me.Start5.ListIndex = -1 And Me.Stop5.ListIndex = -1) Then SubFreq(4) = Me.Start5.List(Me.Start5.ListIndex) & "-" & Me.Stop5.List(Me.Stop5.ListIndex)

        If (Me.Start1 = "" And Me.Start2 = "" And Me.Start3 = "" And Me.Start4 = "" And Me.Start5 = "" And Me.Stop1 = "" And Me.Stop2 = "" And Me.Stop3 = "" And Me.Stop4 = "" And Me.Stop5 = "") _
        Or Me.Start1.Value > Me.Stop1.Value Or Me.Start2.Value > Me.Stop2.Value Or Me.Start3.Value > Me.Stop3.Value Or Me.Start4.Value > Me.Stop4.Value Or Me.Start5.Value > Me.Stop5.Value _
        Or (Me.Start1.ListIndex = -1 And Me.Stop1.ListIndex >= 0) Or (Me.Start2.ListIndex = -1 And Me.Stop2.ListIndex >= 0) Or (Me.Start3.ListIndex = -1 And Me.Stop3.ListIndex >= 0) Or (Me.Start4.ListIndex = -1 And Me.Stop4.ListIndex >= 0) Or (Me.Start5.ListIndex = -1 And Me.Stop5.ListIndex >= 0) _
        Or (Me.Start1.ListIndex >= 0 And Me.Stop1.ListIndex = -1) Or (Me.Start2.ListIndex >= 0 And Me.Stop2.ListIndex = -1) Or (Me.Start3.ListIndex >= 0 And Me.Stop3.ListIndex = -1) Or (Me.Start4.ListIndex >= 0 And Me.Stop4.ListIndex = -1) Or (Me.Start5.ListIndex >= 0 And Me.Stop5.ListIndex = -1) Then
            MsgBox("Please select correctly the frequency ranges - Maybe Start > Stop, one of those was not properly inserted, or the fields are blank", vbExclamation, "Frequency choice error")
            GoTo hell
        End If

        For i = 0 To 4
            If Not SubFreq(i) = "" Then j = j + 1
        Next i
        j = j - 1
        ReDim Preserve SubFreq(j)

    End If

    Me.Hide

hell:
End Sub

Private Sub UserForm_Initialize()

Dim i As Byte

    Me.StartMeas = Date
    Me.StopMeas = Date

    Me.Worker.AddItem "lol"
    Me.Worker.AddItem "rofl"
    Me.Worker.ListIndex = 0

    For i = LBound(FreqArray) To UBound(FreqArray)
        Me.Start1.AddItem FreqArray(i)
        Me.Start2.AddItem FreqArray(i)
        Me.Start3.AddItem FreqArray(i)
        Me.Start4.AddItem FreqArray(i)
        Me.Start5.AddItem FreqArray(i)
        Me.Stop1.AddItem FreqArray(i)
        Me.Stop2.AddItem FreqArray(i)
        Me.Stop3.AddItem FreqArray(i)
        Me.Stop4.AddItem FreqArray(i)
        Me.Stop5.AddItem FreqArray(i)
    Next i

    Me.Start1.ListIndex = 0
    Me.Stop1.ListIndex = Me.Stop1.ListCount - 1

End Sub

显然，当我 Close CalcBook时，它会触发Options中的 UserForm_Terminate 事件，该事件会 End 包含所有代码！如何避免这种情况?

Apparently when I Close CalcBook, it triggers the UserForm_Terminate event from Options which Ends all the code! How do I avoid this?

推荐答案

只需删除语句 End ，因为 End 会导致突然结束代码执行.

Just remove the statement End bacause End causes the abrupt end of code execution.

我在 Cancel 和 Terminate 事件处理程序中看到了 End .如果您将其放在其他地方，请将其移除.

I see End in the Cancel and Terminate event handlers. If you have it on other places, remove it es well.

如果您需要退出方法，请使用 Exit Sub .

If you need exit from a method then use Exit Sub.

为什么:因为 End 可以那样工作.阅读例如这篇文章:.

Why: because End work that way. Read e.g. this post: http://www.vbforums.com/showthread.php?511766-Classic-VB-Why-is-using-the-End-statement-(or-VB-s-quot-stop-quot-button)-a-bad-idea.

如果您需要停止执行代码，请使用 If-condition 甚至是 Exit Sub ，但请避免使用 End .

If you need stop code from execution use If-condition or even Exit Sub but avoid using End for it.

这篇关于关闭工作簿时，子例程意外结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆