test for module...

robfo0

Active Member
Joined
Feb 19, 2002
Messages
281
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 :)
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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/viewtopic.php?topic=5261&forum=2, so im trying another approach.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top