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.
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
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.
Dim Component AsObjectDim Index AsLongForEach Component In Application.VBE.ActiveVBProject.VBComponents
For Index = 1To .CountOfDeclarationLines
Debug.Print .Lines(Index, 1)
For Index = .CountOfDeclarationLines + 1To .CountOfLines
Debug.Print .Lines(Index, 1)
A portal that has dedication and concentration in the development of computer programming and more
resource for help people such as open project, jobs, question & answer, additional tools, and many more.
Collection of various kinds of code programming language from desktop to web programming.