Código de descarga de datos VBA utilizando la duplicación de Transpose y Timeline -- performance campo con vba camp codereview Relacionados El problema

VBA Data dump code using transpose and timeline duplication


1
vote

problema

Español

El siguiente código se ejecutó en 2:26, ​​lo que es demasiado lento, especialmente, ya que solo es 1 / 3º del total (el 3 / 3rd restante hace algo muy similar solo en secciones diferentes). ¿Hay algo que pueda hacer para acelerar esto? Rangecount = 625 y timelinecount = 156, que obviamente son grandes números (total es de alrededor de 100k filas), pero cuando Rangecount fue 200 (filas de 30k), el código corrió en 3 segundos ... no estoy seguro de por qué se ha ido tan lento ahora.

enlace a muestra: https://dive.google .com / file / d / 1qhhhtwad6_giwetzcakw8-qaq7y6j4nx / view? USP = Sharing

Editar: Creo que he descubierto el problema: la tabla en los datos de Dash fue una tabla de Excel. Lo cambié de ser una mesa de Excel a las células normales y corrió todo el código en 4 segundos. ¿Puede alguien explicarme por qué la mesa es tan lenta o cómo podría mejorar la velocidad de él, por favor?

  Sub copy_to_dash_data()  Dim i, j As Integer Dim wsCalcs As Worksheet Dim wsDashData As Worksheet Dim LastRow As Long Dim LastCol As Long Dim FirstRow As Long Dim RangeCount As Long Dim TimelineCount As Long Dim LastCell As Long Dim PasteCell As Long Dim InvoiceDetailsColumnCount As Long Dim NamedRange As Range Dim StartTime As Double Dim TimeElapsed As String  'timer calculation  StartTime = Timer   'set calcs to manual, disable user events and disable screen updating  Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual   'setup sheet names for easy reference  Set wsCalcs = ThisWorkbook.Sheets("Calcs") Set wsDashData = ThisWorkbook.Sheets("Dash Data")   'find last row and column of Dash Data and clear contents  LastRow = wsDashData.Cells(Rows.Count, 1).End(xlUp).Row LastCol = Application.Match("P&L Amount", wsDashData.Rows(1), 0)  wsDashData.Range(Cells(2, 1).Address, Cells(LastRow, LastCol).Address).ClearContents   'count the number of rows in the first Calc block and the timeline block  FirstRow = wsCalcs.Range(Names("Invoice_Details")).Row LastRow = wsCalcs.Range(Names("Invoice_Details")).Rows.Count + FirstRow - 1 RangeCount = wsCalcs.Range(Names("Invoice_Details")).Rows.Count - 1  TimelineCount = wsCalcs.Range(Names("Months_and_Years_Timeline")).Columns.Count - 1   'month start, month end and year rows from Calcs values transfer to Dash Data a number of times equal to the number of rows in the Calc block (e.g. number of invoices)  PasteCell = Application.Match("Month Start", wsDashData.Rows(1), 0)  For i = 1 To RangeCount     wsDashData.Range(Cells(2 + (TimelineCount * (i - 1)), PasteCell).Address, Cells(1 + (TimelineCount * i), PasteCell + 2).Address) = _         Application.Transpose(wsCalcs.Range(Names("Months_and_Years_Timeline"))) Next i   'enable events and screen updating  Application.ScreenUpdating = True Application.EnableEvents = True Calculate  'code complete!  TimeElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss") MsgBox ("Code complete in " & TimeElapsed), vbInformation  End Sub   
Original en ingles

The below code ran in 2:26 which is way too slow especially as it's only 1/3rd of the total (the remaining 2/3rd does something very similar just on different sections). Is there something I can do to speed this up? RangeCount = 625 and TimelineCount = 156 which are obviously large numbers (total is around 100k rows) but when RangeCount was 200 (30k rows) the code ran in 3 seconds...not sure why it's gone so slow now.

Link to sample: https://drive.google.com/file/d/1QHHtWad6_giWETZcAKw8-Qaq7Y6JJ4NX/view?usp=sharing

Edit: I believe I have discovered the issue - the table in Dash Data was an Excel table. I changed it from being an Excel table to just normal cells and it ran the whole code in 4 seconds. Can someone explain to me why the table is so slow or how I could improve the speed of it, please??

Sub copy_to_dash_data()  Dim i, j As Integer Dim wsCalcs As Worksheet Dim wsDashData As Worksheet Dim LastRow As Long Dim LastCol As Long Dim FirstRow As Long Dim RangeCount As Long Dim TimelineCount As Long Dim LastCell As Long Dim PasteCell As Long Dim InvoiceDetailsColumnCount As Long Dim NamedRange As Range Dim StartTime As Double Dim TimeElapsed As String  'timer calculation  StartTime = Timer   'set calcs to manual, disable user events and disable screen updating  Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual   'setup sheet names for easy reference  Set wsCalcs = ThisWorkbook.Sheets("Calcs") Set wsDashData = ThisWorkbook.Sheets("Dash Data")   'find last row and column of Dash Data and clear contents  LastRow = wsDashData.Cells(Rows.Count, 1).End(xlUp).Row LastCol = Application.Match("P&L Amount", wsDashData.Rows(1), 0)  wsDashData.Range(Cells(2, 1).Address, Cells(LastRow, LastCol).Address).ClearContents   'count the number of rows in the first Calc block and the timeline block  FirstRow = wsCalcs.Range(Names("Invoice_Details")).Row LastRow = wsCalcs.Range(Names("Invoice_Details")).Rows.Count + FirstRow - 1 RangeCount = wsCalcs.Range(Names("Invoice_Details")).Rows.Count - 1  TimelineCount = wsCalcs.Range(Names("Months_and_Years_Timeline")).Columns.Count - 1   'month start, month end and year rows from Calcs values transfer to Dash Data a number of times equal to the number of rows in the Calc block (e.g. number of invoices)  PasteCell = Application.Match("Month Start", wsDashData.Rows(1), 0)  For i = 1 To RangeCount     wsDashData.Range(Cells(2 + (TimelineCount * (i - 1)), PasteCell).Address, Cells(1 + (TimelineCount * i), PasteCell + 2).Address) = _         Application.Transpose(wsCalcs.Range(Names("Months_and_Years_Timeline"))) Next i   'enable events and screen updating  Application.ScreenUpdating = True Application.EnableEvents = True Calculate  'code complete!  TimeElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss") MsgBox ("Code complete in " & TimeElapsed), vbInformation  End Sub 
     
         
         

Lista de respuestas


Relacionados problema

6  Juego de serpientes - Dentro de la hoja de trabajo - Celdas como píxeles  ( Snake game within worksheet cells as pixels ) 
Desde mi intento bastante mediocre de hacer un juego de invasores del espacio, me topé con un caché de juegos de Visual Basic para solicitar juegos escritos p...

7  Recuperando datos de archivos en la carpeta  ( Retrieving data from files in the folder ) 
Estoy a usar el código VBA para simplemente recuperar ciertos datos de todos los archivos de Excel en cierta carpeta y pegarlo en la hoja de cálculo de trabaj...

2  Importando datos en Excel  ( Importing data into excel ) 
¿Existe una forma más fácil de importar datos en una matriz de Excel u otra estructura de datos? He intentado investigar colecciones, pero he encontrado la D...

6  Fijación de archivos de reclamación médica a través del archivo de texto Lea / escribe  ( Fixing medical claim files through text file read write ) 
Con Gracias a @ Mat'Smug y @comintern por su aliento, aquí hay un programa que escribí para ayudar a mi equipo a reparar archivos de reclamación médica. El ...

6  Crear tabla que enumera la visibilidad de la hoja de trabajo  ( Create table that lists worksheet visibility ) 
Siguiendo mi pregunta anterior crear Una tabla que enumera las macros en un libro de trabajo o hoja de trabajo Aquí está mi Sub para determinar la visibilid...

2  Búsqueda de pruebas de abstracción y unidad en mesa Excel  ( Abstracting and unit testing lookups in excel table ) 
Fondo Tengo una solución de VBA que utilizo para ingerir informes de texto de inversión y reformatearlos para su análisis en Excel. Funciona, pero las macro...

5  Copiando datos de libros cerrados  ( Copying data from closed workbooks ) 
Soy un pasante en una empresa industrial en Brasil y sucede que estoy usando mucho en Excel. Acabo de empezar a jugar con VBA hace un par de días, ¡y me divie...

3  Comparando células de dos hojas de trabajo  ( Comparing cells of two worksheets ) 
Con el siguiente código, trato de comparar el contenido de dos hojas de trabajo. La primera columna contiene una clave que es única y las siguientes columnas ...

10  Imitación básica de C # enumerable en VBA - o cualquier otra clase estática  ( Basic imitation of c enumerable in vba or any other static class ) 
Desde la implementación de clases estáticas en VBA en realidad parece posible Se me ocurrió una idea para tratar de imitar C # ' 9988776655544330 clase ...

48  Lista <T> Implementación para VB6 / VBA  ( Listt implementation for vb6 vba ) 
Recientemente, decidí que el 998877665555544330 no fue suficiente para mis necesidades, así que decidí implementar algo como C # 's List<T> . Aquí está la ...




© 2022 respuesta.top Reservados todos los derechos. Centro de preguntas y respuestas reservados todos los derechos