Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: global variable?

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

    Default

    mark, thanks for the code, im awaiting your next post now. But, sorry if i was too vauge, but I already considered looping through the workbooks to test. The problem is, most of the time there are more than 2 workbooks open when the employees are working. there are MANY worksheets we use, and they are constantly used. I could ask the users to close all before opening a customer file, but expecting them to actually do it would be ridiculous on my part

    In anycase, I'm sure your code will help, much appreciated.

  2. #12
    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

    OK, the second example and this is my preferred method.

    From the previous example, replace all of the code in the module (Including the Public oWorkbook as Workbook statement) with this code:



    Public Sub FindWorkbook()
    'Declare Variable locally
    Dim oWorkbook As Workbook

    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 oWorkbook 'Call sub routine in the other module and pass oWorkbook as the workbook the subroutine requires

    End Sub


    Now in replace the cod ein step 5 with this:


    Public Sub DisplayWorkbookName(ByRef oWorkbookDifferentVariableNameButStillSameObject As Workbook)
    MsgBox oWorkbookDifferentVariableNameButStillSameObject.Name
    End Sub


    Now save (as a different name as the first example), close the book and then open it. The code will work just the same. the difference this time is that there are none of those horrible global variables.

    The way the second one works is:

    You declare the oWorkbook as a local variable, but the subroutine "DisplayWorkbookName" now requires a valid workbook to be passed to the subroutine as well (in this case oWorkbook). In the subroutine declaration for "DisplayWorkbookName" we basically tell the subroutine that it is to ask for a workbook to be provided for it to work with. i.e. the Byref....bit. Now I put in a long and stupid name just for emphasis "oWorkbookDifferentVariableNameButStillSameObject" but basically, we could have called this "oWorkbook" as well, but it would be a different variable to module 1's oWorkbook because both of the variables have been declared at the subroutine level.

    Well, I hope this is what you need and I hop that this helps.

    Cheers

  3. #13
    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

    Just saw your post in between posts there.

    When you say that there are going to be multiple files open, are you going to have to go through each one and extract data from all of them, or are you going to be looking for a particular workbook?

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

    Default

    Mark, I will be looking for a particular workbook, BUT, the workbook will have different names (these files i am trying to get data from are saved as customer names). There may be other workbooks open, such as a file search (excel file), or one of many others which i dont want to do anything with

  5. #15
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 15:36, robfo0 wrote:
    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.
    I was unclear if your question was answered or not?
    Anyway, in similiar situations, via shared drives, I have used "handshaking" text files to communicate with other projects. Not as sexy as DDE, .exe, or .dlls but it has always worked for me...
    See a post for Ted in which one single value is used among multiple workbooks on seperate workstations, opened simultaneously...
    http://www.mrexcel.com/board/viewtop...5803&forum=2&3
    Maybe this will help you?
    Tom

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
  •