У меня есть динамический именованный диапазон, который я создал пару месяцев назад с помощью людей здесь. Он использует косвенный и работает, но каждый раз, когда я просто использую мышь, чтобы выбрать ячейку, Excel пересчитывает, и это занимает горячую минуту, потому что у меня есть некоторые сложные формулы сортировки/фильтрации, которые я создал, чтобы организовать некоторые динамические списки в определенном порядке, который необходим.
Я думаю, что именно этот динамический именованный диапазон в сочетании с сортировкой/фильтрацией и является причиной этой проблемы с медлительностью.
У меня есть подозрения, что это также приводит к сбою Excel, если я выполняю циклы, которые пересчитывают эти формулы и именованный диапазон в каждом цикле.
Формула динамического именованного диапазона имеет вид:
=INDIRECT("'"&'Loss Template'!$S$33&"'!"&"$A$1:$M"&COUNTA(INDIRECT("'"&'Loss Template'!$S$33&"'!"&"A:A")))
Косвенная формула по сути возвращает определенную таблицу, которая находится на своем собственном листе из-за размера таблицы (300 строк на 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
ОБНОВЛЯТЬ:
На таблицу косвенного именованного диапазона ссылается другой лист для дальнейшей сортировки и фильтрации данных.
Всякий раз, когда я пересчитываю таблицу и любые связанные листы после первоначального открытия файла, использование оперативной памяти увеличивается с 7 МБ до 20,5 ГБ.
Полагаю, это связано с большим использованием косвенных формул в моей таблице, но что бы это ни было, нам нужно уменьшить использование оперативной памяти, чтобы макрос заработал.
решение1
Для обработки непредвиденных ошибок в VBA вы можете использоватьзаявлениеON ERROR.
Это приведет к тому, что ошибка будет обнаружена вашим кодом обработки ошибок, где вы сможете получить информацию об ошибке, включая тип ошибки и номер строки, в которой она возникла.
В простейшей обработке ошибок вы можете просто отобразить информацию об ошибке в окне сообщений. Это дает отправную точку и иногда может быть всем, что вам нужно для решения проблемы.
Для более расширенной обработки ошибок вы можете проверить значения и выполнить одно из нескольких действий, которые вы определили как подходящие для устранения ошибки и продолжения выполнения.
решение2
это решило несколько сценариев сбоев для меня Файл > Параметры > Дополнительно > [прокрутить вниз] отключить аппаратное ускорение графики [установить этот флажок]