Source Code

Deconstruction of a VBA Code Module

  • Mark Regal
  • 26 November 2013 at 15:34
  • VB.NET
  • 710
  • 19
  • United States

Author: Mark Regal Language: United States
License: Freeware
Size: 0 KB
Downloads: 20 times


When working with VBA, a useful tool to have is a module which creates extracts of your work. If you produce frameworks, a complete listing of all components and procedures could prove useful in maintaining consistency in the model. Since the incorporation of the Visual Basic Editor into the Microsoft Office application suite, it is possible to have both.

This article is intended to build on the extensibility knowledge base with a focus on deconstructing a VBA code module. The target being a report of all procedures in the active VBA project. Included with this article is a standard VBA module which employs the deconstruction techniques and is a complete extract and reporting package.

Please note that in order to use the codebase in this article, any targeted Office application must support the VBE interface.


Visual Basic Editor (VBE)

The Visual Basic Editor is an interface which is used to used to create, modify, and maintain Visual Basic for Applications objects. The majority of Microsoft Office applications include a built-in VBE interface which is used to access the underlying Microsoft Visual Basic for Applications Extensibility library (VBIDE). The interface is VBE, its implementation is VBIDE, and the code is VBA.

At runtime, an instance of VBIDE is automatically made available through the VBE property of the Application object. Additional type libraries are not required to use the property or the interface it exposes. The only requirement being that the target Office application supports the interface.

As a result, this project does not early bind to the extensibility type library. All extensibility objects are dimensioned as Object and any required constants have been manually re-created in the module included with this article.

With the required objects already in place, the focus can shift to the deconstruction of a VBA code module and the VBE CodeModule object.

CodeModule Object

A VBComponent is a container which enables differentiation between code objects in a VBA project. A component can be a standard module, a class module, a user form, or a document. Each VBComponent contains one code module and the CodeModule object provides access to it.

Combine the CodeModule object with the capabilities of VBA, and the two become a powerful editor. As with most editors, lines can be inserted, deleted, and replaced. The object includes find-find next functionality and lines (or blocks of lines) can be retrieved. In the scope of this project, lines are indexed, counted, and sometimes retrieved.

The CodeModule object resolves a VBA module by lines. The Lines collection begins at line 1 and continues until CountOfLines is reached, the last line in a module. Any single line or blocks of lines can be retrieved using the collection.

A module can also be separated into declarations and procedures. The declarations section begins at line 1 and continues until it reaches CountOfDeclarationLines, the last non-procedural declaration in a module. The procedures section begins at CountOfDeclarationLines + 1 and continues until CountOfLines is reached.


If there are zero procedures in a module, then all lines belong to the declarations section. If there are zero declarations in a module, then all lines belong to the procedures section, providing there are procedures. The following example will extract all of the code in the active project and display it in the immediate window. The declarations and procedures have been provided their own iterators to enable working with a module in sections. It is also using the Lines collection to display the single line with which the index represents.

Public Sub ListCode()

    Dim Component As Object
    Dim Index As Long

    For Each Component In Application.VBE.ActiveVBProject.VBComponents

        With Component.CodeModule

            'The Declarations
            For Index = 1 To .CountOfDeclarationLines
                Debug.Print .Lines(Index, 1)
            Next Index

            'The Procedures
            For Index = .CountOfDeclarationLines + 1 To .CountOfLines
                Debug.Print .Lines(Index, 1)
            Next Index

        End With

    Next Component

End Sub

for complete modul visit this

comments powered by Disqus

Related Code

Why Responsive Images Is So Hard


Allow Single URL Through .htaccess Password Protection

Countable is a JavaScript function

Passing JavaScript variables to PHP

Create Dynamic Bar Graph or Chart in php

Securimage with Image


A Simple PHP Image Resize Code

How Create a Zip File Using PHP


Tag Linked