|
| |
|
|
|
These VBA tools are for use with Excel 97 onwards. |
|
|
|
These tools are in constant use when I am working on
VBA projects,. They were written out of necessity to help me analyse and
document projects, especially those projects I have taken over that are
either sparsely commented or not commented at all. |
|
|
|
Most of the items that you paste into code can be
changed to your own design. In this version it is through the associated
INI file, which you reach from: |
|
|
|
Byg
Tools > INI File |
|
|
|
There is more on the INI file throughout the text and
in its own section towards the end. |
|
|
|
The loader will default to the “C:\BygVba”
directory. You can change this to a location of your choosing by clicking
on the Browse button |
|
|
|
|
|
|
|
|
|
|
|
·
In Excel, on the Tools menu, click Add-Ins.
- If “Byg Tools for VBA” is not listed in the
Add-Ins dialog box, click Browse and locate where you installed
the program (see above)
·
Select the “Byg Tools for VBA” check box.
·
Click on “OK” |
|
|
|
The “Byg Tools “ menu is located on the end of the VBA
menu bar |
|
|
|
|
|
|
|
The “Byg Tools” menu consists of five items. |
|
|
|
|
|
|
|
This section describes the tools that can make your
work clearer to yourself and to anyone who has to follow on after you. |
|
|
|
There are three types of Annotation tools:
|
|
|
|
By putting headers at the beginning of procedures you
give your work structure. They also record significant information such as
what the procedure does, when it was written, when it was amended, and any
other information you consider necessary. |
|
|
|
When you print your work, headers help to give the
pages structure, which can help to make them more manageable and easy to
read. |
|
|
|
To insert a header into a procedure, ensure the
cursor is anywhere within that procedure, then: |
|
|
Byg Tools > Header
|
|
|
The header will be put immediately above the current
procedure. |
|
|
|
Below is an example of a Header installed to a
function.. |
|
|
|
|
''
*********************************************************************
'' Purpose : Check if a
named worksheet exists
'' Written : 11-Apr-2001
by Andy Wiggins, Byg Software Limited
''
Function
WorksheetExist(wsName)
Dim ws
For Each ws In
Worksheets
If UCase(ws.Name)
= UCase(wsName) Then
WorksheetExist = True
Exit Function
End If
Next
WorksheetExist =
False
End Function |
|
|
|
|
You would use an extended header where you might need
to record more information. |
|
|
|
Byg
Tools > Extended Header |
|
|
|
|
''
*********************************************************************
'' Purpose : Check if a
named worksheet exists
'' Written : 11-Apr-2001
by Andy Wiggins, Byg Software Limited
'' Called : Multiple
procedures
'' Reviewed : 12-Apr-2001
by JT
''
Function
WorksheetExist(wsName)
Dim ws
For Each ws In
Worksheets
If UCase(ws.Name)
= UCase(wsName) Then
WorksheetExist = True
Exit Function
End If
Next
WorksheetExist =
False
End Function |
|
|
|
|
The structure of the Header and Extended Header is
defined in the controlling INI file. |
|
|
|
You can edit this section, or add additional lines,
by clicking on: |
|
|
|
Byg
Tools > INI File |
|
|
|
The definition is in the [Header] section of the INI
file. |
|
|
|
[Header]
NoOfLines=3
ExtendedHeader=2
Header01=
*********************************************************************
Header02= Purpose : *
Header03= Written :
#Date# by #Deve#
Header04= Called : ? (Name(s)
of procedures calling the routine)
Header05= Reviewed :
|
|
|
|
The first two variables define the number of lines
used.
- “NoOfLines” variable defines the number of lines
used by the “Header”,
- “ExtendedHeader” variable defines the additional
number of lines used by the Extended Header.
|
|
|
|
The other lines are those inserted into your work. In
this example, the first three lines, defined by the “NoOfLines” variable,
(Header01, Header02, and Header03) are used by the Header. The other two
lines, defined by the “ExtendedHeader” variable (Header04 and Header05)
are additionally used by the Extended Header. |
|
|
|
Overall you can define up to 99 header lines. These
can be used to contain information or be left blank to add white space.
|
|
|
|
The inline variables you can use are described in the
INI file section. |
|
|
|
The “Code Separator” can be used to break up code
into manageable chunks. For example, if your procedures contain several
looping sections, you could benefit from having this separator put in
between the loops. |
|
|
|
Byg
Tools > Code Separator |
|
|
|
The separator is defined in the “Separator” section
as “BrokenLine” |
|
|
|
When you insert new code, or code you want to test,
into a procedure, put it in-between these markers. It reminds you where
the new code started and finished. |
|
|
|
Byg
Tools > Code Markers |
|
|
|
Code Markers can be used with inline variables to
remind you who made the change and when. |
|
|
|
The example shows some code in-between the markers
with inline annotation. |
|
'' ## ANW: 27-Dec-2000 at
14:55
With .Columns(“A:A”).Borders(xlEdgeRight)
.LineStyle =
xlContinuous
.Weight =
xlThin
.ColorIndex =
xlAutomatic
End With
'' ## |
|
|
|
Below is an extract from the INI file. |
|
The “##” can be changed for any set of characters you
choose. The hash symbol (#) is bold and easy for the eye to pick out. |
|
|
|
[Insertion]
Insertion01=## #Init# on
#Date# at #Time#
Insertion02=## |
|
|
|
Insert the system date at the cursor and shift all
remaining code down one line. |
|
|
|
Byg
Tools > Date |
|
|
|
'' 27-Dec-2000 |
|
|
|
Insert information into your code about the changes
you are making. |
|
|
|
Byg
Tools > Date & Reference |
|
|
|
'' ANW, 27-Dec-2001 : AB123
'* |
|
|
INI File extract
|
|
The variable “Current” holds a reference to the
change you are making. Typically this will be the reference used by the
system your company uses to monitor code changes and modifications. |
|
|
|
The line “Reference00” is always the top line. You
can insert up to ten additional lines numbered “Reference01” to
“Reference10”. |
|
|
|
If you do not want to use all the lines you have
defined, change the variable “NoOfLines” to a number between 1 and 10.
|
|
|
|
[Reference]
Current=AB123
NoOfLines=1
Reference00= #Init#,
#Date#
Reference01='*
Reference02='* |
|
|
|
This appends the information to the end of a line of
code. |
|
|
|
Byg
Tools > Date & Reference at EOL |
|
|
|
Case 4:
Result = "Forty "
Case 5:
Result = "Fifty " '' ANW, 28-Dec-2000 : AB123
Case 6:
Result = "Sixty " |
|
|
|
It uses the following information from the INI file’s
“Reference” section. |
|
Current=AB123
Reference00= #Init#,
#Date# |
|
|
|
By default, the current reference will always be
appended to the end. |
|
|
|
Inserts the VBA keyword “Stop” into your code. In
itself, that’s easy enough. With this option you can also insert
additional information, such as the date and time. |
|
|
|
Case 4:
Result = "Forty "
Stop ''28-Dec-2000 at
16:12
Case 5:
Result = "Fifty " |
|
|
|
The “Stop” is always positioned by the left margin of
the page. |
|
|
|
The INI file extract shows the date and time have
been defined |
|
|
|
[Stop]
Stop= #Date# at
#Time# |
|
|
|
Go to a specific line number in the current module.
Especially useful in large modules with several thousand lines of code. |
|
|
|
|
|
|
|
If the line does not exist, you go to the last line
in the module. |
|
|
|
Highlight some code, then select from the menu: |
|
|
|
Byg
Tools > Print VBA Selection |
|
|
|
The formatted code is initially shown in Excel’s
“Print Preview” window. You can either print it from here or, after you
click on “Close”, go to the Microsoft Excel view and print it. |
|
|
|
|
|
|
|
Report on the occurrences of a selected string in the
current project. |
|
|
|
In the first example, I have searched for the string
“Number”. It has found the word “Number” as well as “MyNumber” and “SpellNumber”,
but only the string I searched for, “Number”, is highlighted. |
|
|
|
|
|
In the second
example, I have also searched for the string “Number”. This time it has
only found specific instances of the word. |
|
|
|
|
|
In the INI file you can define the minimum length of
string you want to search for by changing “minlen”. |
|
|
|
The “AllowedChars” variable defines those characters
that can immediately precede and follow a selection when you are searching
for a specific word. |
|
|
|
The “Orient” variable defines whether the output is
in portrait of landscape mode. |
|
|
|
The “Colour” variable defines which colour will be
used to highlight the selection. The number 3 is red. If this is left
blank, it defaults to black. |
|
|
|
[OK_FindAndReport]
minlen=3
AllowedChars=.(){}[]+-=*_£$%^&@~#\/,<>?"'
;Orient=Landscape
Orient=Portrait
Colour=3 |
|
|
|
|
|
VBA is not a tidy creature. It might look like it on
the surface, but it brushes a lot of rubbish under the carpet that you
can’t see which inflates your file size. This option can help you keep
your code size minimised. |
|
|
|
Below is what Microsoft has to say about the subject. |
|
Code
Cleaning
As you
are writing a VBA program, name spaces and other structures are created
invisibly in the background to track and manage your project. As you
rewrite and move code and objects, unused structures build up in your
project. These development leftovers do not affect the average user, since
they don't add up to enough to make a significant difference. But large
and/or complex development efforts can benefit from periodic removal of
these unused structures. Removing these development leftovers is often
called "cleaning your project" or "stripping your code."
Cleaning
a project involves saving all of its modules and forms out to text files,
deleting the old modules and forms, and then reimporting the modules and
forms from the text files. Larger projects may see a 25-percent reduction
in file size once you've cleaned them. This reduction in file size may or
may not make any real difference in the load time or the run time of your
project. |
|
|
|
That might be easy enough if you have one or two
modules to deal with, but major projects have many modules and classes.
The Byg Tools Code Cleaner automates this process so it takes just a
matter of minutes. |
|
|
|
Byg
Tools > Code Cleaner |
|
|
|
Code Cleaner creates a sub-directory off the folder
containing the VBA you want to clean. If you have used this option before,
other sub-directories will already exist and you will be prompted about
this before continuing. |
|
|
|
The sub-directory is given a name based on the file
you are cleaning together with the system date and time. |
|
|
|
The sub-directory holds the following: |
|
·
a copy of the file before any changes are made.
·
temporary files created during cleaning. |
|
|
|
Once the code has been cleaned and you are satisfied
with the result, you can delete the sub-directory that was created. |
|
|
|
After cleaning, recompile your file (Debug >
Compile) and save it. |
|
|
|
The add-in comes with an INI file called “BygVba.Ini”
which is installed in the same directory as the add-in file. The INI file
holds many settings, including date and time formats.
You can access the file from the “Byg Tools” menu. |
|
|
|
Byg
Tools > INI File |
|
|
|
By default, Byg Tools uses Notepad to edit the INI
file. You can change this in the “Editor” section of the INI file. |
|
|
|
|
|
The following inline variables are available to you: |
|
|
|
|
#Date# |
The system date is substituted for the inline string
#Date#. The date format is set in the INI file's [Formats] section. |
|
|
#Time# |
The system time is substituted for the inline string
#Time#. The time format is set in the INI file's [Formats] section. |
|
|
#Deve# |
Details held in the “Name” variable in the INI file’s
“Developer” section are substituted for #Deve#. |
|
|
#Init# |
Details held in the “Inits” variable in the INI
file’s “Developer” section are substituted for #Init#. |
|
|
|
For anyone who has never dealt with an INI, all you
need to know is that it is a text file that you can edit with any text
editor, such as Notepad. |
|
|
|
You can open the INI file for editing by clicking on
“Byg Tools > INI File”. |
|
|
|
A line commencing with a semicolon, ";", is a comment. |
|
|
|
Lines commencing and ending with square brackets are
section headings. |
|
|
|
The remaining lines are variables. The variable name is
on the left of the equals sign, "=", and the value it holds is on the
right. |
|
|
|
Below is the INI file supplied with the Byg Tools
installation. |
|
|
|
; © Byg Software Ltd
2001, 2002
[User]
Name=User name here
Serial=1234567
[Developer]
Inits = ANW
Name= Andy Wiggins, Byg
Software Limited
[Reference]
Current=AB123
NoOfLines=1
Reference00= #Init#,
#Date#
Reference01='*
Reference02='*
; Formats used by data
and time
[Formats]
FormatDate=dd-mmm-yyyy
FormatTime=hh:mm
;If StartLine is 1,
numbering will start from 1.
;Any other number,
numbering starts on the current line number
[Output]
StartLine=0
[Comment]
CommentStyle=''
[Header]
NoOfLines=3
ExtendedHeader=2
Header01=
************************************************************
Header02= Purpose : *
Header03= Written :
#Date# by #Deve#
Header04= Called : ? (Name(s)
of procedures calling the routine)
Header05= Reviewed :
#Date#
[Separator]
BrokenLine = - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - -
[Insertion]
Insertion01=## #Init#:
#Date# at #Time#
Insertion02=##
[OK_FindAndReport]
minlen=3
AllowedChars=.(){}[]+-=*_£$%^&@~#\/,<>?"'
;Orient=Landscape
Orient=Portrait
Colour=3
[PrintVba]
;Orient=Landscape
Orient=Portrait
[Stop]
Stop= #Date# at #Time#
[Editor]
Editor="Notepad"
;Editor="C:\PFE\PFE.EXE" |
11.
Registering
|
|
|
|
If you do not register your copy the following screen
will appear each time you load Byg Tools for VBA. |
|
|
|
|
|
|
|
Once registered, the above screen does not appear. |
|
|
-
When you register your copy of Byg Tools for VBA you
will be sent a serial number and your registration name.
-
Enter these into the
“User” section of the INI file.
-
The INI is installed in the same directory as the
add-in file. You can edit the file with any text
editor, such as Notepad.
|
|
