I'm getting more and more requests to write VBA to operate on
people's Excel spreadsheets.

Just stumbling along - trying to make them happy - I've been
writing code that resides within the spreadsheet in question.

Of course, when the user comes back with a clone of that sheet
and wants the same thing or something very similar done; I wind
up having to keep .txt file copies of everything I write.

There's got to be a better way.

Maybe a dedicated spreadsheet of my own that, somehow, I can
point other people's sheets to temporarily for the purpose of
executing VBA routines?

Some kind of compiled module analogous to a .DLL?
--
PeteCresswell

I'd create a new workbook and save it as an addin (PetesUtils.xla).

(Whatever you do, don't call it Personal.xl*. This is a common name that lots
of people use and since you can only have one file with that name open in excel
at any given time, you don't want to have to make your users make a choice!)

Then I'd save it in a specific location -- and tell all the users to save it in
that same location.

C:\ExcelUtils\PetesUtils.xla

You could also store the addin in a common network drive and tell the users to
map to that drive using the same letter--or tell them to use the UNC path
(\\server\sharename\excelutils\petesutils.xla).

By using the same folder, it'll make sharing workbooks between co-workers(?)
much easier. (You won't have to worry about telling the users how to change
links.)

And by saving the workbook as an addin, any UDFs that you write can be called in
a worksheet formula like:

=PetesFunct(a1)
instead of
=PetesUtils.xls!PetesFunct(a1)

After you've distributed the addin to the users with the instructions on where
to store the file, you can tell them to open excel and use:
Tools|Addins|Browse Button
and install your addin.

(And if/when you update your version of the file, you can redistribute it to
each user or just plop it into that common network folder--when it's not in
use!)

But since the workbook is now an addin, the users won't see the subroutines in
the Tools|Macro|Macros list. You'll have to give them a way to run your macros.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

In xl2007, those toolbars and menu modifications will show up under the addins.

==========

All this presumes that you're writing your addin in plain old VBA.

If you're really interested in a compiled version, read Chip Pearson's site:
http://www.cpearson.com/excel/CreatingCOMAddIn.aspx

"(PeteCresswell)" wrote:

I'm getting more and more requests to write VBA to operate on
people's Excel spreadsheets.

Just stumbling along - trying to make them happy - I've been
writing code that resides within the spreadsheet in question.

Of course, when the user comes back with a clone of that sheet
and wants the same thing or something very similar done; I wind
up having to keep .txt file copies of everything I write.

There's got to be a better way.

Maybe a dedicated spreadsheet of my own that, somehow, I can
point other people's sheets to temporarily for the purpose of
executing VBA routines?

Some kind of compiled module analogous to a .DLL?
--
PeteCresswell

--

Dave Peterson

Per Dave Peterson:
C:\ExcelUtils\PetesUtils.xla

That was the ticket. Thanks.

One more question: does anybody have a convenient way to rename
modules?

I resorted to exporting, deleting, changing the "Name" attribute,
then re-importing. Seems like there ought tb a
right-click.props somewhere.... but I can't find it. Even
Googled an explicit reference to same, but couldn't make it work
on my version of (11) of Excel.

No big deal, I guess.... just keep a couple lines of text in my
"BoilerPlate.txt", use it to create a .txt file for a new module,
plug in the name, and import..... but still...
--
PeteCresswell

Left-click on a module to select it.

ViewProperties Window.

Just type in a new name over the old one.


Gord Dibben MS Excel MVP

On Sat, 10 May 2008 20:28:26 -0400, "(PeteCresswell)" wrote:

Per Dave Peterson:
C:\ExcelUtils\PetesUtils.xla

That was the ticket. Thanks.

One more question: does anybody have a convenient way to rename
modules?

I resorted to exporting, deleting, changing the "Name" attribute,
then re-importing. Seems like there ought tb a
right-click.props somewhere.... but I can't find it. Even
Googled an explicit reference to same, but couldn't make it work
on my version of (11) of Excel.

No big deal, I guess.... just keep a couple lines of text in my
"BoilerPlate.txt", use it to create a .txt file for a new module,
plug in the name, and import..... but still...