VBA中的变体数据类型及其正面使用

There's lots of stuff on Google regarding the variant data type. Mostly, they say things like "Avoid using it too much and here's why" or "It can hold any type of data", which things I understand. But I still don't fully understand when to use them. Can someone concisely explain and, more importantly, give examples of best uses of the variant data type (maybe even an example of passing a variant to a function as opposed to an explicitly declared variable)?

关于变体数据类型,Google上有很多东西。大多数情况下,他们会说“避免使用它太多,这就是为什么”或“它可以保存任何类型的数据”,这些都是我理解的。但我还是不完全明白何时使用它们。有人可以简明扼要地解释,更重要的是,给出变量数据类型的最佳用法示例(甚至可能是将变量传递给函数而不是显式声明变量的示例)?

3 个解决方案

#1

In general, always use the smallest-memory variable that you can. Variant is the biggest, so you should only use it when there isn't a better typed one. Here are some examples

通常,始终使用最小的内存变量。 Variant是最大的,所以你应该只在没有更好的类型的时候使用它。这里有些例子

Array()

The Array function returns a variant containing an array. The only way you can use Array is to assign to a variant.

Array函数返回包含数组的变量。您可以使用Array的唯一方法是分配给变体。

vArr = Array(1, "a", True)

Assigning Arrays to Ranges

将数组分配给范围

If you have to write a bunch of data to cells, it's way faster to put them in an array and write them all at once rather than one cell at a time. If your data is all the same data type, use a properly typed array. But if it's not all the same data, you probably need to use an array of Variants.

如果你必须将一堆数据写入单元格,那么将它们放入一个数组并将它们全部写入而不是一次写入一个单元格会更快。如果您的数据都是相同的数据类型,请使用正确类型的数组。但如果不是所有相同的数据,您可能需要使用Variants数组。

Dim vaWrite(1 To 2, 1 To 2) As Variant

vaWrite(1, 1) = 1
vaWrite(1, 2) = "Bob"
vaWrite(2, 1) = 2
vaWrite(2, 2) = "Tim"

Range("A1").Resize(2, 2).Value = vaWrite

And going the other way - range to array requires a Variant regardless of the data types in the cells

另一方面 - 无论单元格中的数据类型如何,范围到数组都需要Variant

vArr = Range("A1:C10").Value

Worksheet Functions

Some (all?) worksheet functions can return errors as well as whatever their normal values are. The only data type that can hold, for example, a Double and an Error is a Variant.

一些(所有?)工作表函数可以返回错误以及它们的正常值。唯一可以容纳的数据类型,例如Double和Error是Variant。

vRes = Application.WorksheetFunction.Match(...)

Those are three examples I can think of. There may be more.

这是我能想到的三个例子。可能还有更多。

#2

Variant type take more space in memory than any other variable (here you'll find the code for the macro, to test, I just added the routine for variant)

变体类型在内存中占用的空间比任何其他变量都多(在这里你可以找到宏的代码,进行测试,我只是添加了变量的例程)

There are some other factors that may be in play for this, it's a very subjective topic as such this is my personal experience:
1. A good code would have to be explicitly explained, using variant may lead to confusion in what should be defined in first place.
2. Variant may not get the real property of the object, thus, it will take you more time to code and you may not see the real properties for the object IE:

还有一些其他因素可能会起作用,这是一个非常主观的话题,因为这是我个人的经验:1。必须明确解释一个好的代码,使用变体可能导致在应该定义的内容中出现混淆第一名。 2. Variant可能无法获得对象的真实属性,因此,它将花费您更多时间进行编码,您可能看不到对象IE的真实属性:

Dim myRange as Range
Set myRange = Range("A1:A10")
myRange. 'I can see the properties of the object!
Dim myRange as Variant
Set myRange = Range("A1:A10")
myRange. 'I can't see what properties this may have in this context!

I wanted to give the real context on why to avoid them -I think we both agree on that-.
Now, when to use them? Well this may be subject to discussion as well, but, I have found them useful when you can't control the input.
How this may happen?
Example 1: A TextBox Value let to user input and I don't want to let the user type words, only numbers (whenever you type a word should block it), but, TextBox alone can't block that, so, what you should do?

我想说明为什么要避免它们的真实背景 - 我认为我们都同意这一点。现在,何时使用它们?那么这也可能会受到讨论,但是,当你无法控制输入时,我发现它们很有用。怎么会发生这种情况?示例1:一个TextBox值让用户输入,我不想让用户输入单词,只有数字(每当你输入一个单词时应该阻止它),但是,单独的TextBox不能阻止它,所以,你是什么应该做?

Private Sub TextBox_Value_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim ChrPressed As Variant
    ChrPressed = Chr(KeyAscii)
    Select Case ChrPressed
        Case IsNumeric(ChrPressed)
        Me.TextBox_Value.Value = Me.TextBox_Value.Value & ChrPressed
        KeyAscii = 0
        Case "0"
        Me.TextBox_Value.Value = Me.TextBox_Value.Value & ChrPressed
        KeyAscii = 0
    End Select
End Sub

Where ChrPressed is Variant since user could press word keys still but we are going to define what is pressed and doing the research inside our code -other approach may lead to error debugger to user making our experience not that smooth-.
2. If you don't want to add the proper references to avoid late or early binding but, you know those functions are allowed in the variable itself, this helps as well to avoid some workarounds you may have to do in the future related to late,early binding (though I haven't faced an issue if I do it that way):

其中ChrPressed是Variant,因为用户可以按下单词键但我们将定义按下的内容并在我们的代码中进行研究 - 其他方法可能导致错误调试器给用户使我们的经验不那么顺利。 2.如果您不想添加适当的引用以避免延迟或早期绑定,但是您知道变量本身允许这些功能,这也有助于避免将来可能需要做的一些相关的变通方法。迟到,早期绑定(虽然如果我这样做,我没有遇到过问题):

Sub ImportFile()
Dim MyFile As Variant
MyFile = Application.GetOpenFilename("Import File*.CSV", Title:="CSV Data")
    If MyFile = False ...

3. Arrays that can have numerous data and you want to control them as such

3.可以拥有大量数据并且您希望控制它们的数组

Dim ArrayForTest() as Variant
Select Case ArrayForTest(ArrayElement)
Case IsNumeric(ArrayForTest(ArrayElement))
Case IsString(ArrayForTest(ArrayElement)) 'Just representative not the real way to do so

4. Elements within an element that you haven't found a way to properly call them and you'll learn later on or don't really have an impact in performance related to the proper way to call them.

4.元素中的元素,您没有找到正确调用它们的方法,您将在以后学习或者不会对与调用它们的正确方法相关的性能产生影响。

Sub Graphs_makegraph(XValues As Variant, ChartStyle...
ActiveSheet.Shapes.AddChart2(ChartTypeNumber, ChartStyle).Select
...
ActiveChart.FullSeriesCollection(1).XValues = XValues

Most programmers don't like this approach because you are letting Excel "handle the things", a good coding practice is that you should have everything defined as OOP defines, but, I find that excel versatility in doing that right speeds up the solution and save us a few .class/interfaces then back again to the main class that's calling that we would have to do other way in MVP Programming. As an OT: I mean, if we want to get that strict we would need to program in asm language to avoid any interference of compilers our original intention.

大多数程序员不喜欢这种方法,因为你让Excel“处理事情”,一个好的编码实践是你应该把所有定义为OOP定义,但是,我发现这样做的优点是加速解决方案和保存我们几个.class /接口然后再回到主要类,它调用我们将不得不在MVP编程中做其他方式。作为OT:我的意思是,如果我们想要严格要求,我们需要用asm语言编程,以避免编译器干扰我们的初衷。

#3

"...an example of passing a variant to a function as opposed to an explicitly declared variable"

“...将变量传递给函数而不是显式声明的变量的示例”

Joel Spolsky mentions one form of this here, but I find it incredibly useful to simulate method overloading by using Variant-typed parameters.

Joel Spolsky在这里提到了这种形式,但我发现使用Variant类型参数模拟方法重载非常有用。

For example, take a look at this macro that adds worksheets to a workbook:

例如,看一下将工作表添加到工作簿的宏:

Sub addSheets(wb As Workbook, ByVal wsNames As Variant)
'Adds one or more worksheets (wsNames) to a workbook (wb)

    Dim ws As Worksheet
    Dim i As Long

    If Not IsArray(wsNames) Then
        wsNames = Array(wsNames)
    End If

    For Each ws In wb.Sheets
        For i = LBound(wsNames) To UBound(wsNames)
            If ws.Name = wsNames(i) Then
                MsgBox "Error: Sheet " & wsNames(i) & " already exists in workbook"
                Stop
                Exit Sub
            End If
        Next
    Next

    For i = LBound(wsNames) To UBound(wsNames)
        Dim newWS As Worksheet
        Set newWS = wb.Worksheets.Add
        newWS.Name = wsNames(i)
    Next

End Sub

Because it will accept either a String or an array of Strings for the wsNames parameter, both of the following calls are valid:

因为它将为wsNames参数接受字符串或字符串数组,所以以下两个调用都是有效的:

'Adds sheet called "TestWS1" to the active workbook
Call addSheets(ActiveWorkbook,"TestWS1")
'Adds sheets called "TestWS2" and "TestWS3" to the active workbook 
Call addSheets(ActiveWorkbook, Array("TestWS2","TestWS3"))

By using variants and the "IsArray" method (and similar ones like "IsNumeric","IsDate", etc.) to force individual methods to accept multiple data types for a single parameter, you can avoid having to create separate methods to accomplish similar tasks (ie "addSheet" for adding one WS, "addSheets" for adding multiple).

通过使用变量和“IsArray”方法(以及类似的“IsNumeric”,“IsDate”等)来强制单个方法接受单个参数的多个数据类型,您可以避免必须创建单独的方法来实现类似任务(即“addSheet”用于添加一个WS,“addSheets”用于添加多个)。

#1