global variable?

robfo0

Active Member
Joined
Feb 19, 2002
Messages
281
Hi everyone,

Ive tried doing searches and looking in help, but i cant seem to get the job done.

I would like to define a variable:

currbook=activeworkbook.name

so that ALL modules in the book can use this variable.

I have tried using:
pulic currbook as string
dim currbook as string

then defining currbook in the workbook_open. Is this not the correct way to do this? Help!

Thanks :)
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
assuming you pasted code is a typo (ie. should read PUBLIC not PULIC), then i think i know your problem...

I have had this kind of topic before, you need to put the PUBLIC declaration in a new module, that is, not a worksheet or "ThisWorkbook" modules. if you right-click one of these objects and do ADD>module then put the PUBLIC declaration in there.

Don't ask me why publics don't work in worksheet or workbook modules, they just don't seem to.
 
Upvote 0
ok, another related question. Is there a way that i can use this variable in modules from different workbooks? I tried putting a public in there too, but that didnt work.
 
Upvote 0
no, i'm sure there is a real techy way of doing it, but PUBLIC will still only keep the scope of the variable within the modules collection of the workbook within which it is declared...

I'm surprised you need such a solution, can you not keep all your modules in the same place for this group of operations?
 
Upvote 0
actually, no, the reason i was doing this is because:

I have another workbook, when this workbook opens, i want it to test if another file is open, and if so, it will copy information from that book to the current one. The only problem is, if there is a workbook open, it will always have a different name (they are customer files all formatted the same), so i cant test for name. Also, i have currbook defined in each customer file when it is opened, so to copy the information to the new file, i was hoping to use workbooks(currbook) etc...

Can you think of another way maybe to do this? thanks


edit: i cant open the worksheet from the customer file becuase theres a userform involved which stops the code :(
This message was edited by robfo0 on 2002-04-19 15:47
 
Upvote 0
there must be a way, if when the workbook opens you get it to loop through the names of the workbooks that are open, comparing them to the current workbook name, you can then copy information to the existing books?
 
Upvote 0
Been reading this post. To go back to the Public variable thingy.

The reason that Public variables can't be seen globally from Worksheet and Workbook modules is because these are Private modules. I believe you can mark standard modules as Public or Private, but standard modules are Public by default.

Once I've my dinner I'll maybe look into the variables being global for all open workbooks. (if it's still necessary)

HTH
This message was edited by Mark O'Brien on 2002-04-19 15:59
 
Upvote 0
YES mark please, i would GREATLY appreciate help on this problem, ive been trying to find a way to get this problem done for weeks now, heh

Thanks for the explanation
 
Upvote 0
daleyman was correct when he talked about looping through the workbooks. I'm going to put together two different examples. The first one uses a global variable, the second one is my preferred method. The reason I dislike global variables is that they take up unnecessary space and they are notoriously difficult to control since anything can change them. For both examples I am assuming that only 2 workbooks are going to be open. 1 workbook is the workbook with the VBA in it, the other workbook is the target workbook with the data that you're going to copy. Since these are just examples, I'll tell you exactly what to do and hopefully you can adapt this to suit your needs. Here we go.

1. Create 2 new workbooks, by default mine were called "Book1.xls" and "Book2.xls", it doesn't matter what they're caled though.
2. In the second book, place this code in "ThisWorkbook":

<pre>
Private Sub Workbook_Open()
FindWorkbook
End Sub</pre>

3. Insert a standard module. (mine is called module1)
4. Put this code in module1:

<pre>
Public oWorkbook As Workbook 'Global variable - bleh


Public Sub FindWorkbook()

If Workbooks.Count = 2 Then 'If there are only two workbooks open
For Each oWorkbook In Workbooks 'Cycle through the workbooks collection
If Not oWorkbook.Name = ThisWorkbook.Name Then 'If the name of the workbook does not equal thisworkbook, then we have found the other book
Exit For
End If
Next
Else
MsgBox "There is only one work book open.", vbInformation, "Workbook Information"
End If

DisplayWorkbookName 'Call sub routine in the other module

End Sub</pre>

5. Create a second module and put this code in it:
<pre>
Public Sub DisplayWorkbookName()
MsgBox oWorkbook.Name
End Sub</pre>

5. Save the book with the VBA and close it down. Keep the other book open.

6. Open the book we've just closed. The message box will display the name of the other open workbook. (oWorkbook is now your Global variable that you requested)

Now, my next post will be very similar, but instead of declaring a global variable, we will be passing the variable from subroutine to subroutine.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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