|
|
 |
Do
it Yourself |
 |
-
Subroutines |
| |
A subroutine is a collection of language statements that performs
computations or operations on a worksheet. Subroutines are called
macros in VBA. They are stored in modules visible
in the VBA editor.
Choosing Macro from the Tools menu provides
a partial list of the available subroutines. Only those that
have no arguments are listed because only these may be run directly
from the worksheet. The list below shows some of the subroutines
in the paj_components workbook.
We describe on this page the subroutines included on the paj_components
workbook. These have been included because they have found general
use for some of the add-ins in the collection. |
| |
|
| |
Notice that we have prefaced all
the names of all the subroutines with PAJ_. This helps
to distinguish them from other subroutines that may be in other
workbooks. Excel recognizes a subroutine by its name and all
public subroutines in open workbooks are available to the user.
If two subroutines have the same name it is not always clear
which one will be run. |
VBA Editor
| |
The VBA Editor may reached from
the Macro item on the Tools menu as illustrated in the figure
at the top of this page. The view moves to a new area of Excel
called the VBA Editor where code can be written or revised.
The Project Explorer is visible in that window and
is shown for the paj_components workbook at the left.
All open workbooks and installed add-ins appear in this display.
The only other item shown in this picture is the add_ORMM
add-in.
The expanded list below paj_components is the collection
of objects, forms and modules available. Objects are
worksheets contained in the workbook and the This Workbook
module. The worksheets in paj_components hold the various examples
used in this section. Forms hold dialogs for interacting
with programs. We have no Forms in this workbook, but many can
be found in the other add-ins. Modules hold the subroutines
and functions for the workbook.
|
| |
We first discuss the This
Workbook object because it will place the ORMM item on
the Excel menu. Each of the items on the ORMM menu is connected
to a subroutine which runs when the menu item is selected.
In the following we do not attempt to explain the details of
the subroutine. The user with some experience or a good reference
book may be able to figure them out. To see the code in the
VBA Editor double click on This Workbook in the project
explorer. |
Subroutines in This Workbook
| |
The subroutines in This Workbook
run automatically when the workbook is opened and closed. It
is the means by which we add menus when the workbook is opened
and delete menus when it is closed. The subroutines listed below
add several new items to the OR_MM menu. |
Private Sub workbook_Open() |
| This routine runs automatically
when the workbook is opened in Excel. It calls add_ormm_menu. |
| Sub add_ormm_menu(newMethod, newAction) |
| This subroutine adds OR_MM to the Excel menu and
adds the items specific to the workbook that will appear on this
menu. The arguments to this subroutine, newMethod and
newAction, are the program name that appears at the top of
the list and the subroutine that will be called to place subitems
on the menu. For this example the arguments are: "Do it Yourself"
and "PAJ_checkmenu". |
| Private Function putindex(newMethod) As
Integer |
| When there are several add-in programs listed under
the OR_MM item, this function determines where in the list the
current program will be placed. |
| |
When the workbook is closed, we
want the menu items associated with the workbook to be deleted.
This is accomplished by the subroutines below. |
Private Sub workbook_beforeclose(Cancel
As Boolean) |
| This subroutine runs when the user chooses
to close the workbook and just before the workbook actually
closes. It calls delete_ormm_menu. |
| Private Sub delete_ormm_menu(newMethod) |
| This subroutine deletes the menu item newMethod
and the sub items that appear below it. Part of the task
is accomplished by delete_ormm_items. |
| Private Sub delete_ormm_items(newMethod) |
| This routine actually deletes the items. |
Control Module
| |
Several subroutines have been
found to be useful in a variety of add-ins. They are found in
the Control Module and we list them below with their
purposes and arguments. A subroutine without arguments can be
run from the Tools/Macro menu, from an Excel menu item,
by clicking a button, or by calling the subroutine from another
subroutine of from a function. Subroutines with arguments can
only be called from other subroutines or from functions. |
| Sub PAJ_credits() |
| Displays credits for the programs including
the date of the latest modification. |
| Sub PAJ_checkmenu() |
| This subroutine is called whenever the menu
item "Do it Yourself" is selected. When the menu item
is unchecked it adds the calls PAJ_addmenuitems
to add the subitems. When the menu item is checked
it calls PAJ_deletemenuitems to delete
the subitems. |
| Sub PAJ_addmenuitems(newMethod) |
| The argument newMethod is the main
menu item for which subitems are to be added. This subroutine
adds the menu subitems to the menu. It is here you add the subitems
and the links to the subroutines that are executed when the
subitems are selected by the user. |
| Sub PAJ_deletemenuitems(newMethod) |
| The argument newMethod is the main
menu item for which subitems are to be deleted. This subroutine
deletes the menu subitems from the menu. |
| Sub PAJ_checkworksheet() |
Checks if there is an open worksheet for the workbook |
| Sub PAJ_checkname(my_name, response) |
| Checks a proposed name to see if it is already
used. |
| Sub PAJ_findworksheet(ws, I_OK) |
Find the worksheet named "ws". If it is not found,
call the subroutine that creates it. I_OK is returned FALSE
if ws is not a legal name. |
| Sub PAJ_addworksheet(ws) |
| Adds a worksheet named "ws" to the current workbook. |
| Sub PAJ_makecolor(aa, V_Color) |
| This subroutine colors a range. The argument aa is a worksheet
range. V_color is the index associated with the desired color. |
| Sub PAJ_makeborders(aa, Optional mythick, Optional mycolor) |
| Puts a grid border on a range. aa is the range that is to be
bordered. The optional arguments are mythick, the border thickness,
and mycolor, the border color. |
| Sub PAJ_side_borders(aa, Optional mythick, Optional
mycolor) |
| Puts left and right borders on a range. The argument aa is the
range that is to be bordered. The optional arguments are mythick,
the border thickness, and mycolor, the border color. |
| Sub PAJ_allborders(aa, Optional mythick, Optional mycolor) |
| Puts borders around a range. The argument aa is the range that
is to be bordered. The optional arguments are mythick, the border
thickness, and mycolor, the border color. |
| Sub PAJ_fixformulas() |
| When a workbook is opened with user defined functions that were
created in another computer, the functions will not work. This
routine fixes the formula references to delete any leading paths
to the functions. This remedies function references that appear
first in a formula, but misses function references buried within
a formula. To fix these use the Links command under the
Edit item on the Excel menu. |
| Private Sub newformula(cell, x) |
| Private routine called by PAJ_fixformulas |
| Sub PAJ_addbutton(my_loc, action, title) |
| Places a button on a worksheet in location my_loc.
When clicked the button will call the subroutine whose name is
action. The words placed adjacent to the button are in
title. |
| Sub PAJ_checkempty(locate, down, right,
response) |
| The argument locate is a cell at the upper left
corner of a range to which material will be written. The arguments
down and right specify a rectangular array of cells that will
be filled. If the cells in the specified range are not empty,
the user is questioned if the cells should be written. A response
of no makes response equal to XLcancel. |
| Sub PAJ_makeseries(cell_start, s_length,
s_horizontal, s_prefix, s_first, Optional s_position, Optional
cell_color, Optional border_color) |
| Makes a series in a row or column
- cell_start is the starting cell (upper-left corner)
- s_length is the length of the series
- s_horizontal is true if the series is horizontal, false
if vertical
- s_prefix is the series prefix
- s_first is the first suffix in the series
- s_position must be xlcenter,xlleft or xlright
- cell_color is the background color
- border_color is the cell border color
|
| Sub PAJ_makematrix(cell_start, s_length,
s_width, s_horizontal, Optional cell_contents, Optional s_position,
Optional cell_color, Optional border_color, Optional cell_format) |
Makes a matrix
- cell_start is the starting cell (upper-left corner)
- s_length is the length of the series
- s_width is the width of the matrix
- s_horizontal is true if the matrix is horizontal, false
if vertical
- cell_contents is the default value of the contents
- s_position must be xlcenter,xlleft or xlright
- cell_color is the background color
- border_color is the cell border color
- cell_format is the numerical format of the numbers in the
matrix
|
| Sub PAJ_chgmatrix(old_range As Range, rr,
cc) |
| Changes the size of a matrix stored in old_range.
rr and cc are the new numbers of rows and columns.
When rows or columns are increased in number the current contents
of the matrix is not destroyed. |
Declarations Module
| |
I provide this module to identify
variables that are common to all subroutines and functions.
In paj_components the indices of several common colors
are assigned to variable names. |
Function Module
| |
This module contains the code
for all the functions described on the PAJ
functions page. |
Program Module
| |
This module contains the code
for the subroutines that run when buttons are clicked or menu
items are selected. |
|
