Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Multiple workbooks, one VBA module?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Houston, TX
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is it possible to have multiple workbooks use the code in a single VBA module?

    I have 18 workbooks that all have identical code behind them. When I need to make a change, I make it in one, the copy the completed code to each of the other workbooks, replacing the old code.

    It sure would be easier to reference an external .bas file, or automatically suck it in before it's used.

    I've tried to figure this out off and on for a year, but now the number of workbooks is growing and it's becoming more important to figure out.

    I'm in Excel 97 on NT 4 (soon to move to 2002 on XP).

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about creating an AddIn ? or, setting a reference to a "main" workbook in each of the 18 books ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Houston, TX
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't know how to create an Add-In, plus I assume each user would then have to add the add-in somehow. I want this to be "transparent" to the users.

    If it's easy to create one and can be transparent, I might try to figure this out.

    I'll see if I can figure out how to reference a "Main" workbook with the code in it.

    I just thought pulling in a lone .bas file would be easier to maintain and manage, but I guess not.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would also like to know how to do this. I think you can store the module somewhere so that it's public and all other workbooks can see the module but I've never figured out how to do this either. Does anybody know?

    Dave

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Houston, TX
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I just thought of a possible problem with the Main workbook solution.

    The workbook would have to be opened, to be able to access the code in it. That could cause a problem if multiple people are using one of the other workbooks at the same time and they also try to open the Main one.

    Plus, they could see another workbook has been opened and may switch to it to close it, causing problems.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Houston, TX
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Wow, I was sure thinking I would get more possible solutions than this!

    Oh well, I guess I'll just keep hunting.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I'm not sure that this is what you're looking for but here it is.
    It's possible to open and get a module that has been exportet fra VBA and placed on ex. a server.
    These code has to be in the workbooks. the 1. one imports a module saved as a *.bas fil at startup and the second deletes it at close down.
    Before using these procedures, you'll need to set a reference in VBA to the VBA Extensibility library. In the VBA editor, go to the Tools menu, choose the References item, and put a check next to "Microsoft Visual Basic For Applications Extensibility" library. This enables VBA to find the definitions of these objects.

    For more info take a look at :
    http://www.cpearson.com/excel/vbe.htm

    Sub Auto_open()
    Dim FName As String
    FName = "C:ProgrammerMicrosoft OfficeOfficeModule13.bas"
    ThisWorkbook.VBProject.VBComponents.Import FName
    End Sub

    Sub Auto_close()
    Dim VBComp As VBComponent
    Set VBComp = ThisWorkbook.VBProject.VBComponents("Module13")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    End Sub

    regards Tommy

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Friend,

    One way and easiest is to have ADDIN but disadvantage of addin is that it will load whenever you work with excel.
    second way (i make use of this) is to open your VBE and then go to tools and here you need to have a REFERENCE to your workbook where you get all your codes placed in a book.

    TOOLS > REFERENCE > BROWSE

    here browse for your workbook.

    you can have your addin referred or your excel workbook referred.

    i hope this will help

    ni****h desai
    http://www.pexcel.com

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Houston, TX
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think the VBE code to include/remove the module is what I want.

    I tried this last year, but could not get it to work (don't remember why now, I just remember the code). But I will try it again.

    Thanks for the link, that might help me figure out what I did wrong last time.

    I want to stay away from AddIn's b/c the only time this other code is needed is when working in these timesheet workbooks. I don't think each person on my team is going to want to add one. So I am wanting to do this all "seemlessly" behind the scenes so the users don't know/care what's going on.

    Thanks again for the comments.

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
  •