使用VBA将二维数组卸载到excel工作表中最快的方法是什么？ [英] What is the fastest way to unload a 2 dimensional Array into an excel worksheet using VBA?

问题描述

我有一个常规运行很长时间，因为一个大循环，在工作表中按单元格编写值。我以为我会很聪明，把所有东西都加载到2D阵列中，然后快速将其转储到Excel表格中。目标是加快速度。发现我不能只做我想要的

  xlsheet.Range（C2）= MASTER_OUT 
  

发现上述不行。 MASTER_OUT是一个具有34个列和各行的2D数组。我希望我不必循环遍历数组，并将数据单元格放在工作表中，因为根本不会保存任何时间。

解决方案

。使用 UBound功能调整目的地大小。

  xlsheet.Range（C2）。调整大小（UBound（MASTER_OUT，1），UBound（MASTER_OUT，2））= MASTER_OUT 
  

附录：

翻转行和列是很常见的。如果是这种情况，请使用 Excel应用程序对象' TRANSPOSE功能和调整大小（UBound（MASTER_OUT，2），UBound（MASTER_OUT）），调整大小（UBound（MASTER_OUT，2），UBound ，1））= _
Application.Transpose（MASTER_OUT）

请注意， TRANSPOSE 有局限性¹。我相信它会在一个无符号整数的最大值周围变得不可靠（例如65,536（...？））

---

¹请参阅数据文件中的Excel VBA中的错误13，以获得可靠的二维转置辅助功能无限制。

I have a routine that was running long due to a big loop that writes values cell by cell in a worksheet. I thought I would be smart and load everything into a 2D array first then quickly dump it into the excel sheet. Goal being to speed things up. Found out I can't just do what I wanted.

  xlsheet.Range("C2") = MASTER_OUT

Found out the above doesn't work. MASTER_OUT is a 2D array with 34 cols and various rows. I hope I don't have to loop through the array and place data cell by cell in the worksheet as that won't save any time at all.

解决方案

.Resize the destination with the UBound function.

xlsheet.Range("C2").Resize(UBound(MASTER_OUT, 1), UBound(MASTER_OUT, 2)) = MASTER_OUT

Addendum:

It is common to flip the rows and columns. If this is the case then use the Excel Application object's TRANSPOSE function and flip the ubound ranks.

xlsheet.Range("C2").Resize(UBound(MASTER_OUT, 2), UBound(MASTER_OUT, 1)) = _
   Application.Transpose(MASTER_OUT)

Please note that TRANSPOSE has limitations¹. I believe it starts to become unreliable somewhere around an unsigned integer's maximum value (e.g. 65,536 (...?))

---

¹ See Error13 in Excel VBA in data file for a reliable 2D transpose helper function without limitations.

这篇关于使用VBA将二维数组卸载到excel工作表中最快的方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！