Public variables not public

sykes

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,885
Office Version
  1. 365
Platform
  1. Windows
Hi Folks

My public variables are only public at project level, and I don't understand. It almost seems as if I have to set Excel 2007 up differently?

As a simple test I've created 2 workbooks: "Book1" and "Book2".
I've inserted a new module in each book.
In the book1 module I've put:
Code: 
Public book1var As String

Sub set_book1var()
book1var = "b1var"
End Sub

Sub get_vars()
MsgBox "Book1 variable = " & book1var & Chr(10) & "Book2 variable = " & book2var
End Sub

In the book2 module I've put:

Code: 
Public book2var As String

Sub set_book2var()
book2var = "book2var"
End Sub

Sub get_vars()
MsgBox "Book1 variable = " & book1var & Chr(10) & "Book2 variable = " & book2var
End Sub

Next, I ran
Sub set_book1var()......
to assign a value to book1var
Then I ran
Sub get_vars() ....
from module 1.

the message box returned correctly with only a value for book1var, book2var was empty.

Then I ran Sub get_vars() from module 2, and both book1var and book2 vars were empty (i.e. module 1 in Book2 couldn't see book1var, and book2var wasn't assigned any value at that point.

Next, I ran
Sub set_book2var()......
to assign a value to book2var
Then I ran
Sub get_vars() ....
from module 2.
The message box only returned a value for book2var - book1var was empty.

Finally, I ran Sub get_vars() .... from module 1 again, and book1 var was still returned correctly, with book2var still empty.

Any suggestions please?
 
Sort by date Sort by votes
Thanks - will do.

Have done some more research, including in Mr excel, and am getting there slowly.

Will now have a good read through Chip's pages.

Thanks again.
 
Upvote 0
You could pass it as a function.
Put this in Book 1
Code: 
Public Book1Var As String

Function book1Variable() As String
    book1Variable = Book1Var
End Function


Sub set_book1var()
    Book1Var = "b1var"
End Sub

Sub get_vars()
MsgBox "Book1 variable = " & Book1Var & Chr(10) & "Book2 variable = " & Application.Run("Workbook2.xls!book2Variable")
End Sub
and this in Book 2
Code: 
Public Book2Var As String

Function book2Variable() As String
    book2Variable = Book2Var
End Function


Sub set_book2var()
    Book2Var = "book2var"
End Sub

Sub get_vars()
    MsgBox "Book1 variable = " & Application.Run("Workbook1.xls!book1Variable") & Chr(10) & "Book2 variable = " & Book2Var
End Sub
 
Upvote 0
Thanks for the reply Mike - that's now done the trick.

What I was trying to do in the long run, was to make a pop-up calendar for Excel, which would be available to all open workbooks. The reason for accessing another book's variable is so that I can have the calendar and most of the code in the personal workbook and call it all from any other open workbook, with the minimum of fuss.
I might publish it in a separate post so that any visitors to the site can find it more readily, as I think it's something that folk might often look for, but can't find.
As an MVP, you might be kind enough to let me know if it's worth doing so.

I've put a new module in Personal.xlsb (I'm in 2007 - obviously "Personal.xls" for earlier versions).
At the top of the module I've declared the variable to take the date from the calendar control, after it's been clicked:
Code: 
Public dt As Date

I've put a new userform (userForm1) in Personal.xlsb
I've put a calendar control on userform1
I've initialised the calendar thus:
Code: 
Private Sub UserForm_Initialize()
Me.Calendar1.Value = Now()
End Sub
I've put a function in the same module (thanks for your help there):
Code: 
Function global_cal() As Date
UserForm1.Show
global_cal = dt
End Function

Finally, in the calendar's _click event....
Code: 
Private Sub Calendar1_Click()
dt = Me.Calendar1.Value
Unload UserForm1
End Sub

Now all that's needed is to call the function from any other open workbook, so in any open workbook's sheet selection_change event, the user can put:
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Column = 1 And .Row > 2 And .Row < 20 Then .Value = Format(Application.Run("personal.xlsb!global_cal"), "ddd dd-mmm-yyyy")
    End With
End Sub
.......of course changing the column and row references to suit their needs.

Thought it might just be a nice little ready-made solution for folk, if published under an easily find-able title. What say you?

Either way, thanks for your help, as always.

Kind regards

Sykes
 
Upvote 0
Rather than writting a Sub to put in the Personal Macro Workbook, you could cast the userform as a function.

For example, if UserForm1 has a TextBox and two Buttons and this code.
Code: 
Private Sub butOK_Click()
    Me.Tag = TextBox1.Value
    Me.Hide
End Sub

Private Sub butCancel_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.Tag = "False"
End Sub
One can write a function in a normal module, that will invoke the userform when the function is calculated.
Code: 
Function UserEntry() As String
    UserForm1.Show
    UserEntry = UserForm1.Tag
    Unload UserForm1
End Function

Sub test()
    Dim uiVal As String
    uiVal = UserEntry()
    If uiVal = "False" Then
        MsgBox "User canceled"
    Else
        MsgBox "User entered " & uiVal
    End If
End Sub

If a workbook containing the UDF and the userform is saved as an add-in, the userform can be invoked by calling the function from VBA as above, or by using the function in a worksheet formula.
 
Upvote 0
Mike

I've had a looooooooooooong look at this. There seem to be both pros and cons for the addin case. Once the addin's been created then referenced, it seems to behave much as the Personal workbook does - although you can at least directly refer to the functions.
I guess the main benefit would be if one wished to distribute it.

I was interested in the use of the Tag property. Not used that before. It seems to negate the use of a public variable which I guess is good, but presumably will only exist until the parent object is unloaded from memory, or on the flip side the parent object (userform in this case) needs to be kept in memory until the tag is no longer required.

Thanks again
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
What's the best/most efficient way to set Variables for Columns
Replies
9
Views
418
dmt32
D
A
  • Solved
Error passing variables to a userform
Replies
6
Views
182
Ark68
A
J
  • Question Question
Reorganize VB Code into modules and create public variables
Replies
1
Views
560
Artik
Artik
pcc
  • Question Question
Sending email prblem
Replies
2
Views
361
pcc
pcc
A
  • Question Question
Enter formula with VLOOKUP referencing another file
Replies
4
Views
124
Alan_CT06
A

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top