Reference VBA variables in another workbook

[antoine1]

I am trying to use a variable already declared in a workbook module, in a second workbook.

In the first workbook module I write:

Public StrA As String
 
Sub SetString()
    StrA = "abc"
    MsgBox StrA
End Sub

I can verify in the inspection window that StrA has the correct value after running SetString.
However, when I run the following in the second workbook module:

Sub ShowStr()
    MsgBox StrA
End Sub

I find StrA is empty. I have tried to qualify StrA with the project and modules names, but I get errors stating that an object is needed.
The project name is "VBAProject" in both modules. Shouldn't the PUBLIC declaration allow me to share the variable ?

Thanks for any help !

 

[wigi]

Just explain what you're trying to do - I bet there are better/easier ways than doing things like this.

 

[Richard Schollar]

You would need to set a reference in the workbook doing the calling to the second workbook whose variable is being called (via Tools>References in the VBE).

 

[antoine1]

Thanks to both for the replies.

I have many different spreadsheet reports which download data from SQL procedures. Until now they were all used inside the office and the server ports were closed, so the user and password were hardcoded inside the macros.
Users now want to use them from outside and I would like to require them to enter their user/password, but only once every time they launch Excel. Only the first report would request identification but all the following would use data already acquired.
I understood that having a common Project name and the Public declaration would allow me to share the variables.
Can you suggest other ways ?
Thanks!!

 

[Legacy 98055]

You can set a reference like Richard suggested or you can latebind to the second workbook without the need for a reference. You can use a latebound function that returns a reference or you can obtain a reference with the object datatype and then call all methods...

MsgBox Application.Workbooks("Book1.xls").StrA

This is an example of a function that returns a latebound reference. This would work if called from another workbook...

 

[antoine1]

I'll have to study a little more

With your statement, I get an error stating the object does not admit the property or method. I guess this is because StrA is a declared as a String and not as an Object.

I'll try some more and get back to you if I can't solve it. Thank you !

 

[antoine1]

I'm doing something wrong...
In my first workbook I created a Class module: User

Private pName As String
Public Property Get Name() As String
    Name = pName
End Property
Public Property Let Name(Value As String)
    pName = Value
End Property

and a standard module:

Public CurUser As User
 
Sub SetUser()
    Dim u As String
    Set CurUser = New User
    CurUser.Name = "ABC"
    MsgBox CurUser.Name
End Sub

As before I get the correct value in the MsgBox, and I can see that CurUser exists and has the value "ABC" after I run this.

Then in a second workbook I try:

Sub ShowStr()
    MsgBox Application.Workbooks(1).CurUser.Name
    MsgBox Application.Workbooks(1).Name
End Sub

The first MsgBox gives an error: "Object does not admit this method or property"
The second correctly returns the name of the first workbook.

Can you tell what is wrong ?
Thank you !

 

[Norie]

Couldn't you just store the user's username and password in a central location and reference that?

 

[antoine1]

Hi Norie,
It would be great, but I don't know how to do it... What would the "central location" be? I only know how to use Excel VBA, not .NET or C#...
Thanks.

 

[wigi]

Just use text files or the registry - seems much easier.