416-621-9348 cgreaves@chrisgreaves.com
Home Services Products

How To Build an Excel Library (Home); You Gotta Start Somewhere ; Creating A Function Library ; Compiling A Workbook To A Library ; Creating A Macro Library ; Referencing A Library ; A Toolbar Menu ; Making The Macro Library Available ; Some Minor Points ; Maintaining The Utility Function Library ; End-users And The Utility Function Library

Creating A Macro Library

The creation is easy; the design is harder.

Your utility macros will live in modules. How to name the modules and what goes where is tricky, and you may well change your mind down the road.

For now I’m going to create a regular Excel workbook. I’m going to insert a module and name it “modCellData”.

“mod” because it’s a module

“CellData” because it will house functions that manipulate or change the data content of cells.

Is this starting to sound like my library of utility functions? Don’t be surprised. We are partitioning our code into that used directly by end-users and that that can be used, and re-used, by developers.

Here is my proto-library.

Notes:

I have chosen as a file name “Under” (as in “under-the-hood”) and am at version 001 of my macro library file. This may well be the last time the version numbers of my function library and macro library files coincide. One workbook will spawn versions faster than the other.

I have deleted all but one sheet and renamed that with a web site, in case anyone is curious enough to follow up and needs a clue.

The sole function sits in its module, and has an embedded self-testing procedure. Drag the Sub TESTWipeData out of the function, de-comment it and run it to check that the function works, then re-comment it and drag it back inside.

Compiling the workbook code produces a syntax error. This workbook does not yet know about the library; we’ll tell it in the next page.

Referencing A Library

Loading

Toronto and Mississauga, Friday, December 03, 2010 8:45 AM

Copyright © 1996-2010 Chris Greaves. All Rights Reserved.