variant changes when I pass it to function

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

variant changes when I pass it to function

问题

我试图在VBA中筛选一个范围,然后将其作为变体对象传递给一个函数。

首先,我定义对象并分配如下:
```vba
Dim wRows As Variant 
ActiveSheet.Range("A:Z").AutoFilter 1, "W"
Set wRows = ActiveSheet.Range("B9:L7000").Rows.SpecialCells(xlCellTypeVisible) 

我在调试器中看到了我想要的对象。
variant changes when I pass it to function

如果我将范围传递给函数,如下所示:

copyWiederkehrend (wRows)
Public Function copyWiederkehrend(all As Variant)
    MsgBox "HALLLLO"
End Function

在调试器中,对象看起来像这样:
variant changes when I pass it to function

丢失了很多数据。

我将对象定义为范围、对象或变体,都不起作用。


<details>
<summary>英文:</summary>

I&#39;m trying to filter a range in VBA then pass it to a function as a variant object.

First, I define the object and assign it as follows:
```vba
Dim wRows As Variant 
ActiveSheet.range(&quot;A:Z&quot;).AutoFilter 1, &quot;W&quot;
Set wRows = ActiveSheet.range(&quot;B9:l7000&quot;).Rows.SpecialCells(xlCellTypeVisible) 

I see the object in my debugger as I would like it.
variant changes when I pass it to function

If I transfer the range to a function as follows:

copyWiederkehrend (wRows)
Public Function copyWiederkehrend(all As Variant)
    MsgBox (&quot;HALLLLO&quot;)
End Function

the object in the debugger looks like this:
variant changes when I pass it to function

A lot of data is lost.

I defined the object as a range, object or as a variant, nothing works.

答案1

得分: 1

在VBA中,调用子例程可以使用以下两种方式之一:

copyWiederkehrend wRows

或者

Call copyWiederkehrend(wRows)

这两种命令是相同的(有些人称使用Call已经不推荐,但这是另一回事)。如果你调用一个函数,而且你不关心返回的结果,语法是完全相同的。如果你想获取函数的结果,语法是:

x = copyWiederkehrend(wRows)

但是当你写成:

copyWiederkehrend (wRows)

括号中的 (wRows) 不用来表示VBA运行时的参数列表。它们被解释为“获取值”(请注意现在函数名和命令的其余部分之间有一个空格)。

类似地,写类似 x = 3 * (y + 2) 的语句会产生类似的效果:运行时会计算 y + 2 并将这个中间结果存储在内存中,然后乘以3,最终将结果传递给 x。关键是一旦中间结果被计算出来,它就不再与 y 关联。当你写 x = 3 * (y) 时,发生的事情相同 - 中间结果包含与 y 相同的值,但它是该值的副本。

你可以通过将变量作为参数传递给函数,然后在子例程中修改该值来检查这一点,通过引用传递变量:

Sub Test()
    Dim v As Long
    v = 3
    testSub v
    Debug.Print v   ' 这将打印出4
    
    v = 3
    testSub (v)
    Debug.Print v   ' 这将打印出3
End Sub

Sub testSub(ByRef x As Long)
    x = x + 1
End Sub

在第一种情况下,v 的值发生了更改(因为它是按引用传递的)。在第二种情况下,传递并修改的是 v 的中间副本

现在,将一个范围放入括号会产生一些不同寻常的效果:它将使用范围对象的默认属性。你经常会看到类似于 x = Range("A1") 的语句在VBA中(用于Excel),你并不会考虑太多 - 你理所当然地认为 x 将接收单元格 A1 的。但是,如果你仔细考虑,就不太容易看出发生了什么。Range("A1") 是一个具有许多属性的 Range 类型的对象,那么为什么将分配给 x(而不是例如颜色、宽度、字体大小或数字格式)?这是因为在VBA中,对象可以具有默认属性,如果你不指定属性,它们将被使用:写 x = Range("A1") 类似于 x = Range("A1").Value

因此,如果你的范围 wRows 只包含一个单元格,(wRows) 将创建一个单个的值,然后将其作为参数传递。如果它包含多个单元格,它将创建一个二维数组的值 - 这就是你在调试器中看到的情况。再次注意,这些值不再与范围相关联,所以如果你在子例程中修改它们,它们将不会写回到Excel。

一个提示:只要你处理Ranges(而不是范围中存储的值),你应该将变量声明为 Range,而不是 Variant

Dim wRows As Range
Set wRows = ActiveSheet.Range("B9:L7000").Rows.SpecialCells(xlCellTypeVisible)

copyWiederkehrend wRows     ' 这是可以的
copyWiederkehrend (wRows)   ' 会引发错误

Public Function copyWiederkehrend(all As Range)
    MsgBox "HALLLLO"
End Function

为什么会引发错误?因为 (wRows) 会创建一个二维数组的值,不再是一个Range,因此会出现错误(在这种情况下是“424- 需要对象”)。不要通过将所有东西转换为 Variant 来避免这些错误 - 基本上运行时告诉你你做错了什么,这是一件好事。

英文:

A call to a Subroutine in VBA can be done either with

copyWiederkehrend wRows

or with

Call copyWiederkehrend(wRows)

Both commands are identically (some state that it is deprecated to use Call, but that's a different story).
If you call a Function and you are not interested in the result that is returned, the syntax is exactly the same. If you want to get the result of the function, syntax is

x = copyWiederkehrend(wRows)

But when you write

copyWiederkehrend (wRows)

the parenthesis around (wRows) are not used to show the VBA runtime the list of parameters. They are interpreted as "get the value of" (note that now a space is between the function name and the rest of the command).

Writing a statement like x = 3 * (y + 2) will do something similar: The runtime will calculate y + 2 and put this intermediate result somewhere into the memory, multiply it by 3 and ultimately pass this result to x. The point is that once the intermediate result is calculated, it is no longer associated with y. The same happens when you write x = 3 * (y) - the intermediate result contains the same value as y, but it is a copy of that value.

You can check this by passing a variable as parameter to a function by reference and then modify the value within the subroutine:

Sub Test()
    Dim v As Long
    v = 3
    testSub v
    Debug.Print v   &#39; This will print 4
    
    v = 3
    testSub (v)
    Debug.Print v   &#39; This will print 3
End Sub

Sub testSub(ByRef x As Long)
    x = x + 1
End Sub

In the first case, the value of v changed (because it was passed by reference). In the second case, an intermediate copy of v was passed and modified.

Now putting a Range into parenthesis will do something unusual: It will use the Default property of the Range object. You often see statements like x = Range(&quot;A1&quot;) in VBA (for Excel), and you don't think about it - you take it for granted that x will receive the Value of the cell A1. But if you think about it, it is not really easy to see what goes on. Range(&quot;A1&quot;) is an object of type Range with lots of properties, so why is the value assigned to x (and not, for example, the color, the width, the font size or number format)? This is because in VBA objects can have default properties that are used if you don't specify something: Writing x = Range(&quot;A1&quot;) is similar to x = Range(&quot;A1&quot;).Value.

So if your range wRowscontains only one cell, (wRows) will create a single value that then is passed as parameter. If it contains more than one cell, it will create a 2-dimensional array of values - that is what you see in your case in the debugger. Again, note that those values are no longer associated with the range, so if you modify them in the subroutine, they will not be written back into Excel.

One hint: As long as your work with Ranges (and not with the values stored in that range), you should declare your variables as Range, not as Variant.

Dim wRows As Range
Set wRows = ActiveSheet.range(&quot;B9:l7000&quot;).Rows.SpecialCells(xlCellTypeVisible) 

copyWiederkehrend wRows     &#39; This is okay
copyWiederkehrend (wRows)   &#39; Will throw an error.

Public Function copyWiederkehrend(all As Range)
    MsgBox (&quot;HALLLLO&quot;)
End Function

Why this is throwing an error? Because (wRows) will create 2-dimensional array of values, and that is no longer a Range and therefore an error occurs (in this case "424- Object is required). Don't avoid this errors by converting everything to Variant - basically the runtime tells you that you did something wrong and that's a good thing.

huangapple
  • 本文由 发表于 2023年3月7日 20:00:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/75661727.html
匿名

发表评论

匿名网友

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

确定