时间戳

首页 > 解决方案 > 大量的多页用户窗体，在初始化时导致 Excel 意外崩溃

excel - 大量的多页用户窗体，在初始化时导致 Excel 意外崩溃

问题描述

我有一个带有 MultiPage 控件的复杂用户窗体，其中有很多代码导致 Excel 崩溃。

不会引发异常，但在加载 UserForm Excel 时会关闭而不会出现任何错误。我以前没有遇到过这样的案例。是否有任何可能的修复或继续方法的想法？

我还注意到，如果我解锁 VBA，Excel 不会崩溃。出于安全原因，它被密码锁定。奇怪的。锁定它崩溃并解锁它做得很好。

初始化时我的代码时间低于 x 3。仅仅因为我有 3 页 = 标签。Excel VBA 是不是太多了？

代码示例：

Private Sub InitializeOptional()

'On Error Resume Next

' ComboBoxOPT

    Me.ComboBoxOPT4.List = ThisWorkbook.Sheets("Data").Range("J38:J39").Value
    Me.ComboBoxOPT5.List = ThisWorkbook.Sheets("Data").Range("J38:J39").Value
    Me.ComboBoxOPT6.List = ThisWorkbook.Sheets("Data").Range("J38:J39").Value

' TextBoxOPT

    Me.TextBoxOPT25.Text = ThisWorkbook.Worksheets("TableForOL").Range("B660").Value
    Me.TextBoxOPT23.Text = ThisWorkbook.Worksheets("TableForOL").Range("B1314").Value
    Me.TextBoxOPT24.Text = ThisWorkbook.Worksheets("TableForOL").Range("B1968").Value
    
    Me.TextBoxOPT32.Text = CalculationItemO3.Range("I1862").Value
    Me.TextBoxOPT26.Text = CalculationItemO2.Range("I1862").Value
    Me.TextBoxOPT1.Text = CalculationItemO1.Range("I1862").Value
    
    Me.TextBoxOPT16.Text = ThisWorkbook.Worksheets("Data").Range("M115").Value
    Me.TextBoxOPT27.Text = ThisWorkbook.Worksheets("Data").Range("M116").Value
    Me.TextBoxOPT33.Text = ThisWorkbook.Worksheets("Data").Range("M117").Value
    
    Me.TextBoxOPT18.Text = CalculationItemO1.Range("E2303").Value
    Me.TextBoxOPT28.Text = CalculationItemO2.Range("E2303").Value
    Me.TextBoxOPT34.Text = CalculationItemO3.Range("E2303").Value
    
    Me.TextBoxOPT19.Text = CalculationItemO1.Range("E2304").Value
    Me.TextBoxOPT29.Text = CalculationItemO2.Range("E2304").Value
    Me.TextBoxOPT35.Text = CalculationItemO3.Range("E2304").Value
    
    ' Option 1
    
    Me.TextBoxOPT20.Text = Format(CalculationItemO1.Range("E2310").Value, "###,##")
    Me.TextBoxOPT21.Text = CalculationItemO1.Range("F2309").Value
    
    ' Option 2
    
    Me.TextBoxOPT30.Text = Format(CalculationItemO2.Range("E2310").Value, "###,##")
    Me.TextBoxOPT31.Text = CalculationItemO2.Range("F2309").Value
    
    ' Option 3
    
    Me.TextBoxOPT36.Text = Format(CalculationItemO3.Range("E2310").Value, "###,##")
    Me.TextBoxOPT37.Text = CalculationItemO3.Range("F2309").Value
    
    Me.TextBoxOPT38.Text = CalculationItemO1.Range("G1835").Value
    Me.TextBoxOPT39.Text = CalculationItemO1.Range("G1834").Value
    Me.TextBoxOPT40.Text = CalculationItemO1.Range("G1833").Value
    Me.TextBoxOPT41.Text = CalculationItemO1.Range("G1838").Value
    Me.TextBoxOPT42.Text = CalculationItemO2.Range("G1835").Value
    Me.TextBoxOPT43.Text = CalculationItemO2.Range("G1834").Value
    Me.TextBoxOPT44.Text = CalculationItemO2.Range("G1833").Value
    Me.TextBoxOPT45.Text = CalculationItemO2.Range("G1838").Value
    Me.TextBoxOPT46.Text = CalculationItemO3.Range("G1835").Value
    Me.TextBoxOPT47.Text = CalculationItemO3.Range("G1834").Value
    Me.TextBoxOPT48.Text = CalculationItemO3.Range("G1833").Value
    Me.TextBoxOPT49.Text = CalculationItemO3.Range("G1838").Value

    
' Lebel

    ' Option 1

    Me.Controls("LabelOPT50").Caption = CalculationItemO1.Range("E2354").Value

    Me.Controls("LabelOPT122").Caption = Format(CalculationItemO1.Range("E2327").Value, "###,##")
    Me.Controls("LabelOPT116").Caption = Format(CalculationItemO1.Range("E2307").Value, "###,##")
    Me.Controls("LabelOPT114").Caption = Format(CalculationItemO1.Range("E2306").Value, "###,##")

    Me.Controls("LabelOPT121").Caption = CalculationItemO1.Range("E2346").Value
    Me.Controls("LabelOPT115").Caption = CalculationItemO1.Range("E2346").Value
    Me.Controls("LabelOPT101").Caption = CalculationItemO1.Range("E2346").Value

    Me.Controls("LabelOPT13").Caption = Format(CalculationItemO1.Range("E2328").Value, "###,##")
    Me.Controls("LabelOPT14").Caption = Format(CalculationItemO1.Range("E2352").Value, "###,##")

    Me.Controls("LabelOPT34").Caption = CalculationItemO1.Range("E2348").Value

'     Option 2

    Me.Controls("LabelOPT162").Caption = CalculationItemO2.Range("E2354").Value

    Me.Controls("LabelOPT207").Caption = Format(CalculationItemO2.Range("E2327").Value, "###,##")
    Me.Controls("LabelOPT201").Caption = Format(CalculationItemO2.Range("E2307").Value, "###,##")
    Me.Controls("LabelOPT199").Caption = Format(CalculationItemO2.Range("E2306").Value, "###,##")

    Me.Controls("LabelOPT206").Caption = CalculationItemO2.Range("E2346").Value
    Me.Controls("LabelOPT200").Caption = CalculationItemO2.Range("E2346").Value
    Me.Controls("LabelOPT187").Caption = CalculationItemO2.Range("E2346").Value

    Me.Controls("LabelOPT157").Caption = Format(CalculationItemO2.Range("E2328").Value, "###,##")
    Me.Controls("LabelOPT158").Caption = Format(CalculationItemO2.Range("E2352").Value, "###,##")

    Me.Controls("LabelOPT159").Caption = CalculationItemO1.Range("E23480").Value

    ' Option 3

    Me.Controls("LabelOPT225").Caption = CalculationItemO3.Range("E2354").Value

    Me.Controls("LabelOPT270").Caption = Format(CalculationItemO3.Range("E2327").Value, "###,##")
    Me.Controls("LabelOPT264").Caption = Format(CalculationItemO3.Range("E2307").Value, "###,##")
    Me.Controls("LabelOPT262").Caption = Format(CalculationItemO3.Range("E2306").Value, "###,##")

    Me.Controls("LabelOPT269").Caption = CalculationItemO3.Range("E2346").Value
    Me.Controls("LabelOPT263").Caption = CalculationItemO3.Range("E2346").Value
    Me.Controls("LabelOPT250").Caption = CalculationItemO3.Range("E2346").Value

    Me.Controls("LabelOPT220").Caption = Format(CalculationItemO3.Range("E2328").Value, "###,##")
    Me.Controls("LabelOPT221").Caption = Format(CalculationItemO3.Range("E2352").Value, "###,##")

    Me.Controls("LabelOPT222").Caption = CalculationItemO1.Range("E2348").Value

' CheckBoxOPT automation

    If ThisWorkbook.Worksheets("Data").Range("N91").Value = True Then
        Me.CheckBoxOPT2 = True
        Me.TextBoxOPT16.Visible = True
        Me.Controls("LabelOPT71").Visible = True
        Me.Controls("LabelOPT72").Visible = False
    Else
        Me.CheckBoxOPT2 = False
        Me.TextBoxOPT16.Visible = False
        Me.Controls("LabelOPT71").Visible = False
        Me.Controls("LabelOPT72").Visible = True
    End If

    If ThisWorkbook.Worksheets("Data").Range("N92").Value = True Then
        Me.CheckBoxOPT8 = True
        Me.TextBoxOPT27.Visible = True
        Me.Controls("LabelOPT167").Visible = True
        Me.Controls("LabelOPT168").Visible = False
    Else
        Me.CheckBoxOPT8 = False
        Me.TextBoxOPT27.Visible = False
        Me.Controls("LabelOPT167").Visible = False
        Me.Controls("LabelOPT168").Visible = True
    End If

    If ThisWorkbook.Worksheets("Data").Range("N93").Value = True Then
        Me.CheckBoxOPT9 = True
        Me.TextBoxOPT33.Visible = True
        Me.Controls("LabelOPT230").Visible = True
        Me.Controls("LabelOPT231").Visible = False
    Else
        Me.CheckBoxOPT9 = False
        Me.TextBoxOPT33.Visible = False
        Me.Controls("LabelOPT230").Visible = False
        Me.Controls("LabelOPT231").Visible = True
    End If

    If ThisWorkbook.Worksheets("Data").Range("M91").Value = True Then
        CheckBoxOPT7.Value = True
    Else
        CheckBoxOPT7.Value = False
    End If

    If ThisWorkbook.Worksheets("Data").Range("M92").Value = True Then
        CheckBoxOPT5.Value = True
    Else
        CheckBoxOPT5.Value = False
    End If

    If ThisWorkbook.Worksheets("Data").Range("M93").Value = True Then
        CheckBoxOPT6.Value = True
    Else
        CheckBoxOPT6.Value = False
    End If

    Me.ComboBoxOPT4.Value = ThisWorkbook.Worksheets("Data").Range("M104").Value
    Me.ComboBoxOPT5.Value = ThisWorkbook.Worksheets("Data").Range("M105").Value
    Me.ComboBoxOPT6.Value = ThisWorkbook.Worksheets("Data").Range("M106").Value

End Sub

我曾尝试仅在导航到某个页面时加载数据，但由于某些原因无法将数据加载到控件中？

Select Case MainMultiPageCard.SelectedItem.Index
    Case 0
        Call InitializeBasicInformation
    Case 1
        Call InitializeCommercial
    Case 2
        Call InitializeOptional
    Case Else
End Select

编辑：

我在初始化时运行了这个循环。删除它似乎有帮助：

Private Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean
Dim element As Variant
On Error GoTo IsInArrayError: 'array is empty
    For Each element In arr
        If element = valToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next element
Exit Function
IsInArrayError:
On Error GoTo 0
IsInArray = False
End Function

标签： excelvba

解决方案

推荐阅读