如何将变体分配给 VBA 中的变体? [英] How can I assign a Variant to a Variant in VBA?
问题描述
(警告:虽然乍一看可能很像,但这不是初学者级别的问题.如果您熟悉Let coercion"这个短语,或者您曾经查看 VBA 规范,请继续阅读.)
假设我有一个 Variant
类型的表达式,我想将它分配给一个变量.听起来很简单,对吧?
Dim v As Variantv = SomeMethod() ' SomeMethod 有返回类型 Variant
不幸的是,如果 SomeMethod
返回一个对象(即 VarType 为 vbObject 的 Variant),让强制 开始,v
包含对象的简单数据值".换句话说,如果 SomeMethod 返回对 TextBox 的引用,v
将包含一个字符串.
显然,解决方法是使用Set
:
Dim v As Variant设置 v = SomeMethod()
不幸的是,如果 SomeMethod
没有不返回一个对象,例如一个字符串,产生一个类型不匹配错误.
到目前为止,我找到的唯一解决方案是:
Dim v As Variant如果 IsObject(SomeMethod()) 那么设置 v = SomeMethod()别的v = SomeMethod()万一
不幸的副作用是调用 SomeMethod
两次.
有没有不需要调用 SomeMethod
两次的解决方案?
在 VBA 中,将 Variant 分配给您不知道它是对象还是原语的变量的唯一方法是将其作为一个参数.
如果您无法重构代码以便将 v
作为参数传递给 Sub、Function 或 Let 属性(尽管 Let
这也适用于对象),你总是可以在模块范围内声明 v
并有一个专用的 Sub 仅用于保存分配该变量:
Private v As VariantPrivate Sub SetV(ByVal var As Variant)如果 IsObject(var) 那么设置 v = var别的v = 无功万一结束子
在其他地方调用 SetV SomeMethod()
.
不漂亮,但这是不调用 SomeMethod()
两次或不接触其内部工作的唯一方法.
编辑
好的,我仔细考虑了一下,我想我找到了一个更接近您的想法的更好的解决方案:
Public Sub LetSet(ByRef variable As Variant, ByVal value As Variant)如果 IsObject(value) 那么设置变量 = 值别的变量 = 值万一结束子
<块引用>
[...] 我想 VBA 中没有 LetSet v = ... 语句
现在有:LetSet v, SomeMethod()
您没有需要根据其类型将返回值 Let
或 Set
设置为变量,而是传递应保存返回值的变量value 作为第一个参数通过引用,以便 Sub 可以更改其值.
(Warning: Although it might look like one at first glance, this is not a beginner-level question. If you are familiar with the phrase "Let coercion" or you have ever looked into the VBA spec, please keep on reading.)
Let's say I have an expression of type Variant
, and I want to assign it to a variable. Sounds easy, right?
Dim v As Variant
v = SomeMethod() ' SomeMethod has return type Variant
Unfortunately, if SomeMethod
returns an Object (i.e., a Variant with a VarType of vbObject), Let coercion kicks in and v
contains the "Simple data value" of the object. In other words, if SomeMethod returns a reference to a TextBox, v
will contain a string.
Obviously, the solution is to use Set
:
Dim v As Variant
Set v = SomeMethod()
This, unfortunately, fails if SomeMethod
does not return an object, e.g. a string, yielding a Type Mismatch error.
So far, the only solution I have found is:
Dim v As Variant
If IsObject(SomeMethod()) Then
Set v = SomeMethod()
Else
v = SomeMethod()
End If
which has the unfortunate side effect of calling SomeMethod
twice.
Is there a solution which does not require calling SomeMethod
twice?
In VBA, the only way to assign a Variant to a variable where you don't know if it is an object or a primitive, is by passing it as a parameter.
If you cannot refactor your code so that the v
is passed as a parameter to a Sub, Function or Let Property (despite the Let
this also works on objects), you could always declare v
in module scope and have a dedicated Sub solely for the purpose of save-assigning that variable:
Private v As Variant
Private Sub SetV(ByVal var As Variant)
If IsObject(var) Then
Set v = var
Else
v = var
End If
End Sub
with somewhere else calling SetV SomeMethod()
.
Not pretty, but it's the only way without calling SomeMethod()
twice or touching its inner workings.
Edit
Ok, I mulled over this and I think I found a better solution that comes closer to what you had in mind:
Public Sub LetSet(ByRef variable As Variant, ByVal value As Variant)
If IsObject(value) Then
Set variable = value
Else
variable = value
End If
End Sub
[...] I guess there just is no LetSet v = ... statement in VBA
Now there is: LetSet v, SomeMethod()
You don't have a return value that you need to Let
or Set
to a variable depending of its type, instead you pass the variable that should hold the return value as first parameter by reference so that the Sub can change its value.
这篇关于如何将变体分配给 VBA 中的变体?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!