Ich habe einen dynamischen benannten Bereich, den ich vor ein paar Monaten mit Hilfe von Leuten hier erstellt habe. Er verwendet indirekte Berechnungen und funktioniert, aber jedes Mal, wenn ich mit der Maus eine Zelle auswähle, berechnet Excel neu und es dauert eine ganze Minute, weil ich einige komplizierte Sortier-/Filterformeln erstellt habe, um einige dynamische Listen in einer bestimmten, erforderlichen Reihenfolge zu organisieren.
Ich denke, dass dieser dynamische benannte Bereich in Kombination mit den Sortier-/Filterfunktionen die Ursache für das Problem der Langsamkeit ist.
Ich habe den Verdacht, dass diese auch zum Absturz von Excel führen, wenn ich Schleifen ausführe, die diese Formeln und den benannten Bereich in jeder Schleife neu berechnen.
Die Formel für den dynamischen benannten Bereich lautet:
=INDIRECT("'"&'Loss Template'!$S$33&"'!"&"$A$1:$M"&COUNTA(INDIRECT("'"&'Loss Template'!$S$33&"'!"&"A:A")))
Die indirekte Formel gibt im Wesentlichen eine bestimmte Tabelle zurück, die sich aufgrund ihrer Größe (300 Zeilen mal 12 Spalten) auf einem eigenen Blatt befindet. Sie ändert sich, wenn sich eine angegebene Eingabezelle ändert.
Die Sortier-/Filterformeln sind alle ähnlich, weisen jedoch geringfügige Unterschiede hinsichtlich der Organisation auf:
=SORTBY(FILTER('Loss Template'!$E:$E, ('Loss Template'!$A:$A=TEXT($B$2,"0"))*('Loss Template'!$F:$F<=$J$7+1)*('Loss Template'!$F:$F>=$H$7)*('Loss Template'!$H:$H>2000)*('Loss Template'!$M:$M=5),""),FILTER('Loss Template'!$M:$M, ('Loss Template'!$A:$A=TEXT($B$2,"0"))*('Loss Template'!$F:$F<=$J$7+1)*('Loss Template'!$F:$F>=$H$7)*('Loss Template'!$H:$H>2000)*('Loss Template'!$M:$M=5),""),1,FILTER('Loss Template'!$H:$H, ('Loss Template'!$A:$A=TEXT($B$2,"0"))*('Loss Template'!$F:$F<=$J$7+1)*('Loss Template'!$F:$F>=$H$7)*('Loss Template'!$H:$H>2000)*('Loss Template'!$M:$M=5),""),1)
Diese Sortier-/Filterformeln verweisen auf einen „Ausdruck“ des dynamischen benannten Bereichs, der sich auf dem 'Loss Template'Blatt befindet. Ich habe darüber nachgedacht, die Spaltenverweise möglicherweise in dynamische Verweise zu ändern, wenn das hilft, sodass nicht so viele Zeilen betrachtet werden, aber ich bin nicht sicher, ob das einige der Langsamkeits- oder Absturzprobleme beheben wird, und wollte mich nicht darauf einlassen, wenn es das Problem nicht beheben würde.
Hier ist das Makro, das nach 10–15 Iterationen schließlich abstürzt:
Sub CalculateEmods()
Application.ScreenUpdating = False
Dim filename As String
Dim FolderName As String
Dim Folderstring As String
Dim FilePathName As String
Dim ws As Worksheet
Dim Calculator As Variant
Dim xprating As Variant
Dim emod As Range
Dim member As Range
Dim emodsws As Variant
Dim memberfound As Variant
Dim i As Integer
Dim RowCount As Integer
Dim NeededEmods As Range
Dim Report As Variant
Set Calculator = ThisWorkbook.Sheets(Array("Loss Template", "Codes", "Rating Data", "Yearly Breakdown", "Cover Sheet", "Ag Loss Sensitivity", "Experience Rating Sheet", "Loss Ratio Analysis", "Mod Analysis&Strategy Proposal", "Mod Snapshot", "Mod & Potential Savings"))
Set xprating = ThisWorkbook.Sheets("Experience Rating Sheet")
Set emod = ThisWorkbook.Sheets("Yearly Breakdown").Range("G334")
Set member = ThisWorkbook.Sheets("Yearly Breakdown").Range("B2")
Set emodsws = ThisWorkbook.Sheets("2020Emods")
Set NeededEmods = emodsws.Range("A2", Range("A2").End(xlDown))
Set memberfound = NeededEmods.Find(member)
FolderName = "EmodFolder"
RowCount = NeededEmods.Rows.Count + 1
Report = Array("Cover Sheet", "Ag Loss Sensitivity", "Experience Rating Sheet", "Loss Ratio Analysis", "Mod Analysis&Strategy Proposal", "Mod Snapshot", "Mod & Potential Savings")
For i = 2 To RowCount
Application.EnableEvents = False
member.Value2 = emodsws.Range("A" & i).Value2
'Updates Report for newly entered member
For Each ws In Calculator
ws.Calculate
Next ws
For Each ws In Calculator
ws.Calculate
ws.PageSetup.RightFooter = Sheet17.Range("B3").Text & Chr(10) & "Mod Effective
Date: " & Sheet17.Range("B4")
Next ws
Application.EnableEvents = True
xprating.Calculate
'Copies emod and pastes it to Emod Worksheet
emodsws.Cells(i, 4).Value2 = emod.Value2
'Prints Emod Report for member as PDF
filename = ActiveWorkbook.Sheets("Cover Sheet").Range("B20") & "_Emod" & ".pdf"
Folderstring = CreateFolderinMacOffice2016(NameFolder:=FolderName)
FilePathName = Folderstring & Application.PathSeparator & filename
ThisWorkbook.Sheets(Report).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
FilePathName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False
emodsws.Select
Next i
Application.ScreenUpdating = True
MsgBox "Emod Report Updated!"
End Sub
Das Makro berechnet die Dinge neu, da ich die Arbeitsmappe immer auf Manuell eingestellt habe (aufgrund des Neuberechnungsproblems). Die Formeln sind miteinander verbunden und erfordern einige Berechnungen, um sicherzustellen, dass die Tabellen korrekt aktualisiert werden.
Außerdem möchte ich darauf hinweisen, dass die Arbeitsmappe trotz der Langsamkeit einwandfrei zu funktionieren scheint. Da sie jedoch geöffnet bleibt und eine Zeit lang einige Berechnungen durchführt, werden einige Formeln nicht wie vorgesehen mit dem von mir verwendeten Berechnungsbefehl neu berechnet.
Eine einfache Formel wie diese ='Yearly Breakdown'!$B$2rechnet nicht mit ws.Calculatedem Befehl.
Wenn ich auf die Zelle mit der Formel klicke und die Eingabetaste drücke, läuft die Berechnung wie vorgesehen. Wenn ich jedoch auf das Berechnungsarbeitsblatt klicke, geschieht nichts.
Fazit: Wie kann ich Abstürze verhindern? Die benötigte Zeit stört mich nicht, aber hauptsächlich die Abstürze.
Nach der Überprüfung sieht es aus wie ein Speicherleck
Hier ist der Fehlercode, wenn es abstürzt:
Microsoft Error Reporting log version: 2.0
Error Signature:
Exception: EXC_BAD_ACCESS
ExceptionEnumString: 1
Exception Code: KERN_INVALID_ADDRESS (0x0000000000000000)
Date/Time: 2020-07-15 16:24:02 +0000
Application Name: Microsoft Excel
Application Bundle ID: com.microsoft.Excel
Application Signature: XCEL
Application Bitness: x64
Application Version: 16.36.0.20041300
Crashed Module Name: WLMGraphicsDevice
Crashed Module Version: 16.36.0.200413
Crashed Module Offset: 0x00000000000048ac
Blame Module Name: WLMGraphicsDevice
Blame Module Version: 16.36.0.200413
UnsymbolicatedChecksum: 7F136B3BB3D9137C72F75133AE7A2115
Blame Module Offset: 0x00000000000048ac
StackHash: 3458086775a1e3cc-dm_1_main
Application LCID: 1033
Extra app info: Reg=en Loc=0x0409
Build Type: Release
Crashed thread Stack Pointer: 0x_00007ffee1f2ce80
Crashed thread: 0
AKTUALISIEREN:
Auf die indirekt benannte Bereichstabelle wird von einem anderen Blatt verwiesen, um die Daten noch weiter zu sortieren und zu filtern.
Immer wenn ich die Tabelle und alle verbundenen Blätter nach dem ersten Öffnen der Datei neu berechne, steigt die RAM-Nutzung von 7 MB auf 20,5 GB.
Ich vermute, es liegt an der häufigen Verwendung indirekter Formeln in meinem Tabellenblatt, aber was auch immer es ist, wir müssen die RAM-Nutzung reduzieren, damit das Makro funktioniert.
Antwort1
Um unerwartete Fehler in VBA zu behandeln, können Sie verwendendie ON ERRORAussage.
Dadurch wird der Fehler von Ihrem Fehlerbehandlungscode abgefangen und Sie erhalten Informationen zum Fehler, einschließlich der Art des Fehlers und der Zeilennummer, in der der Fehler aufgetreten ist.
Bei der einfachsten Fehlerbehandlung können Sie die Fehlerinformationen einfach in einem Meldungsfeld anzeigen. Dies bietet einen Ausgangspunkt und ist manchmal alles, was Sie zur Lösung des Problems benötigen.
Zur erweiterten Fehlerbehandlung können Sie Werte prüfen und eine von mehreren Aktionen ausführen, die Ihrer Einschätzung nach geeignet sind, um den Fehler zu beheben und die Ausführung fortzusetzen.
Antwort2
dies hat bei mir einige Absturzszenarien gelöst: Datei > Optionen > Erweitert > [nach unten scrollen] Grafikhardwarebeschleunigung deaktivieren [dieses Kontrollkästchen aktivieren]