VBA中”Double”和”Variant”数组类型在转置时的区别是什么?

huangapple go评论116阅读模式
英文:

VBA difference between "Double" and "Variant" array types when transposing

问题

我想要有人解释为什么如果数组被定义为Variant类型,VBA代码可以工作,但如果定义为Double类型,当转置数组时就不能工作。我有这个简单的测试代码:

Option Base 1
Sub testDELput()
    Dim c() As Variant
    Dim d() As Variant
    Dim i As Integer
    ReDim c(5)
    For i = 1 To 5        'assign test values to c
        c(i) = 10 * i
    Next
    ReDim d(5, 1)
    d = Application.WorksheetFunction.Transpose(c)
End Sub

这段代码运行良好,d是一个包含c的元素的列向量。然而,如果我将数组声明更改为Double,即:

Dim c() As Double
Dim d() As Double

我会得到以下错误:

VBA中”Double”和”Variant”数组类型在转置时的区别是什么?

我无法理解为什么会这样。有人能解释一下吗?

如果我像这样重新定义c,也会发生同样的情况:

ReDim c(1, 5)

感谢关注。

英文:

I would like someone to explain why VBA code works if arrays are defined as Variant type but not if they are defined as Double type, when transposing the array.
I have this simple test code:

Option Base 1
Sub testDELput()
    Dim c() As Variant
    Dim d() As Variant
    Dim i As Integer
    ReDim c(5)
    For i = 1 To 5        'assign test values to c
        c(i) = 10 * i
    Next
    ReDim d(5, 1)
    d = Application.WorksheetFunction.Transpose(c)
End Sub

This code works fine, d is a column vector with the elements of c
However, if I change the array declarations to Double, i.e.:

Dim c() As Double
Dim d() As Double

I got the following error:

VBA中”Double”和”Variant”数组类型在转置时的区别是什么?

I cannot understand why. Can anybody explain please?

The same happens if I redim c like this:

ReDim c(1, 5)

Thanks for the attention

答案1

得分: 3

WorksheetFunction.Transpose 声明为返回一个(单一的) Variant

Function Transpose(Arg1) As Variant

一个单一的 Variant 可以包含各种东西,包括其他数据类型的数组。

编译器知道这一点,因此它不会立即引发编译时错误。它必须等到运行时才能确定返回的 Variant 是否恰好包含一个 Double 数组,而这是你的 d() 可以包含的唯一内容。

恰巧的是,Transpose 返回的 Variant 不包含 Double 数组。它包含一个 Variant 数组。您的 d() 不能包含它,因此会发生运行时类型不匹配错误。

如果 Transpose 被声明为明确返回一个 Variant() 数组:

Function Transpose(Arg1) As Variant()

那么,您不会得到运行时错误 13,而会得到一个编译时错误 "无法分配给数组",因为这次编译器无需等到运行时来检查返回的值是否与 d() 兼容。它可以立刻看到它不兼容。

您唯一能够分配给 d() As Double 的方式是如果您有一个明确返回 Double() 数组(甚至不是 Single()Long())的函数,或者一个在调用时恰好包含 Double() 数组的单一 Variant 的函数:

Sub testDELput()
    Dim d() As Double
    
    d = returns_double_array_as_double_array()    '有效
    d = returns_double_array_as_variant()         '有效
    
    'd = returns_variant_array_as_variant()       '运行时错误 13: 类型不匹配
    'd = returns_variant_array_as_variant_array() '编译时错误: 无法分配给数组
End Sub

Private Function returns_double_array_as_double_array() As Double()
    Dim arr() As Double
    returns_double_array_as_double_array = arr
End Function

Private Function returns_double_array_as_variant() As Variant
    Dim arr() As Double
    returns_double_array_as_variant = arr
End Function

Private Function returns_variant_array_as_variant() As Variant
    Dim arr() As Variant
    returns_variant_array_as_variant = arr
End Function

Private Function returns_variant_array_as_variant_array() As Variant()
    Dim arr() As Variant
    returns_variant_array_as_variant_array = arr
End Function
英文:

WorksheetFunction.Transpose is declared to return a (single) Variant:

Function Transpose(Arg1) As Variant

A single Variant can contain all sorts of things, including an array of other data types.

The compiler knows that, so it does not raise a compile-time error immediately. It has to wait till runtime to see if, by any chance, that returned Variant happens to contain an array of Doubles, which is the only thing that your d() can contain.

It so happens that no, the Variant that Transpose returns does not contain an array of Doubles. It contains an array of Variants. Your d() cannot contain that, so a runtime type mismatch error occurs.

If Transpose were declared as explicitly returning an array of variants:

Function Transpose(Arg1) As Variant()

then instead of a runtime error 13 you would get a compile time error "Can't assign to array", because this time the compiler does not have to wait till runtime to check if the returned value is in fact compatible with d(). It can see straight away that it isn't.

The only way you would be able to assign to a d() As Double is if you had a function that explicitly returns a Double() (not even Single() or Long()), or a function that returns a single Variant that at the time of the call will happen to contain an array of Double():

Sub testDELput()
    Dim d() As Double
    
    d = returns_double_array_as_double_array()    'Works
    d = returns_double_array_as_variant()         'Works
    
    'd = returns_variant_array_as_variant()       'Run-time error 13: Type mismatch
    'd = returns_variant_array_as_variant_array() 'Compile-time error: Can't assign to array
End Sub

Private Function returns_double_array_as_double_array() As Double()
    Dim arr() As Double
    returns_double_array_as_double_array = arr
End Function

Private Function returns_double_array_as_variant() As Variant
    Dim arr() As Double
    returns_double_array_as_variant = arr
End Function

Private Function returns_variant_array_as_variant() As Variant
    Dim arr() As Variant
    returns_variant_array_as_variant = arr
End Function

Private Function returns_variant_array_as_variant_array() As Variant()
    Dim arr() As Variant
    returns_variant_array_as_variant_array = arr
End Function

答案2

得分: 1

你对数组的理解会变得奇怪,如果你不知道 Transpose 函数返回什么。

正如微软在这里所述,它返回一个 Variant 类型!

Sub testDoubleArray()
  Dim d() As Double, i As Long

  ReDim d(5, 1)
  For i = 1 To 5
        d(i, 1) = i * 100
  Next i
  
  Debug.Print Join(Application.Transpose(d), "|") '只是为了可视化查看其内容(在转换为1D数组 - Variant类型之后)
End Sub

然后,在你尝试的示例中,mismatch 错误是因为你不能使用 Variant 类型的数组加载 Double 数组:

Sub testDELput()
    Dim c() As Double
    Dim d() As Variant '因为这是Transpose返回的类型!
    Dim i As Integer
    ReDim c(5): Debug.Print LBound(c): Stop
    For i = 1 To 5        '将测试值分配给c
        c(i) = 10 * i
    Next
    ReDim d(5, 1)
    
    With Application
       Debug.Print Join(.Transpose(.Transpose(c)), "|"): Stop '1D Variant类型
        d = .Transpose(c)
    End With
    
    Debug.Print LBound(d), UBound(d), UBound(d, 2)
End Sub

你可以从另一个 Double 数组加载一个 Double 数组,就像你在下一个子例中看到的那样:

Sub testDoubleArray()
  Dim d() As Double, c() As Double, i As Long

  ReDim d(5, 1)
  For i = 1 To 5
        d(i, 1) = i * 10
  Next i
  
  Debug.Print Join(Application.Transpose(d), "|") '只是为了可视化查看其内容(在转换为1D数组之后)
  ReDim c(5, 1)
  c = d
  
  Debug.Print Join(Application.Transpose(c), "|")
End Sub
英文:

Your understanding about array becomes strange if you do not know what Transpose function returns.

As Microsoft states here, it returns a Variant!

Sub testDoubleArray()
  Dim d() As Double, i As Long

  ReDim d(5, 1)
  For i = 1 To 5
        d(i, 1) = i * 100
  Next i
  
  Debug.Print Join(Application.Transpose(d), "|") 'just to visually see its content (after transforming in 1D array - Variant type)
End Sub

Then, in the example you try, the mismatch error comes from the fact that you cannot load a Double array with a Variant type one:

Sub testDELput()
    Dim c() As Double
    Dim d() As Variant 'because this is what Transpose returns!
    Dim i As Integer
    ReDim c(5): Debug.Print LBound(c): Stop
    For i = 1 To 5        'assign test values to c
        c(i) = 10 * i
    Next
    ReDim d(5, 1)
    
    With Application
       Debug.Print Join(.Transpose(.Transpose(c)), "|"): Stop '1D Variant type
        d = .Transpose(c)
    End With
    
    Debug.Print LBound(d), UBound(d), UBound(d, 2)
End Sub

You can load a Double array from another Double one, as you can see in the next sub:

Sub testDoubleArray()
  Dim d() As Double, c() As Double, i As Long

  ReDim d(5, 1)
  For i = 1 To 5
        d(i, 1) = i * 10
  Next i
  
  Debug.Print Join(Application.Transpose(d), "|") 'just to visually see its content (after tranforming in 1D array)
  ReDim c(5, 1)
  c = d
  
  Debug.Print Join(Application.Transpose(c), "|")
End Sub

答案3

得分: -1

我的过去经验类似,我通常在以编程方式更改您标记为c()和d()的变量中包含的值的情况下默认使用“Variant”数据类型。

我明白您指的是您本来希望使用的“Double”数据类型应该允许您存储声明的元素。

因此,尽管“Variant”可以存储不同的值类型,包括整数、字符串和日期等,但“Double”数据类型专用于表示双精度浮点数。

据我所知,您的代码可能会生成最终结果的整数,而不是浮点数,因此出现了类型不匹配的错误,例如c(5) = 10 * 5生成50(整数),而不是50.00(双精度浮点数)。您需要将结果强制转换为浮点类型,例如10 * 5.00 = 50.00,或将c()和d()声明为整数类型。

仅仅将数据类型声明为Double很可能不会自动执行从整数到Double的转换,正如人们可能会假设的那样。

英文:

My experience in the past has been similar, and I default to using the 'Variant' data type in situations where I'm programmatically changing the values contained in the variables you've denoted c() and d().

I understand that you're eluding to the fact that you would have expected the 'Double' data types being used should have otherwise allowed you to store the elements being declared.

So whilst 'Variant' can store different value types including integer, string, and dates etc. the 'Double' data type is specific to representing double-precision floating-point numbers.

Your code as far as I can tell is likely yielding integer numbers in the final result and NOT floating point numbers hence the Type mismatch error e.g. c(5) = 10 * 5 yields 50 (integer) and not 50.00 (double). You need to either coerce the result to be a float type e.g. 10 * 5.00 = 50.00 or dim c() and d() as an Integer.

Simply stating the data type as Double is likely not performing this conversion from Integer to Double automatically as one might assume.

huangapple
  • 本文由 发表于 2023年6月12日 01:33:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/76451689.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定