Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Where's the macro? (2003 (any))

    Hi All,

    Today I was looking at a workbook that had about 30 sheets in it. When I went to open it, I got the macro warning since ... there were macros somewhere.

    I went into the VBE and started opening the code module associated with each sheet. No macros in any of them. By that time I had scrolled down the list of items in the project window and saw there was also a Modules entry. I assume that would not be there unless there was an existing macro in it.

    That got me to thinking - is there a way to tell which items have code behind them? Again I go by my assumption for starters. But if I had clicked that one first, that would not preclude there being code associated with any of the sheets or the workbook.

    TIA

    Fred

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where's the macro? (2003 (any))

    While in a code window, do a "Find" (ctrl+f) and have it search the "current project" for "sub" or "function" (without the quotes of course)

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where's the macro? (2003 (any))

    If there is a standard module (not the event modules behind the worksheets and thisworkbook objects) in the workbook it will cause the macro warning message, even if there is no code in the module.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Where's the macro? (2003 (any))

    Legare,

    Thanks - I forgot that.

    So the question remains: if I get a macro warning when opening the file, is there a way to find out where the code is without having to open each sheet's event module, the workbook's event module, and the standard module?

    Fred

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Where's the macro? (2003 (any))

    Thanks for the suggestion but I'm not sure if I'm missing something.

    To do the Find, don't I have to be in a code window? If I'm in a code window, I can tell that there's code in it.

    What I'm trying to do is find out where code might be to avoid having to open a lot of potentially blank code windows. I had a workbook that gave the macro warning and had 30 sheets.

    Fred

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Where's the macro? (2003 (any))

    Fred,
    The point is that once you are in a code window, you can issue the find on the entire project rather than having to open every code window yourself. You can also use pattern matching and just put an asterisk in the find box.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where's the macro? (2003 (any))

    mbarron has already given you the method to search the entire project for code. <post:=656,698>post 656,698</post:>
    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Where's the macro? (2003 (any))

    Legare,

    I think I got it now:
    - open any code window, doesn't matter which one
    - do ctrl+f to invoke the find dialog
    - click the "entire project" radio button (this is the step I was missing)
    - enter a search pattern like sub or function

    This is workable but not necessarily an efficient approach. If a module has lots of subs or other things that match the search pattern, the matching will stop at each one within the module before going on to the next module. This is acceptable although not the most desirable way forward because of the potential for multiple matches within a module (eg, "sub" will match the first line of a sub AND the matching last line "end sub").

    Fred

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where's the macro? (2003 (any))

    If you have turned on "Require variable declaration" in Tools | Options in the Visual Basic Editor (and you should!), all modules should start with the line

    Option Explicit

    This line can occur only once in each module, so you could look for that. However, modules created while "Require variable declaration" was off will not contain this line unless it was manually added later.

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Where's the macro? (2003 (any))

    Hi Hans,

    Excellent point. But as you are probably well aware, I can't control how other people develop their code. This was a case of an inherited workbook. Although the workbook is at work and I'm at home now, I am about 99% certain that Option Explicit was NOT included. Out of the 30+ sheets, only 2 of them had code - hence my original question.

    Fred

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where's the macro? (2003 (any))

    One thing you'd want is some code that checks all VBComponents of a file to see if there is code in them.
    The cod ebelow does work, but if you have require variable declaration switched on, the code will cause adding "Option Explicit" automatically to each object module it opens for the very first time to check if there is code in it. Code like this:

    <pre>Sub LookForCode()
    Dim oComp As VBComponent
    Dim colWithCode As Collection
    Dim sMsg As String
    Set colWithCode = New Collection
    For Each oComp In ActiveWorkbook.VBProject.VBComponents
    Select Case oComp.Type
    Case vbext_ct_ClassModule
    colWithCode.Add oComp
    Case vbext_ct_Document
    If oComp.CodeModule.CountOfLines > 0 Then
    colWithCode.Add oComp
    End If
    Case vbext_ct_MSForm
    colWithCode.Add oComp
    Case vbext_ct_StdModule
    colWithCode.Add oComp
    End Select
    Next
    For Each oComp In colWithCode
    sMsg = sMsg & oComp.Name & vbNewLine
    Next
    MsgBox "This workbook generates macro warnings because of these modules:" & vbNewLine & _
    sMsg

    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Where's the macro? (2003 (any))

    Hi Jan Karel,

    Thanks for the code. This is along the lines I was looking for.

    As you said, though, this will add an Option Explicit line when opening a module if require variable is on (which I always do anyway). So you will now have code in the workbook whereas you might now have had any before. Is there a way to turn it off at the beginning of your code and turn it back on at the end?

    However, your answer gave me an idea - not that I could code it.

    One could export each module to a temp file/folder for deletion later in some "cleanup" code. For a worksheet module (and probably the workbook module but I didn't check), one gets a .cls file (as opposed to a .bas file if I recall for a regular module) with a few lines of info even if the module did not exist. However, this can be easily checked with a find command to see if there are any lines with sub or function or whatever else. I'm not sure if there is a standard size for the .cls of an empty module (mine was 219 bytes) versus one with lines of code (254 bytes for a 3-line macro of sub, msgbox "hello" and end sub); this might depend on the version of VBA.

    Anyway, another idea.

    Thanks again.

    Fred

Posting Permissions

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