Error13 in Excel VBA in data file
I am executing the VBA code below in the following file:
http://www.filedropper.com/error13
I get an Error 13 Type Mismatch.
Here is the macro
Aggregate, Collate and Transpose rows into columns
Works fine when I select some of the rows (for example id 1001 or 1003 and 1004 together but when I try to process more rows I get Error 13.
I'm trying to process each id at a time but I have about 100..
Solution 1:
For the sake of curiosity and intellectual exercise, I took a stab at the array processing method originally submitted by ZygD in the original question with a mind to overcome the Runtime error '13': Type mismatch error on larger data sets.
The ReDim Preserve statement can only redimension the last rank while preserving existing values already stored subject to the fact that you are raising the size of the array dimension and not shrinking it. This is what msdn.microsoft.com has to say on the subject:
Resizing with Preserve. If you use Preserve, you can resize only the last dimension of the array. For every other dimension, you must specify the bound of the existing array.
For example, if your array has only one dimension, you can resize that dimension and still preserve all the contents of the array, because you are changing the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension if you use Preserve.
Due to the orientation of the data peeled out of the worksheet, the first rank was the dimension to grow with ReDim so the orientation was flipped with Application.Transpose, ReDim'ed with Preserve then flipped back. As the array grew with additional records, the Application.Transpose quickly reached its maximum capacity to reorient the array. I found some older documentation on this in XL: Limitations of Passing Arrays to Excel Using Automation but it is horribly out of date.
My solution was to transpose the values from ar1 into ar2 on the fly so that ar2 could be redimensioned without reorientation. Once processing was complete, the results were in the wrong orientation. To get the values back into the worksheet in the correct orientation, I wrote a helper function that transposed ar2 back into a truncated ar1. This pseudo-transpose was only needed once; just before stuffing the new aggregated values back into the reporting area.
Modified sub code:
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
So now you might be wondering just how much improvement over the original worksheet based routine there was with the arrayed memory processing. As that was the logical next step, I ran both with a timer noting start and stop.
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
Both of the report data results were identical. Note that I turned off screen updating and event handling for the duration of the test. This likely improved the worksheet method more than it improved the array method but I thought it was fair given that these are pretty standard techniques for improving the efficiency of a macro.
Timed Results:
Test environment: 45,894 rows × 2 columns of raw data converted to 123 rows × 2 columns of aggregated report data using a business class i5/8Gb based laptop (Win7, Excel 2010 version 14.0.7145.5000 (32-bit)
concatenate_and_transpose_to_delim_string (worksheet) .... 00:01.01 seconds¹
jpd_Transposing (memory array) ............................................... 00:00.07 seconds¹
¹Test was run several times. Times are typical.
Conclusions:
Okay, so we picked up almost a full second using a variant memory array over the worksheet read/write but that is still a whopping 93% improvement in efficiency of processing identical data to identical results. I've converted other long-running routines from worksheet driven to arrayed memory; the results were at least as appreciable and those were devoted to more repetitious lookup-type operations in large data matrices.
Was it worth it? That's pretty much up to the individual user and situation. Certainly there are benefits to be had but I write worksheet based code a lot faster than array based code so unless this ran several times a day every day, I probably wouldn't bother. The size of the project would also be a factor as benefits would increase with the amount of work to be done. Still, it's good to keep the methods used with memory array methods fresh in the mind and a mashup of methods may produce the best result in some cases.
FWIW, the VBA Trim function used in the transpose helper function produced no measurable detrimental effect (e.g. additional time) whether it was used or not and seemed the best place to ensure that the end result did not have a trailing space character left over from string concatenation.