global variable?

[robfo0]

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

[daleyman]

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.

[robfo0]

that was it, thanks

[robfo0]

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.

[daleyman]

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?

[robfo0]

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 ]

[daleyman]

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?

[Mark O'Brien]

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 ]

[robfo0]

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

[Mark O'Brien]

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":

Private Sub Workbook_Open()

  FindWorkbook

End Sub

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

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

5. Create a second module and put this code in it:

Public Sub DisplayWorkbookName()

  MsgBox oWorkbook.Name

End Sub

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.