dcsimg
Results 1 to 5 of 5

Thread: [RESOLVED] [VBA in Access and Excel] Two modules with same name

  1. #1

    Thread Starter
    Member
    Join Date
    May 2011
    Posts
    42

    Resolved [RESOLVED] [VBA in Access and Excel] Two modules with same name

    I'm working on improving some VBA code in Excel, but the only time it's ever run is during the process of a huge macro in Access (the macro opens the Excel file and runs the macro in Excel and the Access macro does a bunch of stuff before and after). In Access, we have the following code:

    Code:
    Set ObjXLApp = CreateObject("excel.application")
    Set objXLBook = ObjXLApp.workbooks.Open("whatever.xls")
    ObjXLApp.Visible = True
    ObjXLApp.Run ("SomeMacro")
    ObjXLApp.Quit
    My question is, which version of SomeMacro is run if the Excel workbook has two different modules with this name? They do basically the same thing, but I just want to know which one I can delete out. One is in the code for a worksheet, one is in a module, so my guess is the one in the module is the one that is run. Am I right? Thanks for any help!

    Oh, similar question. In the Access file, there are three modules each with a function: Public Function getPath() As String
    Which one is run when it's called? It is called only in the three modules that it is in. So, does it default to using the one in the same module? The code is all exactly the same, so again, not real important, but I'm trying to clean things up a bit.
    Last edited by ActSciMan; Aug 9th, 2011 at 03:51 PM. Reason: missing "

  2. #2
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: [VBA in Access and Excel] Two modules with same name

    this wil help u
    Code:
    Application.Run ThisWorkbook.Name & "!Module1.temp"
    where 'Module1' is module name and 'temp' is sub name
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  3. #3

    Thread Starter
    Member
    Join Date
    May 2011
    Posts
    42

    Re: [VBA in Access and Excel] Two modules with same name

    Nope, it won't. I know how to make it call a specific one by using the module name. My question is what happens in the given situation described above.

  4. #4
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: [VBA in Access and Excel] Two modules with same name

    as i tested, it said that macro can't be found'
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,267

    Re: [VBA in Access and Excel] Two modules with same name

    Quote Originally Posted by ActSciMan View Post
    My question is, which version of SomeMacro is run if the Excel workbook has two different modules with this name? They do basically the same thing, but I just want to know which one I can delete out. One is in the code for a worksheet, one is in a module, so my guess is the one in the module is the one that is run. Am I right? Thanks for any help!
    As the worksheet is an object, you cannot run the sub etc in it without specifying the worksheet too (either directly as in Sheet(x).SomeMacro , or indirectly by running it from code within the worksheet).

    In this case neither apply, so you are running the module based one.
    Oh, similar question. In the Access file, there are three modules each with a function: Public Function getPath() As String
    Which one is run when it's called? It is called only in the three modules that it is in. So, does it default to using the one in the same module? The code is all exactly the same, so again, not real important, but I'm trying to clean things up a bit.
    It does default to the most "local" one, thus the one in the same module.

    As they are all the same, and declared as Public, you could delete two of the three (if you think it is apt for your situation).

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width