How can I delete empty VBA modules

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

Answer 1

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.)

Answer 2

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/

Answer 3

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)

Answer 4

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/

Answer 5

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.)

Answer 6

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)

Answer 7

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.

Answer 8

Actually it is late bound and does not require a reference.

---

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

Answer 9

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.)

Answer 10

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)

Answer 11

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.)

Answer 12

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.)

Answer 13

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)

Answer 14

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)

Answer 15

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.)

Answer 16

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)

Answer 17

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.)

Answer 18

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

Answer 19

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/

Answer 20

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

Answer 21

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/

Answer 22

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