Error13 en Excel VBA en archivo de datos

excel-vba

Question

Estoy ejecutando el código VBA a continuación en el siguiente archivo:

http://www.filedropper.com/error13

Me sale un error 13 No coinciden los tipos.

Aquí está la macro

Agregar, clasificar y transponer filas en columnas

Funciona bien cuando selecciono algunas de las filas (por ejemplo, id 1001 o 1003 y 1004 juntas, pero cuando trato de procesar más filas me sale el error 13.

Estoy tratando de procesar cada identificación a la vez, pero tengo alrededor de 100 ...

Answer 1

En aras de la curiosidad y el ejercicio intelectual, tomé una puñalada en el método de procesamiento de matriz presentado originalmente por ZygD en la pregunta original con la ZygD de superar el error de tiempo de ejecución ''13'': error de desajuste de tipo en conjuntos de datos más grandes.

La instrucción ReDim Preserve solo puede redimensionar el último rango mientras conserva los valores existentes ya almacenados, sujeto al hecho de que está aumentando el tamaño de la dimensión de la matriz y no la reduce. Esto es lo que msdn.microsoft.com tiene para decir sobre el tema:

Redimensionando con Preserve . Si usa Conservar, solo puede cambiar el tamaño de la última dimensión de la matriz. Para cada otra dimensión, debe especificar el límite de la matriz existente.
Por ejemplo, si su matriz tiene solo una dimensión, puede cambiar el tamaño de esa dimensión y aún preservar todo el contenido de la matriz, porque está cambiando la última y única dimensión. Sin embargo, si su matriz tiene dos o más dimensiones, puede cambiar el tamaño de solo la última dimensión si usa Conservar.

Debido a la orientación de los datos pelados de la hoja de trabajo, el primer rango fue la dimensión para crecer con ReDim, por lo que la orientación se volcó con Application.Transpose, ReDim''ed con Preserve y luego volteó hacia atrás. A medida que la matriz creció con registros adicionales, Application.Transpose alcanzó rápidamente su capacidad máxima para reorientar la matriz. Encontré algo de documentación anterior sobre esto en XL: Limitaciones de pasar matrices a Excel usando la automatización, pero está terriblemente desactualizado.

Mi solución fue transponer los valores de ar1 a ar2 sobre la marcha para que ar2 pudiera redimensionarse sin reorientación. Una vez que se completó el procesamiento, los resultados estaban en la orientación incorrecta. Para recuperar los valores en la hoja de trabajo en la orientación correcta, escribí una función auxiliar que transpuso ar2 nuevo a un ar1 truncado. Esta pseudo-transposición solo se necesitaba una vez; justo antes de volver a introducir los nuevos valores agregados en el área de informes.

Subcódigo modificado:

Sub jpd_Transposing()
    Const sDestination As String = "D2"
    Dim ar1 As Variant
    Dim ar2 As Variant
    Dim i As Long ''counter

    With ActiveSheet
        ar1 = .Range("A2:B" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
        ReDim ar2(1 To 2, 1 To 1)
        ar2(1, 1) = ar1(1, 1): ar2(2, 1) = ar1(1, 2)
        For i = 2 To UBound(ar1, 1)
            If ar1(i, 1) = ar2(1, UBound(ar2, 2)) Then
                ar2(2, UBound(ar2, 2)) = ar2(2, UBound(ar2, 2)) & ar1(i, 2)
            ElseIf ar1(i, 1) = vbNullString Then
                ar2(2, UBound(ar2, 2)) = ar2(2, UBound(ar2, 2)) & " "
            Else
                ReDim Preserve ar2(1 To 2, 1 To UBound(ar2, 2) + 1)
                ar2(1, UBound(ar2, 2)) = ar1(i, 1)
                ar2(2, UBound(ar2, 2)) = ar1(i, 2)
            End If
        Next
        ar1 = my_2D_Transpose(ar1, ar2)
        .Range(sDestination).Resize(UBound(ar1, 1), UBound(ar1, 2)) = ar1
    End With

End Sub

Function my_2D_Transpose(a1 As Variant, a2 As Variant)
    Dim a As Long, b As Long
    ReDim a1(1 To UBound(a2, 2), 1 To UBound(a2, 1))
    For a = LBound(a2, 1) To UBound(a2, 1)
        For b = LBound(a2, 2) To UBound(a2, 2)
            a1(b, a) = Trim(a2(a, b))
        Next b
    Next a
    my_2D_Transpose = a1
End Function

Entonces, ahora se preguntará cuánta mejora sobre la rutina basada en la hoja de trabajo original había con el procesamiento de memoria ordenada. Como ese era el siguiente paso lógico, ejecuté ambos con un temporizador que indicaba iniciar y detener.

Sub timed()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Debug.Print Timer
    Call concatenate_and_transpose_to_delim_string
    Debug.Print Timer
    Call jpd_Transposing
    Debug.Print Timer
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Los dos resultados de los datos del informe fueron idénticos. Tenga en cuenta que apagué la actualización de pantalla y el manejo de eventos durante la prueba. Esto probablemente mejoró el método de la hoja de trabajo más de lo que mejoró el método de matriz, pero pensé que era justo dado que estas son técnicas bastante estándar para mejorar la eficiencia de una macro.

Resultados cronometrados:

Entorno de prueba: 45.894 filas × 2 columnas de datos sin procesar convertidos a 123 filas × 2 columnas de datos de informes agregados utilizando una computadora portátil basada en clase empresarial i5 / 8Gb (Win7, Excel 2010 versión 14.0.7145.5000 (32 bits)

concatenate_and_transpose_to_delim_string (hoja de trabajo) .... 00: 01.01 segundos¹
jpd_Transposing (matriz de memoria) ............................................. .. 00: 00.07 segundos¹

¹ La _{prueba se ejecutó varias veces. Los tiempos son típicos.}

Conclusiones:

Bien, entonces tomamos casi un segundo completo usando una matriz de memoria variante sobre la lectura / escritura de la hoja de trabajo, pero eso sigue siendo una mejora del 93% en la eficiencia del procesamiento de datos idénticos a resultados idénticos. He convertido otras rutinas de larga duración de la hoja de trabajo dirigida a la memoria ordenada; los resultados fueron al menos tan apreciables y se dedicaron a operaciones de búsqueda más repetitivas en grandes matrices de datos.

¿Valió la pena? Eso depende más o menos del usuario individual y la situación. Ciertamente, se pueden obtener beneficios, pero escribo código basado en hojas de trabajo mucho más rápido que el código basado en matriz, por lo que a menos que esto se ejecute varias veces al día todos los días, probablemente no me molestaría. El tamaño del proyecto también sería un factor, ya que los beneficios aumentarían con la cantidad de trabajo a realizar. Aún así, es bueno mantener los métodos utilizados con los métodos de matriz de memoria frescos en la mente y una combinación de métodos puede producir el mejor resultado en algunos casos.

_{FWIW, la función VBA Trim utilizada en la función auxiliar de transposición no produjo ningún efecto perjudicial medible (por ejemplo, tiempo adicional) si se usó o no y parecía el mejor lugar para asegurarse de que el resultado final no tuviera un carácter de espacio restante de la cadena concatenación.}