英文:
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
我会得到以下错误:
我无法理解为什么会这样。有人能解释一下吗?
如果我像这样重新定义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:
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 Double
s, 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 Double
s. It contains an array of Variant
s. 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论