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 !

 

[antoine1]

Ideally I would like the password to disappear completely when the user exits Excel...

 

[Legacy 98055]

There is nothing wrong with your methodology. Creating a public property in a workbook seems ideal for what you need. It's how I would go about it...

Without setting a reference to the workbook (early binding), you must use late binding...

Change: Public CurUser As User
To: Public CurUser As Object

This should solve the problem. I was not aware that you were working with a custom type...

 

[wigi]

Ideally I would like the password to disappear completely when the user exits Excel...

In the VBA Workbook_BeforeClose event of your files, code it so that the text file will be deleted. Kill can be used for that.

 

[antoine1]

Neither was I , I used to program many years ago, with no OO... Just learning.

Changing to Object did not change the error, however I think the problem is in the way I try to get the name. In workbook 1 where there is no scope problem, I get the same "object does not admit the property or method" error.

Sub SetUser()
    Dim u As String
    Set CurUser = New User
    CurUser.Name = "ABC"
 
' These work fine
    MsgBox CurUser.Name
    MsgBox Application.Workbooks(1).Name ' Returns the name of the first workbook
 
 ' This doesn't
    MsgBox Application.Workbooks(1).CurUser.Name
 
End Sub

Thank you !

 

[ZVI]

Hi Antoine1 and welcome to the Board!

For your task the temporary workbook name with name property Visible=False seems pretty enough.

Sub GetWb1Name()
  MsgBox Workbooks("WB1.XLS").Names("WB1_Name")
End Sub

You can add, delete, hide and access to such name from any workbooks code.

But using of Class module of another workbook is interesting task itself.
As I never found the rules for that, below is the result of my experience.

How to work with Class module of another workbook

Let’s assume that there are 2 workbooks.

First workbook: WB1.XLA (already saved).
Unique Name of VBA project (Tools – Properties – General –Project Name): WbWithClass
Class Name: Class1
Code in WB1.XLA standard module:

' In VBA the declaration Function is the same as Public Function
Function FuncSetObjClass1() As Class1
  Set FuncSetObjClass1 = New Class1
End Function

Second workbook WB2.XLS has the reference to WB1.XLA.
To set the reference just Drag & Drop WB1.XLA into WB2.XLS in VBE Project Explorer.
Or for WB2.XLS check up WbWithClass in Tools-References.
Code in WB2.XLS:

' Code in WB2.XLS with reference to WB1.XLA
Sub Test()
  
  Dim NewClassObj As Object
  
  ' WbWithClass is the Project Name of WB1.XLA
  Set NewClassObj = WbWithClass.FuncSetObjClass
  
  ' Now you can use NewClassObj as the Class1 object of WB1.XLA but without tooltips
  
  ' ...
  
  ' Release the memory
  Set NewClassObj = Nothing
  
End Sub

You can work with NewClassObj in WB2.XLS as with Class1 object of WB1.XLA but without tooltips because the late binding is used.
Why it’s the late binding as the direct reference is already set up?
It’s because Instancing property of Class1 is Private as default. Select Class1 in Project Explorer and hit F4 to see its properties.

As opposed to the late binding for the class objects the early binding to the functions is provided in this case – see calling of FuncSetObjClass in the code above.
After typing of the project name WbWithClass and dot char the tooltips list of accessible modules and public functions will appear and you can choose FuncSetObjClass function from that list.

Regards,
Vladimir

 

[antoine1]

Thank you Vladimir, both for the help and for the welcome !
Issue solved!!

Your solution worked just as you said. I had to take one more step because the object was being created and destroyed from the second workbook so I didn't get persistent info. However the trick was getting the reference to workbook1 in workbook2 and also using the project name to qualify the object. I would never have thought of trying this !

As a recap for anyone with the same issue, here are the basic steps I finally followed -using Excel 2003 and XP. (Not sure I understand why everything works, but it does!)

1) Created a workbook and saved it as an Excel add-in (.xla)
2) Opened it in the VBA Editor and changed its project name (I used "SQLUserAccess" in the code below)
3) Added a Class module with the following code

Private pLogin As String
Private pPwd As String
 
Public Property Get Login() As String
    Login = pLogin
End Property
Public Property Let Login(Value As String)
    pLogin = Value
End Property
Public Property Get Pwd() As String
    Pwd = pPwd
End Property
Public Property Let Pwd(Value As String)
    pPwd = Value
End Property

4) Added a standard module:

Public CurUser As SQLUserClass
 
Sub InitialUserSetUp()
    Set CurUser = New SQLUserClass
 
' Just to check, still need to write the request to the user...
    CurUser.Login = "ABC3"
    CurUser.Pwd = "def4"
    MsgBox CurUser.Login & " / " & CurUser.Pwd
End Sub

5) Created a workbook (in which the user data will be used) and created a standard module in VBA Editor.

Sub GetUserLoginData()
    Dim NewLogIn As Object
 
    Set NewLogIn = SQLUserAccess.CurUser
 
    ' Use the info as needed.
    MsgBox NewLogIn.Login & " / " & NewLogIn.Pwd
 
    ' Release the memory
    Set NewLogIn = Nothing
End Sub

6) In the Project Explorer drag and dropped the whole SQLUserAccess project created in step 2 into the new workbook. This created a References subfolder with a reference to the .xla

7) Ran the Sub InitialUserSetUp first and checked the info effectively got to the second workbook as needed. Many more workbooks can be added that will be able to share the info!

8) I still have to write the input of the login and password, but the part that was difficult for me is solved...

Thanks again to all of you !

BTW: while getting this to work and trying to understand a little more, I found this site by Chip Pearson, with a cool solution to the same problem, that follows a completely different approach: http://www.cpearson.com/excel/hidden.htm . It works very well too, I'm not sure which solution I'll implement.

Cheers,
Antoine

 

[ZVI]

Glad to hear you sorted this out, Antoine!
BTW you can rename InitialUserSetUp into Autpen to run it automatically.
Cheers,
Vladimir

 

[gconner]

oo