数据文件中Excel VBA中的Error13 [英] Error13 in Excel VBA in data file
问题描述
我正在以下文件中执行VBA代码：
I am executing the VBA code below in the following file:
http://www.filedropper.com/error13
我收到错误13类型不匹配。
I get an Error 13 Type Mismatch.
这是宏
当我选择一些行时，可以正常工作（例如id 1001或1003和1004在一起，但是当我尝试处理更多的行我得到错误13。
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.
我正在尝试一次处理每个ID，但我有约100 ..
I'm trying to process each id at a time but I have about 100..
推荐答案
为了好奇和智力锻炼，我采取了一个刺激原始由 ZygD 在运行时错误'13'：类型不匹配错误的原始问题的门 - 整理和转置 - 行到列/ 29441489＃29441489>原始问题
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.
ReDim Preserve语句只能重新维护最后一个等级，同时保留已存储的现有值，这取决于您提高数组维度的大小而不缩小它的大小。这是 msdn.microsoft.com 必须说的话题：
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:
使用保留大小调整。如果使用Preserve，则只能调整数组的最后一个维度。对于每个其他维度，您必须指定现有数组的边界。
例如，如果您的数组只有一个维度，则可以调整该维度的大小，并保留数组的所有内容，因为您正在改变最后和唯一的维度。但是，如果您的数组具有两个或多个维度，则可以更改仅使用保留的最后一个维度的大小。
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.
对于从工作表中删除的数据的方向，第一个级别是使用ReDim生长的维度，因此使用Application.Transpose将方向翻转，然后使用保留程序重新定向。随着阵列随着附加记录而增长，Application.Transpose快速达到其最大容量以重新定向阵列。我在 XL：使用自动化传递数组到Excel的限制中找到了一些较旧的文档。 a>但是这是非常过时的。
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.
我的解决方案是将值从 ar1 转换为 ar2 ，以便 ar2 可以重新定位，无需重新定向。处理完成后，结果方向错误。为了以正确的方向将值重新返回到工作表中，我写了一个帮助函数，将 ar2 转换为截断的 ar1 。这种伪转置只需要一次;在填写新的汇总值回到报告区域之前。
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.
修改的子代码：
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.
strong>定时结果
Timed Results:
测试环境：45,894行×2列原始数据转换为123行×2列使用基于i5 / 8Gb的笔记本电脑（Win7，Excel 2010版本14.0.7145.5000（32位））的汇总报告数据
concatenate_and_transpose_to_delim_string （工作表）... 00：01.01秒¹
jpd_Transpose （内存数组）.......................... ..................... 00：00.07秒¹
¹测试运行了好几次，时间是典型的。 / sub>
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.
结论：
好的，所以我们用工作表read / writ上的一个变体内存数组，几乎用了一整秒e但是处理相同数据的效率仍然提高了93％，达到同样的效果。我将其他长时间运行的例程从工作表驱动转换为数组内存;结果至少是相当可观的，而那些在大型数据矩阵中用于更多重复的查找类型操作。
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 ， VBA修剪功能在转置辅助函数中使用，不会产生可测量的有害影响（例如，额外的时间）是否被使用，并且似乎是确保最终结果没有从字符串连接留下的尾随空格字符的最佳位置。 sub>
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.
这篇关于数据文件中Excel VBA中的Error13的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
