locked
How can I delete empty VBA modules RRS feed

  • Question

  • Hi,

    I'm reviewing a DB and I'm coming across empty modules.  Is there any way to search for empty modules so I can delete them?

    /Joe

    Thursday, February 2, 2012 12:33 PM

Answers

  • Here is some code from Access MVP Thomas Möller that I modified to show all empty modules in an Access Project. If the Module contains no procedures but only General Declarations it is also considered empty. It produces a list of all empty modules in the Immediate window.

     

    'http://www.team-moeller.de/?Tipps_und_Tricks:Rund_um_die_VB-IDE:Pruefen_ob_Modul_leer_ist
    '---------------------------------------------------------------------------------------
    ' Procedure : ModulesEmptyList
    ' Author    : http://www.team-moeller.de/?Tipps_und_Tricks:Rund_um_die_VB-IDE:Pruefen_ob_Modul_leer_ist
    '           : Modified by Patrick Wood - Gaining Access Technologies http://gainingaccess.net/
    ' Date      : 1/24/2012
    ' Purpose   : Produce a list of empty modules
    '---------------------------------------------------------------------------------------
    '
    Public Function ModulesEmptyList() As String
    On Error GoTo ErrHandle
        
        Dim vbcModule As Object
        Dim lngLine As Long
        Dim strLine As String
        Dim strList As String
        Dim blnEmpty As Boolean
        
        ModulesEmptyList = False 'Default Value
           
        With VBE.ActiveVBProject
            'For each component in the project ...
            For Each vbcModule In .VBComponents
        
                blnEmpty = False 'Assuming the Module is Not Empty.
            
                'If the Module Declaration lines = total count of lines in the Module check it.
                If .VBComponents(vbcModule.Name).CodeModule.CountOfLines _
                    = .VBComponents(vbcModule.Name).CodeModule.CountOfDeclarationLines Then
                    
                    blnEmpty = True 'Assuming the Module is Empty.
                    
                    For lngLine = 1 To .VBComponents(vbcModule.Name).CodeModule.CountOfLines
                        strLine = Trim$(.VBComponents(vbcModule.Name).CodeModule.Lines(lngLine, 1))
                        If Len(strLine) > 0 Then
                            If Left$(strLine, 6) <> "Option" Then
                                If Left$(strLine, 1) <> "'" Then
                                    If Left$(strLine, 3) <> "Rem" Then
                                        blnEmpty = False
                                        Exit For
                                    End If
                                End If
                            End If
                        End If
                    Next lngLine
                    
                    'Add any Empty Module Names to list.
                    If blnEmpty = True Then
                        strList = strList & vbcModule.Name & vbCrLf
                    End If
                End If
            Next vbcModule
        End With
        
        'Build the Return string
        If Len(strList & "") > 0 Then
            strList = "********** List of Empty Modules **********" & vbCrLf & strList
        Else
            strList = "No empty Modules were found."
        End If
        
        ModulesEmptyList = strList
    
    ExitHere:
        On Error Resume Next
        Set vbcModule = Nothing
        Exit Function
    
    ErrHandle:
        Call MsgBox(Err.Number & " " & Err.Description _
    & " In Procedure ModulesEmptyList") Resume ExitHere End Function

    Patrick Wood, Access MVP
    Founder, Gaining Access Technologies http://gainingaccess.net/




    • Edited by PatrickWood Friday, February 3, 2012 3:44 AM Changed ErrorLog to MsgBox
    • Proposed as answer by Matthew Slyman Friday, February 3, 2012 12:28 PM
    • Marked as answer by Model_m Thursday, February 9, 2012 11:27 AM
    Thursday, February 2, 2012 3:08 PM

All replies

  • Do you mean, using VBA? Yes, there is a way. OR, do you mean, manually, using the VBA code editor?
    Matthew Slyman M.A. (Camb.)
    Thursday, February 2, 2012 12:59 PM
  • Here is some code from Access MVP Thomas Möller that I modified to show all empty modules in an Access Project. If the Module contains no procedures but only General Declarations it is also considered empty. It produces a list of all empty modules in the Immediate window.

     

    'http://www.team-moeller.de/?Tipps_und_Tricks:Rund_um_die_VB-IDE:Pruefen_ob_Modul_leer_ist
    '---------------------------------------------------------------------------------------
    ' Procedure : ModulesEmptyList
    ' Author    : http://www.team-moeller.de/?Tipps_und_Tricks:Rund_um_die_VB-IDE:Pruefen_ob_Modul_leer_ist
    '           : Modified by Patrick Wood - Gaining Access Technologies http://gainingaccess.net/
    ' Date      : 1/24/2012
    ' Purpose   : Produce a list of empty modules
    '---------------------------------------------------------------------------------------
    '
    Public Function ModulesEmptyList() As String
    On Error GoTo ErrHandle
        
        Dim vbcModule As Object
        Dim lngLine As Long
        Dim strLine As String
        Dim strList As String
        Dim blnEmpty As Boolean
        
        ModulesEmptyList = False 'Default Value
           
        With VBE.ActiveVBProject
            'For each component in the project ...
            For Each vbcModule In .VBComponents
        
                blnEmpty = False 'Assuming the Module is Not Empty.
            
                'If the Module Declaration lines = total count of lines in the Module check it.
                If .VBComponents(vbcModule.Name).CodeModule.CountOfLines _
                    = .VBComponents(vbcModule.Name).CodeModule.CountOfDeclarationLines Then
                    
                    blnEmpty = True 'Assuming the Module is Empty.
                    
                    For lngLine = 1 To .VBComponents(vbcModule.Name).CodeModule.CountOfLines
                        strLine = Trim$(.VBComponents(vbcModule.Name).CodeModule.Lines(lngLine, 1))
                        If Len(strLine) > 0 Then
                            If Left$(strLine, 6) <> "Option" Then
                                If Left$(strLine, 1) <> "'" Then
                                    If Left$(strLine, 3) <> "Rem" Then
                                        blnEmpty = False
                                        Exit For
                                    End If
                                End If
                            End If
                        End If
                    Next lngLine
                    
                    'Add any Empty Module Names to list.
                    If blnEmpty = True Then
                        strList = strList & vbcModule.Name & vbCrLf
                    End If
                End If
            Next vbcModule
        End With
        
        'Build the Return string
        If Len(strList & "") > 0 Then
            strList = "********** List of Empty Modules **********" & vbCrLf & strList
        Else
            strList = "No empty Modules were found."
        End If
        
        ModulesEmptyList = strList
    
    ExitHere:
        On Error Resume Next
        Set vbcModule = Nothing
        Exit Function
    
    ErrHandle:
        Call MsgBox(Err.Number & " " & Err.Description _
    & " In Procedure ModulesEmptyList") Resume ExitHere End Function

    Patrick Wood, Access MVP
    Founder, Gaining Access Technologies http://gainingaccess.net/




    • Edited by PatrickWood Friday, February 3, 2012 3:44 AM Changed ErrorLog to MsgBox
    • Proposed as answer by Matthew Slyman Friday, February 3, 2012 12:28 PM
    • Marked as answer by Model_m Thursday, February 9, 2012 11:27 AM
    Thursday, February 2, 2012 3:08 PM
  • Hi Patrick, et al;

    I'd make note of the fact that we should be careful to make sure that the HasModule property of a form isn't modified if we need it to be... for example, it may be required if we are syncing events in another module and the event property is set to [Event Procedure].  In such a case, the HasModule property must be true for WithEvents to work, regardless of whether there's code in the module or not.

    Just something to keep tucked away...

    cheers,


    Jack D. Leach (Access MVP)
    UtterAccess Wiki: (Articles, Functions, Classes, VB7 API Declarations and more)
    Thursday, February 2, 2012 3:31 PM
  • Hi Jack,

    For the sake of clarity I believe you are addressing the subject of Event Procedures in Forms being handled by code in a Class Module instead of code within the Form. Before changing the Form's HasModule Property to "No" the Event Properties would have to be checked. And since it only takes one Event Property, On Open for example, to handle a number of Form Events this could be easily overlooked. Thanks for the reminder.


    Patrick Wood, Access MVP
    Founder, Gaining Access Technologies http://gainingaccess.net/
    Thursday, February 2, 2012 5:02 PM
  • Code looks tidy and looks like it should work.

    But I seem to recall that a VBA Reference is required for this...

    Can you remind us of which one?


    Matthew Slyman M.A. (Camb.)
    Thursday, February 2, 2012 10:19 PM
  • I believe it's Microsoft Visual Basic for Applications Extensibilty 5.3


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Thursday, February 2, 2012 10:24 PM
  • There is an Access 2003 download available at Access World Forums


    http://www.access-programmers.co.uk/forums/showthread.php?t=220555&highlight=hasmodule

    in post #11.

    I have only tested it in Access 2003.

    Chris.

    Thursday, February 2, 2012 10:57 PM
  • Actually it is late bound and does not require a reference.
    Patrick Wood, Access MVP
    Founder, Gaining Access Technologies http://gainingaccess.net/
    Friday, February 3, 2012 3:35 AM
  • I've always been a tiny bit confused by the differences between Early/Late binding. Can you share some links on where I can learn about this more systematically?

    Am I right in thinking that this is one of the key lines in terms of late binding?

        Dim vbcModule As Object
    


    In other words, you've defined vbcModule as a generic "Object", and you're going to let Access "bind" this as a specific type of Object (to specific Classes) at run time?

    I'm a little confused about how this is possible without the VBA Reference, whereas Early Binding is only possible WITH the Reference. What are VBA References for then? Merely for code completion and support for optimising compilation? Or do they (as I previously thought), also provide an actual link to the code that does the work at run time?


    Matthew Slyman M.A. (Camb.)
    Friday, February 3, 2012 9:39 AM
  • See whether what Tony Toews has at http://www.granite.ab.ca/access/latebinding.htm helps.
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Friday, February 3, 2012 10:21 AM
  • Thanks Douglas!

    I read the article. Tony Toews is normally very good but I found this article a little confusing, and that appears to be designed only to bring me up to the level I'm already at in my understanding of Early/Late Binding (and not much further than that).

    My present conclusion about this is that my own technique is superior, at least for my purposes:

    • Develop with References installed
    • Run a routine to dump references (see below) and copy these from the Immediate window into a code module
    • Use this routine to refresh references every time the application is started (careful to include reference refreshing routines in a separate module that doesn't require references)
    • Where necessary: Test for success or failure, and modify application behaviour accordingly (rather than waiting for individual lines of code to fail, and suffering unpredictable behaviour as a result).

     

    Public Sub VBAReferences_DumpGUIDs_GenerateRefreshCode() ' USED FOR DEVELOPMENT ONLY!
        Dim ref As Reference
        For Each ref In Application.References
            Debug.Print "    IncludeLibRefFromGUID(" & Chr$(34) & ref.Guid & Chr$(34) & ") ' " & ref.Name
        Next ref
    End Sub
    

     


    Rest of code:

     

    Public Function RefreshVBAReferences() As Boolean
        PLT.UnloadBrokenReferences
        PLT.IncludeAllLibraryReferences ' This Subroutine appears to force compilation of all VBA code.  WHY?  Can this be prevented/ postponed, until after the Subroutine completes?
    End Function
    
    Public Sub IncludeAllLibraryReferences()
    ' INSERT CODE DUMPED FROM IMMEDIATE WINDOW...
    End Sub<br/>
    Public Sub RemoveAllLibraryReferences()
    On Error Resume Next ' GoTo Failed?  Can't see any reason why this would fail...
        Dim i As Long
        For i = Application.References.Count To 1 Step -1
            If Not Application.References(i).BuiltIn Then
                Debug.Print "#ALERT# index.RemoveAllLibraryReferences: " & CStr(i) & ": " & Application.References(i).Name
                Dim RefToRemove As Reference
                Set RefToRemove = Application.References(i)
                Application.References.Remove RefToRemove
                Err.Clear
            End If
        Next i
        Exit Sub
    Failed:
        Debug.Print "#ALERT# index.RemoveAllLibraryReferences: " & Err.Description
        Err.Clear
    End Sub<br/>
    Public Function IncludeLibRefFromGUID(LibraryReferenceGUID As String, Optional Major As Long = 0, Optional Minor As Long = 0) As Boolean
    ' Major: 0, Minor: 0 => Reference the MOST UP-TO-DATE version of the library that is available.
    On Error GoTo Failed
        Application.References.AddFromGuid LibraryReferenceGUID, Major, Minor
        IncludeLibRefFromGUID = True
        Exit Function
    Failed:
        Select Case Err.number
            Case 32813:  ' Reference already included.  No action necessary
            Case Else:
            Debug.Print "#ALERT# " & Err.number & " index.IncludeLibRefFromGUID: " & LibraryReferenceGUID & ": " & Err.Description
        End Select
        Err.Clear
        IncludeLibRefFromGUID = False
    End Function
    


    I write these into a Module called "PLT" for Platform, and then on Form_Open within my main menu, I write the following code:

    Private Sub Form_Open(Cancel As Integer) ' Occurs before Form_Load
        DoCmd.Maximize
        PLT.RefreshVBAReferences ' Replaces AutoExec macro - "RunCode - RefreshVBAReferences ()" 'http://support.microsoft.com/kb/931407 ' AutoExec macro...  Why did it fail?  What to do about it?  Answered here.
    '    APP.on_open ' For application-specific code. Not necessary for example on MSDN...
    End Sub
    


    Does anyone see any problems with this? This works fine for me. I get all the advantages of developing with References/ Early Binding, and my clients are not reporting any problems with weird error messages... The code seems to tolerate a wide variety of environments quite gracefully. And my software gets the small performance boost from Early Binding that Tony Toews mentions in the article you cited...

    I don't see the problem with Early Binding, with this solution. Am I still missing out on something here? Can anyone see any reasons why this would be less reliable than the Late Bound alternative?

     


    Matthew Slyman M.A. (Camb.)
    Friday, February 3, 2012 12:53 PM
  • I suppose what I'm looking for with Early/ Late Binding is to understand how it works on a technical level. For example, I don't understand how VBA understands what code is supposed to be about, if the Reference is not loaded...
    Matthew Slyman M.A. (Camb.)
    Friday, February 3, 2012 1:08 PM
  • You instantiate the object using CreateObject (or GetObject). That points to an entry in the registry which means that it can pick up some of the information it needs. However, it doesn't pick up the "overhead" of the library and all of its definitions.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Friday, February 3, 2012 1:18 PM
  • In my opinion, it's far simpler to develop with Early Binding, then switch to Late Binding when you're done.

    To switch, you need to change all of your variable declarations from, say

    Dim appExcel As Excel.Application
    Dim wkbActive As Excel.Workbook
    Dim wksActive As Excel.Worksheet
    

    to

    Dim appExcel As Object
    Dim wkbActive As Object
    Dim wksActive As Object
    

    (hopefully you didn't use Dim <var> As New <object>!)

    If you instantiated your variables using New, you need to change from

      Set appExcel = New Excel.Application
    

    to

    Set appExcel = CreateObject("Excel.Application")
    

    The big issue is replacing any intrinsic constants you might have used. For instance, the Excel constants all start xl..., and you might have things like xlCenter, xlLeft and xlRight. Constant by constant, you need to determine their value (use the Object Browser, or simply print the value of the constant to the Debug window while you've still got the reference) and set the constants yourself. In other words, you'll need a module (call it basExcelConstants) like

    Option Explicit
    
    Public Const xlCenter As Long = -4108
    Public Const xlLeft As Long = -4131
    Public Const xlRight As Long = -4152

    (This is by far the most time consuming, but once you've done it a few times, you'll have built up a library you can copy from app to app in the future!)

    Make sure you have Option Explit in every single module in your application (I've never figured out why this isn't the default, although you can set the VB Editor up to include it in all modules it creates!). Remove the reference, and do a compile. If it compiles, you're done!



    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Friday, February 3, 2012 1:35 PM
  • You appear to suggest that this "overhead" means late binding is faster than early binding. This is the opposite of what Tony Toews is saying he understands in theory (although he does mention that he has not observed any overall slow-down from using late binding, compared with how things work with early binding). Do I understand this correctly? I supposed that early binding would help somehow with compilation... That it would enable the code to be compiled all at once into more fully optimised machine code. Is this not how it works?
    Matthew Slyman M.A. (Camb.)
    Friday, February 3, 2012 2:32 PM
  • I didn't mean to imply that late binding is faster.

    Like Tony, I've never seen any measurable difference between the two, but it makes sense that Late Binding will take a little bit longer to initialize (because of needing to go through the Registry in order to get the information required to instantiate, as opposed to simply using a library). And even though I'm in a standardized environment (so that I can be assured that everyone will have the same version of Office installed), I always use Late Binding when automating Office from Access simply to make it easier when we upgrade.

    I was being a little flippant when I (mis)used the word "overhead".

    Sorry for any confusion!


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Friday, February 3, 2012 3:27 PM
  • Thank you for the clarification!

    I've never seen a problem with Office upgrades yet, using my technique... The References are deleted every time the application is closed, and restored from GUID every time the application is opened... The only potential issue with this, from what I can see, that late binding might resolve; would be when breaking changes are made in an upgrade to a Referenced library, or when the GUID is changed. Most of the time, I can't see the benefit one way or the other. Perhaps I'm better off with my technique, in that I don't need to rewrite any of my VBA code for deployment?


    Matthew Slyman M.A. (Camb.)
    Friday, February 3, 2012 4:06 PM
  • Here is some code from Access MVP Thomas Möller that I modified to show all empty modules in an Access Project. If the Module contains no procedures but only General Declarations it is also considered empty. It produces a list of all empty modules in the Immediate window.

    Patrick thank you for this answer.

    I just came back from my weekend and found this.  This looks great and I'm going to give it a try.  The EB/LB discussion is also fun.  I struggled with wrapping my head around it for a while and now have switched to LB in my code.  I think that Matthew's solution is extreemly clever and I'm going to keep that in mind for possible future use.

    Regarding the code from Thomas Moller I have two questions:

    1) Is there a way to modify this to deal with empty procedures as well?  I had a bunch of Report_Load procedures and due to some changes they are all now empty.

    2) How would you modify this to also delete the modules / procedures in question?  This is more of an issue regarding the procedures because they are scattered and they are numerous.

    Thank you,

    /Joe

    Sunday, February 5, 2012 7:18 AM
  • I just came back from my weekend and found this.  This looks great and I'm going to give it a try.  The EB/LB discussion is also fun.  I struggled with wrapping my head around it for a while and now have switched to LB in my code.  I think that Matthew's solution is extreemly clever and I'm going to keep that in mind for possible future use.
    Regarding the code from Thomas Moller I have two questions:
    1) Is there a way to modify this to deal with empty procedures as well?  I had a bunch of Report_Load procedures and due to some changes they are all now empty.
    2) How would you modify this to also delete the modules / procedures in question?  This is more of an issue regarding the procedures because they are scattered and they are numerous.
    Thank you,
    /Joe

    Hi Joe
    You can do it with procedures but the code is a bit more complex. You have to make a number of decisions and the possibility of removing something important is too high for my comfort. How closely you want to check your procedures for code or comments makes a difference. Finding the Procedures is not too difficult, but determining if they have anything important in them is more difficult.  The Procedure might have only one line of code but it may call another very important procedure or open a form. It could contain a number of comments in the body you would have to write check for that. Both Modules and Procedures can be deleted but Modules are much easier. Right now I do not have the time to get into writing that much code.
    I will post some code for the time being that has helped me very much.
    '---------------------------------------------------------------------------------------
    ' Procedure : AllCodeToTextFile
    ' Purpose   : Outputs all code in standard and class modules to a text file.
    '           : It uses a Folder path you supply and the CurrentProject.Name
    '           : to create the file name.
    ' Author    : Remou http://forum.lessthandot.com/viewtopic.php?f=95&t=379 10/5/2008
    '           : You can output the code from all components of your project using VBE
    '           : The reference for the FileSystemObject Object is Windows
    '           : Script Host Object Model but it not necessary to add
    '           : the reference for this procedure.
    '           : Modified by Pat Wood 8/18/2011
    ' Example   : Call AllCodeToTextFile("C:\MyCode\", "txt")
    '---------------------------------------------------------------------------------------
    '
    Public Function AllCodeToTextFile(strFolder As String, Optional strFileExt As String = "txt") As Boolean
    On Error GoTo ErrHandle
    
        Dim fso As Object
        Dim fsoFile As Object
        Dim strMod As String
        Dim mdl As Object
        Dim i As Integer
        Dim strFileName As String
        Dim strDate As String
    
        AllCodeToTextFile = False  'Default Value
    
        'Use a unique file name with the database name plus date and time
        strFileName = Replace(CurrentProject.Name, ".", "-")
        strDate = Format(Now(), "yyyy-mm-dd-hh-nn")
    
        Set fso = CreateObject("Scripting.FileSystemObject")
    
        ' Add \ to the end of the folder path if needed
        If Right$(strFolder, 1) = "\" Then
            'Do Nothing
        Else
            strFolder = strFolder & "\"
        End If
    
        ' Put together the file name
        strFolder = (strFolder & strFileName & "-" & strDate & "." & strFileExt)
        'Debug.Print strFolder
    
        'Set up the file.
        Set fsoFile = fso.CreateTextFile(strFolder)
    
        'For each component in the project ...
        For Each mdl In VBE.ActiveVBProject.VBComponents
            'using the count of lines ...
            i = VBE.ActiveVBProject.VBComponents(mdl.Name).CodeModule.CountOfLines
            'put the code in a string ...
            strMod = VBE.ActiveVBProject.VBComponents(mdl.Name).CodeModule.Lines(1, i)
            'and then write it to a file, first marking the start with
            'some equal signs and the component name.
            fsoFile.WriteLine String$(55, "=") & vbCrLf & mdl.Name _
                        & vbCrLf & String$(55, "=") & vbCrLf & strMod
        Next
        
        AllCodeToTextFile = True
        
        MsgBox "Code has been saved to " & strFolder
    
    ExitHere:
        On Error Resume Next
        'Close eveything
        fsoFile.Close
        Set fsoFile = Nothing
        Set fso = Nothing
        Exit Function
    
    ErrHandle:
        MsgBox "Error No: " & Err.Number & " " & Err.Description _
            & vbCrLf & "In procedure AllCodeToTextFile"
        Resume ExitHere
    End Function
    
     

    Patrick Wood, Access MVP
    Founder, Gaining Access Technologies http://gainingaccess.net/
    • Proposed as answer by Bruce Song Thursday, February 9, 2012 9:40 AM
    Sunday, February 5, 2012 8:01 AM
  • Patrick,

    Your blocks of code worked nicely. I still need to figure out how to do this for procedures but we'll see how things go in the meantime.

    /Joe

    Sunday, February 5, 2012 1:47 PM
  • Hi Joe,

    Don't forget that your Forms and Reports can have expressions instead of [Event Procedure] in the Properties Sheet. These can call other Procedures like this: =MyFunction(). As Jack pointed out if you delete the Module the Form or Report these may not work correctly. You need to check for these expressions by using code or manually before deleting the Module or setting the HasModule property to No. 


    Patrick Wood, Access MVP
    Founder, Gaining Access Technologies http://gainingaccess.net/
    Monday, February 6, 2012 3:14 AM
  • Hi Joe,
     
    How about the problem on your side? Does the suggestion help you? If you still show any concern on the problem, just feel free to let us know. 

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, February 9, 2012 9:42 AM