传递给函数的VBA变量被修改

I have passed a variable to a VBA function as an argument but have only referenced the argument in the function, not the original variable. It seems that the original variable is being modified by the function, meaning that it shares the same address space? What is happening here and how do i prevent it from happening?

我已将变量作为参数传递给VBA函数但仅引用函数中的参数,而不是原始变量。似乎原始变量正在由函数修改,这意味着它共享相同的地址空间?这里发生了什么,我该如何防止它发生?

Here is the function: (units, tens, hundreds and thousands are global integer variables)

这是函数:(单位,数十,数百和数千是全局整数变量)

Function Components(qty As Integer, stringSize As Integer)

If qty < stringSize Then
    units = qty

ElseIf qty >= stringSize * 100 Then
    thousands = qty \ (stringSize * 100)
    qty = qty - (thousands * stringSize * 100)
    Components qty, stringSize


ElseIf qty >= stringSize * 10 Then
    hundreds = qty \ (stringSize * 10)
    qty = qty - (hundreds * stringSize * 10)
    Components qty, stringSize

ElseIf qty >= stringSize Then
    tens = qty \ (stringSize)
    qty = qty - (tens * stringSize)
    Components qty, stringSize

End If

End Function

And i call it from another function using

我用另一个函数调用它

Components charQty, 26

Where charQty = 565 when it is passed as an argument to Components and charQty = 19 after components has completed. I determined this by printing the values immediately before and after the function call.

其中charQty = 565,当它作为参数传递给Components时,charQty = 19,在组件完成后。我通过在函数调用之前和之后立即打印值来确定这一点。

I am fairly new to VBA. Any help would be greatly appreciated.

我对VBA很新。任何帮助将不胜感激。

1 个解决方案

#1

Unlike most other languages, the default behaviour for parameters in VBA is for them to be passed ByRef - that means a reference to the original object/variable is passed and thus the original object/variable can be modified by the called function.

与大多数其他语言不同,VBA中参数的默认行为是传递ByRef - 这意味着传递对原始对象/变量的引用,因此原始对象/变量可以被被调用函数修改。

The alternative is to use ByVal, in which case a temporary copy of the value is passed to the called function. Because it is temporary, any changes made are then lost when the function ends.

另一种方法是使用ByVal,在这种情况下,值的临时副本将传递给被调用的函数。因为它是临时的,所以当函数结束时,所做的任何更改都会丢失。

Function Components(ByVal qty As Integer, ByVal stringSize As Integer)

One thing to be careful of ... because an object is always referred to by a reference to the object, a temporary copy of that reference is still going to point to the original object. So it is almost* as if an object is being passed ByRef even when you specify ByVal. (* Except that if the Function were to perform a Set of that parameter to some other object, the original object would still be being pointed to outside of the Function.)

有一点需要注意......因为对象总是通过对对象的引用来引用,该引用的临时副本仍将指向原始对象。因此,即使指定了ByVal,它也几乎就像传递ByRef的对象一样。 (*除非函数要对某个其他对象执行该参数的设置,否则原始对象仍将被指向函数外部。)

MSDN documentation

#1