Simple shared code library, also how to find spreadsheets with macrosin them

[felciano]

Hello all!

I started learning Excel VBA scripting informally a few years ago, and
have been putting together increasingly complex utility scripts on an
as needed basis. I've now gotten to the point where I've rewritten
this stuff enough that I'd like to collect and modularize some of this
code in a more reusable way. I'm more of a LAMP / *nix developer, so
I'm hoping to be able to come up with a fairly lightweight solution
that doesn't require a heavyweight switch in my development tools
(e.g. switching to Visual Studio). I also have been informal (=lazy)
about tracking the macros I've written, so I have two questions for
you:

#1. Is it possible to somehow scan for or otherwise locate spreadsheet
files that have macros or scripts in them, so that I can collect all
the VBA functions I've written?

#2. If I want to collect all of these VBA functions and snippets into
a centralized location (vs cloning them in each spreadsheet I want to
use), what is the easiest way to do that? I know it is possible to put
this code in separate "Module" files, but I don't really understand
where those are stored or how you would deploy them (I'd like
something I can have on multiple machines, ideally via SyncToy or
something similar).

Thanks!

Ramon

 

[Dennis Tucker]

Per Question #1:
I don't know of any automated way to do this. The stuff I come up with
would be very difficult to centralize. I understand the need though. One
big problem I would have doing this stuff would be how to handle worksheet
functions and their normal interactions with the macro coding. The rest is
not so bad. Just open each workbook, go to the VBA editor and export all of
the userforms/module stuff. You may have to use some creative file naming.
Then in a new workbook, import all of the userforms/module stuff.

Per Question #2:
Excel VBA macros can reach out over a network. One app I created runs a
separate client workbooks on many workstations. They all take commands from
and report status to one central workbook. VB is better for code reuse. In
the VB project file, there are can be network pathnames for code modules and
userforms. In Excel VBA most of the resources are local(contained/embedded)
inside the workbook. Excel VBA can also use external API calls, DLL's and
Class Libraries. The VBA editor can show you the VB coding. However the
coding can be embedded in one or more worksheets, the main workbook, in code
modules, in userforms and the external resources.


Dennis