Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: global variable?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    that was it, thanks

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?



    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •