여기에 있는 사람들의 도움을 받아 몇 달 전에 만든 동적 이름 범위가 있습니다. 간접적으로 사용하고 작동하지만 마우스를 사용하여 셀을 선택할 때마다 Excel이 다시 계산되고 일부 동적 목록을 특정 순서로 구성하기 위해 만든 복잡한 정렬 기준/필터 수식이 있기 때문에 시간이 오래 걸립니다. 필수적이다.
정렬 기준/필터와 결합된 이 동적 명명된 범위가 이러한 속도 저하 문제의 원인이라고 생각합니다.
각 루프를 통해 이러한 수식과 명명된 범위를 다시 계산하는 루프를 수행하면 이로 인해 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 사용량은 7MB에서 20.5GB로 증가합니다.
나는 그것이 내 시트에서 간접 공식을 크게 사용하는 것이라고 추측하지만 그것이 무엇이든 매크로가 작동하려면 Ram 사용량을 줄여야 합니다.
답변1
VBA에서 예기치 않은 오류를 처리하려면 다음을 사용할 수 있습니다.성명서ON ERROR.
이렇게 하면 오류 처리 코드에 의해 오류가 포착되고, 여기서 오류 유형, 오류가 발생한 줄 번호 등 오류에 대한 정보를 얻을 수 있습니다.
가장 간단한 오류 처리에서는 메시지 상자에 오류 정보를 표시할 수 있습니다. 이는 출발점을 제공하며 때로는 문제를 해결하는 데 필요한 전부일 수도 있습니다.
고급 오류 처리를 위해 값을 확인하고 적절하다고 판단한 여러 작업 중 하나를 수행하여 오류를 해결하고 실행을 계속할 수 있습니다.
답변2
파일 > 옵션 > 고급 > [아래로 스크롤] 그래픽 하드웨어 가속 비활성화 [이 상자 선택]