Tengo un rango dinámico con nombre que creé hace un par de meses con la ayuda de personas de aquí. Utiliza indirecto y funciona, pero cada vez que uso el mouse para seleccionar una celda, Excel vuelve a calcular y toma un minuto porque tengo algunas fórmulas complicadas de clasificación/filtro que creé para organizar algunas listas dinámicas en un orden específico que es necesario.
Este rango dinámico con nombre combinado con la clasificación/filtro es lo que creo que está causando este problema de lentitud.
Tengo la sospecha de que esto también está provocando que Excel falle si hago bucles que recalculan estas fórmulas y el rango con nombre en cada bucle.
La fórmula del rango dinámico con nombre es:
=INDIRECT("'"&'Loss Template'!$S$33&"'!"&"$A$1:$M"&COUNTA(INDIRECT("'"&'Loss Template'!$S$33&"'!"&"A:A")))
La fórmula indirecta esencialmente devuelve una tabla específica que reside en su propia hoja debido al tamaño de la tabla (300 filas por 12 columnas). Cambia a medida que cambia una celda de entrada especificada.
Las fórmulas de clasificación/filtro son todas similares con pequeñas diferencias de organización:
=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)
Estas fórmulas de clasificación/filtro hacen referencia a una "impresión" del rango dinámico con nombre que se encuentra en la 'Loss Template'hoja. Estaba pensando en posiblemente cambiar las referencias de las columnas a referencias dinámicas si eso ayuda para que no se revisen tantas filas, pero no estoy seguro de si eso solucionará parte del problema de lentitud o falla y no quería comprometerme si así fuera. No voy a arreglarlo.
Aquí está la macro que finalmente falla después de 10 a 15 iteraciones:
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
La Macro vuelve a calcular las cosas porque tengo el libro configurado en Manual todo el tiempo (debido al problema de recálculo). Las fórmulas están interconectadas y necesitan un par de cálculos para asegurarse de que las tablas se actualicen correctamente.
Otra cosa que me gustaría señalar es que el libro parece funcionar bien a pesar de la lentitud, pero como permanece abierto y hace algunos cálculos durante un tiempo, algunas de las fórmulas no se vuelven a calcular como deberían según el comando de cálculo que uso.
Una fórmula simple como ='Yearly Breakdown'!$B$2no se calcula con ws.Calculatecomando.
Cuando hago clic en la celda en la que se encuentra esta fórmula y hago clic en Intro, se calcula como debería, pero si hago clic en la hoja de cálculo de cálculo, no hace nada.
En pocas palabras, ¿cómo puedo evitar el bloqueo? No me importa el tiempo necesario, pero sobre todo el bloqueo.
Después de revisarlo, parece una pérdida de memoria.
Aquí está el código de error cuando falla:
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
ACTUALIZAR:
Otra hoja hace referencia a la tabla de rango con nombre indirecto para ordenar y filtrar los datos aún más.
Cada vez que vuelvo a calcular la tabla y las hojas conectadas después de abrir el archivo inicialmente. El uso de RAM va de 7 MB a 20,5 GB.
Supongo que es el gran uso de fórmulas indirectas en mi hoja, pero sea lo que sea, necesitamos reducir el uso de RAM para que la macro funcione.
Respuesta1
Para manejar errores inesperados en VBA, puede usarla ON ERRORdeclaración.
Esto hará que el código de manejo de errores detecte el error, donde podrá obtener información sobre el error, incluido el tipo de error y el número de línea donde se encontró el error.
En el manejo de errores más simple, puede mostrar la información del error en un cuadro de mensaje. Esto proporciona un punto de partida y, en ocasiones, puede ser todo lo que necesita para resolver el problema.
Para un manejo de errores más avanzado, puede verificar los valores y realizar una de varias acciones que haya determinado que son apropiadas para resolver el error y continuar con la ejecución.
Respuesta2
esto me ha resuelto algunos escenarios de fallas archivo> opciones> avanzado> [desplácese hacia abajo] deshabilite la aceleración del hardware de gráficos [marque esta casilla]