警惕工作表函数Transpose的隐含陷阱

边缘码农

  近日，在解决“对比差异差异（数据量超过1百万行）”这个帖子提出的问题时，模拟数据运行没问题，使用实际数据时结果不对。使用楼主提供的实际数据，用自己的代码跑一遍，耗时6分钟上下，结果中有许多数值是“#N/A”。经调试代码，随时查看各字典、数组的即时状态，最终将矛头指向了这几句代码：

   
    With Sheet1
        .Cells(15, "E").Resize(dict3.Count, 1) = WorksheetFunction.Transpose(dict3.keys)
    End With



  经查找资料，得出如下未曾掌握的知识点：
  1、因为Transpose为WorksheetFunction表函数，受Excel限制，如：65536行、16384列之类的。例如：我们在调试代码的时候，在本地窗口中，查询字典数据的时候，Item只显示256行。因此，当字典中的Key数量很多时，在使用Transpose函数转置时，就受到了限制，将转置后的数组写入工作表时，就出现“#N/A”这样的数据。
  此前曾多次使用字典处理实际业务，好在字典中的Key数量不多，没有造成错误。
  2、Transpose所能够处理的数组元素字符长度最大为255。这个知识点也是盲区，也未曾经历过。
  3、在VBA中使用工作表函数，效率偏低。


  解决方法：自己编写一小段代码，将字典中的数据逐个写入数组中。代码类似于：



    ReDim jgarr(1 To dict3.Count, 1 To 1)
    I = 1
    For Each Key In dict3
        jgarr(I, 1) = dict3(Key)
        I = I + 1
    Next



  牢记上述知识点，防止在实际工作中犯临界点错误。
  与诸君共勉。

边缘码农

第一个主题帖。

perfect131

是的 vba有三大不要用
1. 不要用 on error resume next （逃避bug不是好码农）
2. 不要用 Transpose (慢而且 长度一旦过3万行 写入就变 #N/A 了）
3. 不要用 redim preserve （每次都会重新写入数组 非常 very 降速）

zhangcheng6688

mark,mark

wang-way

perfect131 发表于 2024-7-5 15:03
是的 vba有三大不要用
1. 不要用 on error resume next （逃避bug不是好码农）
2. 不要用 Transpose (慢 ...

on error resume next又不只是用来逃避bug

杨成云

在VBA中，Application.Transpose以前也困扰了很久。

perfect131

wang-way 发表于 2024-7-5 16:03
on error resume next又不只是用来逃避bug

那不然呢？

skiss

perfect131 发表于 2024-7-5 15:03
是的 vba有三大不要用
1. 不要用 on error resume next （逃避bug不是好码农）
2. 不要用 Transpose (慢 ...

遇到除数0了，真没办法了，用了on error resume next
不知道还有没有其它方法。

lss001

skiss 发表于 2024-7-5 16:30
遇到除数0了，真没办法了，用了on error resume next
不知道还有没有其它方法。

'跳转提示一下错误!
Sub ErrorGoto()
On Error GoTo errHandler

'…………………………

errHandler:
  MsgBox (Err.Description)
  Err.Clear
End Sub

wang-way

lss001 发表于 2024-7-5 16:47
'跳转提示一下错误!
Sub ErrorGoto()
On Error GoTo errHandler

还可以在尾部做完错误处理以后。resume 回原来的地方