Excel VBA：数组变量中的变体
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7513816/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: Variants in Array Variables
提问by Surferosa
A question on variants. Im aware that variants in Excel vba are both the default data type and also inefficient (from the viewpoint of overuse in large apps). However, I regularly use them for storing data in arrays that have multiple data types. A current project I am working on is essentially a task that requires massive optimistaion of very poor code (c.7000 lines)- and it got me thinking; is there a way around this?
关于变体的问题。我知道 Excel vba 中的变体既是默认数据类型，也是低效的（从在大型应用程序中过度使用的角度来看）。但是，我经常使用它们将数据存储在具有多种数据类型的数组中。我目前正在进行的一个项目本质上是一项需要对非常糟糕的代码（大约 7000 行）进行大量优化的任务——这让我开始思考；有没有解决的办法？
To explain; the code frequently stores data in array variables. So consider a dataset of 10 columns by 10000. The columns are multiple different data types (string, double, integers, dates,etc). Assuming I want to store these in an array, I would usually;
解释; 代码经常将数据存储在数组变量中。所以考虑一个 10 列乘以 10000 的数据集。列是多种不同的数据类型（字符串、双精度、整数、日期等）。假设我想将这些存储在一个数组中，我通常会；
dim myDataSet(10,10000) as variant
But, my knowledge says that this will be really inefficient with the code evaluating each item to determine what data type it is (when in practise I know what Im expecting). Plus, I lose the control that dimensioning individual data types gives me. So, (assuming the first 6 are strings, the next 4 doubles for ease of explaining the point), I could;
但是，我的知识表明，对于评估每个项目以确定它是什么数据类型的代码，这将非常低效（实际上我知道我期望什么）。另外，我失去了对单个数据类型进行尺寸标注所赋予我的控制权。所以，（假设前 6 个是字符串，接下来的 4 个是双倍以方便解释这一点），我可以；
dim myDSstrings(6,10000) as string
dim myDSdoubles(4,10000) as double
This gives me back the control and efficiency- but is also a bit clunky (in practise the types are mixed and different- and I end up having an odd number of elements in each one, and end up having to assign them individually in the code- rather than on mass). So, its a case of;
这让我重新获得控制和效率 - 但也有点笨拙（实际上，类型是混合和不同的 - 我最终在每个元素中都有奇数个元素，最终不得不在代码中单独分配它们- 而不是质量）。所以，这是一个案例；
myDSstrings(1,r) = cells(r,1)
myDSdoubles(2,r) = cells(r,2)
myDSstrings(2,r) = cells(r,3)
myDSstrings(3,r) = cells(r,4)
myDSdoubles(3,r) = cells(r,5)
..etc...
Which is a lot more ugly than;
哪个比它丑得多；
myDataSet(c,r) = cells(r,c)
So- it got me thinking- I must be missing something here. What is the optimal way for storing an array of different data types? Or, assuming there is no way of doing it- what would be best coding-practise for storing an array of mixed data-types?
所以-这让我想到-我一定在这里遗漏了一些东西。存储不同数据类型的数组的最佳方法是什么？或者，假设没有办法做到这一点 - 存储混合数据类型数组的最佳编码实践是什么？
采纳答案by Dick Kusleika
Never optimize your code without measuring first. You'll might be surprised where the code is the slowest. I use the PerfMon utility from Professional Excel Development, but you can roll your own also.
永远不要在没有先测量的情况下优化你的代码。您可能会对代码最慢的地方感到惊讶。我使用 Professional Excel Development 中的 PerfMon 实用程序，但您也可以使用自己的实用程序。
Reading and writing to and from Excel Ranges is a big time sink. Even though Variants can waste a lot of memory, this
从 Excel 范围读取和写入是一个很大的时间槽。尽管 Variant 会浪费大量内存，但这
Dim vaRange as Variant
vaRange = Sheet1.Range("A1:E10000").Value
'do something to the array
Sheet1.Range("A1:E10000").Value = vaRange
is generally faster than looping through rows and cells.
通常比循环遍历行和单元格快。
My preferred method for using arrays with multiple data types is to not use arrays at all. Rather, I'll use a custom class module and create properties for the elements. That's not necessarily a performance boost, but it makes the code much easier to write and read.
我使用具有多种数据类型的数组的首选方法是根本不使用数组。相反，我将使用自定义类模块并为元素创建属性。这不一定是性能提升，但它使代码更易于编写和阅读。
回答by JMax
I'm not sure your bottleneck comes from the Varianttyping of your array.
我不确定你的瓶颈来自Variant你的数组类型。
By the way, to set values from an array to an Excel range, you should use (in Excel 8 or higher):
顺便说一下，要将数组中的值设置为 Excel range，您应该使用（在 Excel 8 或更高版本中）：
Range("A1:B2") = myArray
On previous versions, you should use the following code:
在以前的版本中，您应该使用以下代码：
Sub SuperBlastArrayToSheet(TheArray As Variant, TheRange As Range)
With TheRange.Parent.Parent 'the workbook the range is in
.Names.Add Name:="wstempdata", RefersToR1C1:=TheArray
With TheRange
.FormulaArray = "=wstempdata"
.Copy
.PasteSpecial Paste:=xlValues
End With
.Names("wstempdata").Delete
End With
End Sub
from this sourcethat you should read for VBA optimization.
从这个来源，你应该阅读 VBA 优化。
Yet, you should profile your appto see where your bottlenecks are. See this question from Issunto help you benchmark your code.
然而，您应该分析您的应用程序以查看您的瓶颈在哪里。请参阅Issun 的此问题以帮助您对代码进行基准测试。
