Passing Arguments Between Projects

[Don Wells]

Running Excel 2003 SP3

I have a small procedure that I want to utilize from many projects; so I have placed it in a standard module in Personal.xls.

I have been unable to successfully pass an argument back to the calling procedure.

As a work-around I place the variable in Sheet1 of the Personal file then when the process returns to the calling procedure, I dig it out.

Does anyone have a less cumbersome approach?

T.I.A.

[HansV]

You can change the procedure to a function and use the function's return value. Here is a silly example:

In a standard module in Personal.xls:

Code: Select all

Function MyFunc(x As Long) As Long
    MyFunc = 2 * x + 3
End Function

In a standard module in another workbook:

Code: Select all

Sub MyTest()
    Dim a As Long
    Dim b As Long
    a = 21
    b = Application.Run("Personal.xls!MyFunc", a)
    MsgBox b
End Sub

When you run MyTest, you should get a message box with the number 45.

If you set a reference in your workbook to Personal, in Tools | References... in the Visual Basic Editor, you can use

Code: Select all

    b = Personal.MyFunc(a)

or even

Code: Select all

    b = MyFunc(a)

if the name MyFunc only occurs in Personal.

[Don Wells]

If you set a reference in your workbook to Personal, in Tools | References... in the Visual Basic Editor, you can use

Code: Select all

    b = Personal.MyFunc(a)

Thank you Hans
    That's what I was after.