Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple shared code library, also how to find spreadsheets with macrosin them
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple shared code library, also how to find spreadsheets with macros in them
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 "felciano" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving shared spreadsheets on a shared drive. | Excel Discussion (Misc queries) | |||
Issues with shared vs. non-shared formulas on somewhat wide spreadsheets | Excel Discussion (Misc queries) | |||
best way to set up macro library so all spreadsheets have access? | Excel Programming | |||
shared spreadsheets | Excel Discussion (Misc queries) | |||
Code to fix: 'can't find project or library' | Excel Programming |