excel - Personal.xlsb Function result - Stack Overflow-Linux大棚

admin 管理员组

文章数量: 1086019

None of the "similar" questions addressed returning the result of the XLSB function. My issue is that the result of the sucessfully called function is lost when the process gets back to the calling routine. Code:

    Public Function OTHER_TEST(X As Integer) As Integer
         R = R + 1
    End Function

    Sub TEST()
         Call
         Application.Run("PERSONAL.XLSB!OTHER_TEST",99)
         MsgBox(R)
    End Sub

"R" was defined (Dim) as "Integer" in both the calling module as well as in PERSONAL.XLSB. The value of R was correctly exported to OTHER_TEST as "99". OTHER_TEST added 1 making it "100". However upon returning to TEST the value of "R" was "0". What have I missed?

    Public Function OTHER_TEST(X As Integer) As Integer
         R = R + 1
    End Function

    Sub TEST()
         Call
         Application.Run("PERSONAL.XLSB!OTHER_TEST",99)
         MsgBox(R)
    End Sub

Share Improve this question asked Mar 27 at 16:13 KKAIII 74 bronze badges

Add a comment |

1 Answer 1

Sorted by: Reset to default 3

What have I missed? A lot.

Any variable in OTHER_TEST has nothing to do with any variable of the calling module. If you declare a variable R in the personal workbook and a variable R in some other workbook, they are different variables.

A function should return a value. In VBA, this is done by assigning a value to the function name

Public Function OTHER_TEST(X As Integer) As Integer
    OTHER_TEST = X + 1
End Function

When you call the function within the same workbook (or "project" in VBA-terms), you use the function like this:

R = OTHER_TEST(99)
Debug.Print R   ' Will print 100

The function will add 1 to whatever is passed as parameter and return this calculated value back. As you assign the value to R, it is now 100.

you could to something like this (still assuming you are within the same project):

Public Sub OTHER_TEST(X As Integer)
    X = X + 1
End Function

And a calling code could do

R = 99
OTHER_TEST R
Debug.Print R   ' Will print 100

Now OTHER_TEST modifies the passed value because it is passed by reference. Note that OTHER_TEST does not know anything about R, only the address of it (which is a tiny little place of 32bit in your computer memory).

You could also declare the variable R as Global. Now you could modify it directly in OTHER_TEST

Public Sub OTHER_TEST
    R = R + 1
End Function

and call it like this

R = 99
OTHER_TEST      ' Nothing to pass here
Debug.Print R   ' Will print 100

Both practices, modifying a value that is passed by reference and using global variables, are error prone and should be avoided if possible.

However, you want to call a function from a different workbook by using Run.

There are no global variables that can be shared among two workbooks.
You cannot pass arguments by reference when using Run, they are always passed by value. That means a copy of the value is created and passed. Obviously, when you modify that copy, it doesn't change the original value.

So the only clean way is to use a function (like in the first code example). You can call it like this:

R = Application.Run("PERSONAL.XLSB!OTHER_TEST", 99)
Debug.Print R   ' Will print 100

Update (for completeness) Objects in VBA (ranges, worksheets, objects of own defined classes and so on) are always passed by reference. When declaring a parameter as ByVal but having an object type as argument, a copy of the address is passed - which is still the same address of the object. This is of course true also when calling the routine using Run.

本文标签： excelPersonalxlsb Function resultStack Overflow

版权声明：本文标题：excel - Personal.xlsb Function result - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人，转载请联系作者并注明出处：http://roclinux.cn/p/1744080967a2530235.html，本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。

Linux大棚 – 不忘初心的技术博客，浮躁时代的安静角落

excel - Personal.xlsb Function result - Stack Overflow

1 Answer 1

更多相关文章