Excel中VBA：变体数组变量 [英] Excel VBA: Variants in Array Variables

问题描述

这是变体的一种问题。林知道，在Excel中VBA变种都是默认的数据类型和效率也很低（从过度使用的大型应用程序的角度来看）。不过，我经常使用它们在具有多种数​​据类型的数组存储数据。目前的一个项目我工作的本质要求$ C很差$ C大规模optimistaion（c.7000行）任务 - 它让我思考;有没有解决的办法？

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?

要解释;在code数组变量经常存储数据。因此，通过10000考虑10列的​​数据集的列是多个不同的数据类型（字符串，双，整数，日期等）。假设我想将这些存储在数组中，我通常会;

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;

dim myDataSet(10,10000) as variant

不过，我的知识说，这将是与code评估每个项目，以确定它是什么类型的数据（当在实践中我知道我在期待）真的效率低下。另外，我失去标注个人数据类型给我的控制。因此，（假设第6串，接下来的4双打为便于解释点），我就可以;

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;

dim myDSstrings(6,10000) as string
dim myDSdoubles(4,10000) as double

这让我回控制和效率 - 而且是一个有点笨重（在实践中，各类混合，different-，我最终不得不在每一个奇数的元素，并最终不得不单独为它们分配在code-而非质量）。所以，它的的情况下;

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)

所以 - 它让我thinking-我必须在这里失去了一些东西。什么是存储不同数据类型的数组的最佳方式？或者，假设有没有做它 - 方式什么是最好的编码实践存储混合数据类型的数组？

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?

推荐答案

从不优化code不先进行测量。其中code是最慢的，你可能会很惊讶。我使用Excel的专业发展Perfmon实用工具，但你也可以推出自己的。

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.

读取和写入和Excel区域是一个大的时间下沉。尽管变种会浪费大量的内存，这

Reading and writing to and from Excel Ranges is a big time sink. Even though Variants can waste a lot of memory, this

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.

有关使用数组与多个数据类型我的preferred的方法是根本不使用数组。相反，我将使用一个自定义的类模块，并为元素的属性。这不一定是性能提升，但它使code更容易编写和阅读。

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.

这篇关于Excel中VBA：变体数组变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！