Pass variables between workbooks

[bianca.pira]

Hi!
Please help me on this issue.
I have 2 workbooks
' B1 workbook

Public Sub UniqueName(params)

MsgBox params(1)
MsgBox params(2)
params(3) = "OK"

End Sub

' B2 workbook
Sub CallAnotherSubroutine()
' Array required to pass parameters
Dim params(1 To 3) As String

params(1) = "Ahhhh"
params(2) = "daaaa"
params(3) = "wrong"

Application.Run "B1.xls!Module1.UniqueName", params

MsgBox params(3)
End Sub

The last MsgBox params(3) returns value "wrong", but i would like it to return the value "OK", which is assigned in Sub UniqueName.
What changes do i have to do in order to obtain the value given in the other procedure?

Thank you.

 

[Andrew Poulsom]

First change the project name of the B2 workbook from VBAProject to eg Project1. Then set a reference to that project in the B1 workbook (Tools|References). Then try:

' B1 workbook
 
Public Sub UniqueName(params)
    MsgBox params(1)
    MsgBox params(2)
    Project1.params(3) = "OK"
End Sub
 
' B2 workbook
 
' Array required to pass parameters
Public params(1 To 3) As String
 
Sub CallAnotherSubroutine()
    params(1) = "Ahhhh"
    params(2) = "daaaa"
    params(3) = "wrong"
    Application.Run "B1.xls!Module1.UniqueName", params
    MsgBox params(3)
End Sub

 

[bianca.pira]

I tried and still doesn't work, the last message box returns "wrong"

 

[Andrew Poulsom]

I tested the code before I posted it and the MsgBox returned OK. If you have followed my instructions and used the exact code, I don't know why it isn't working for you.

 

[bianca.pira]

I am using Microsoft Office 2003...

The references for VBAProject (B1.xls) that are marked are:

- Visual Basic For Applications
- Microsoft Excel 11.0 Object Library
- OLE Automation
- Microsoft Office 11.0 Object Library
- Project1

And the new code is
Public Sub UniqueName(params)

MsgBox params(1)
MsgBox params(2)
Project1.params(3) = "OK"

End Sub

Is it possible that more references are required? Or the public declaration of the variable?

 

[Andrew Poulsom]

That's fine. What code do you have in the other workbook?

 

[bianca.pira]

Thank u very much.
It worked!!!
I had params declared both as public and in the procedure...
Thank u.