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

Thread: test for module...

  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 again, i got this code from a http://www.cpearson.com/excel/vbe.htm given to me by al, i have a question about this piece of code:

    this test for the existance of a module in the workbook its run from. My question is, is there a way to modify it to test for the module in ALL the workbooks i have open (say if for example i had 3 open)? heres the code:

    Function ModuleExists(ModuleName As String) As Boolean
    On Error Resume Next
    ModuleExists = Len( _
    ThisWorkbook.VBProject.VBComponents(ModuleName).Name) <> 0
    End Function

    thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    if you select the workbook before each run it would check that active workbook.

  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

    well, its a little more complicated than that. There wont always be another workbook open, and the reason im testing for a module is if there IS another workbook open, it will have different names all the time. any other ideas?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Function ModuleExists(ModuleName As String) As Boolean
    On Error Resume Next
    ModuleExists = Len( _
    ThisWorkbook.VBProject.VBComponents(ModuleName).Name) <> 0
    If ModuleExists = True Then
    MsgBox "Module Exists"
    End If
    End Function


    Sub runfunction()
    ModuleExists ("Cat")
    End Sub

    That would tell you if a module named cat was in your current workbook
    what are you actually trying to do
    it checks the active workbook do you want it to check closed workbooks?

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Public wkbname
    Function ModuleExists(ModuleName As String) As Boolean
    On Error Resume Next

    Workbooks(wkbname).Select
    ModuleExists = Len( _
    ThisWorkbook.VBProject.VBComponents(ModuleName).Name) <> 0
    If ModuleExists = True Then
    MsgBox "Module Exists in " & wkbname
    End If
    End Function


    Sub runfunction()

    wkbname = ("Book1.xls")
    ModuleExists ("Cat")
    End Sub

    or this macro to specify one particular book
    or replace with an inputbox if required

    [ This Message was edited by: brettvba on 2002-04-18 21:57 ]

  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

    the code you posted will ONLY check the workbook that is open for the module. I dont want to test the workbook its run from, i want to test if there are ANY workbooks open that contain the module. I would test for the workbook itself, but like i said the names are always different.

    Ultimately what im trying to do is:
    -I have a workbook (Book1) open which contains data.
    -I want to use a button to open another workbook (Book2).
    -Book2 contains a userfor which starts when it opens, asks a few questions, then opens yet another book (Book3) depending on what the user selected in the userform, then closes Book2
    -i want to copy information from Book1 to Book3.

    the problem is that i cant copy the information until the user selects the appropriate information in the userform in Book2.

    my first idea was to test if book2 was open, but i had some problems with that. i wasnt able to get it worked out in my first idea here: http://www.mrexcel.com/board/viewtop...c=5261&forum=2, so im trying another approach.



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

    Default

    i think i got it although i dont know if this solves my overall problem, i wont know until i get to work to try it out tomorrow. Heres the code i came up with:

    Dim oWkBook As Workbook

    Sub Macro1()
    Dim FromFile As Boolean

    FromFile = False

    For Each oWkBook In Workbooks
    If ModuleExists("opencopy") = True Then
    FromFile = True
    Exit For
    End If
    Next

    If FromFile = True Then
    MsgBox ("code is there!")
    End If

    End Sub

    Function ModuleExists(ModuleName As String) As Boolean
    On Error Resume Next
    ModuleExists = Len( _
    Workbooks(oWkBook.Name).VBProject.VBComponents(ModuleName).Name) <> 0
    End Function

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't know maybe you have got the code wrong on the ok button just a thought

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
  •