数か月前に、ここの人たちの助けを借りて作成した動的な名前付き範囲があります。間接的に使用していて機能しますが、マウスを使用してセルを選択するたびに、Excel が再計算し、必要な特定の順序で動的なリストを整理するために作成した複雑な並べ替え/フィルター式があるため、1 分ほどかかります。
この動的な名前付き範囲と並べ替え/フィルターの組み合わせが、この速度低下の問題を引き起こしていると考えられます。
これらの数式と名前付き範囲を各ループで再計算するループを実行すると、Excel がクラッシュする原因にもなるのではないかと疑っています。
動的名前付き範囲の式は次のとおりです。
=INDIRECT("'"&'Loss Template'!$S$33&"'!"&"$A$1:$M"&COUNTA(INDIRECT("'"&'Loss Template'!$S$33&"'!"&"A:A")))
間接的な数式は、基本的に、テーブルのサイズ (300 行 x 12 列) に応じて、独自のシート上に存在する特定のテーブルを返します。指定された入力セルが変更されると、テーブルも変更されます。
並べ替え/フィルターの式はすべて似ていますが、整理に関しては若干の違いがあります。
=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)
これらの並べ替え/フィルター式は、'Loss Template'シート上にある動的な名前付き範囲の「プリントアウト」を参照します。列参照を動的参照に変更して、参照される行の数を減らすことを考えていましたが、それが速度低下やクラッシュの問題の一部を修正するかどうかはわかりません。修正されない場合はコミットしたくありませんでした。
以下は、10 ～ 15 回の反復後に最終的にクラッシュするマクロです。
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
マクロは、ワークブックを常に手動に設定しているため (再計算の問題のため)、再計算を行います。数式は相互に関連しており、テーブルが正しく更新されるようにするには、いくつかの計算が必要です。
もう一つ注意したいのは、ワークブックは遅いにもかかわらず正常に動作しているように見えるものの、開いたまましばらく計算をいくつか実行すると、使用している計算コマンドどおりに数式の一部が再計算されないことです。
のような単純な数式はコマンド='Yearly Breakdown'!$B$2では計算されませんws.Calculate。
この数式が含まれているセルをクリックして Enter キーを押すと、期待通りに計算されますが、計算ワークシートをクリックしても何も起こりません。
要するに、クラッシュを防ぐにはどうしたらいいのでしょうか。所要時間の長さは気になりませんが、クラッシュは大抵気になります。
調べてみるとメモリリークのようです
クラッシュ時のエラーコードは次のとおりです:
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
アップデート：
間接的な名前付き範囲テーブルは別のシートによって参照され、データをさらに並べ替えたりフィルター処理したりします。
ファイルを最初に開いた後、テーブルと接続されたシートを再計算するたびに、Ram 使用量が 7 MB から 20.5 GB に増加します。
私のシートでは間接的な数式が多用されているのが原因だと推測していますが、いずれにせよ、マクロを機能させるには RAM の使用量を減らす必要があります。
答え1
VBAで予期しないエラーを処理するには、声明ON ERROR。
これにより、エラーがエラー処理コードによってキャッチされ、エラーの種類やエラーが発生した行番号などのエラーに関する情報を取得できるようになります。
最も単純なエラー処理では、メッセージ ボックスにエラー情報を表示するだけです。これが出発点となり、問題を解決するのに必要なすべてとなる場合があります。
より高度なエラー処理のために、値をチェックし、エラーを解決して実行を続行するのに適切であると判断したいくつかのアクションのいずれかを実行できます。
答え2
これで、クラッシュするいくつかのシナリオが解決しました。ファイル > オプション > 詳細設定 > [下にスクロール] グラフィック ハードウェア アクセラレーションを無効にする [このボックスにチェックを入れる]