> As far as how I want to proceed, I'd like to know how Macro's work. I've taken both a access and excel course several years ago. So I am trainable ... <
How do macro's work? "Macro" is actually a holdover from days long past when one used to automate activities in spreadsheet and word processing software by using a special "macro" language. Basically, most anything you could do from the keyboard or with a mouse had a corresponding keyword in the macro language. So, you could automate activities by creating basically a list of keywords (the list was the "macro"). Microsoft Access still has an actual macro feature but it's been gone from Excel and Word for years.
To automate Excel or Word today, you can use Visual Basic for Applications (VBA). VBA is a full-fledged programming language and, as with macros, VBA can be used to do pretty much anything that can be done from the mouse or keyboard ... and quite a bit more.
It's been a very long time since I've read a book about VBA programming. I first learned VBA from reading the programming books that used to come with the developer versions of Excel. This was before the internet when ink on paper and application help files were the main source of such information. The VBA help in Excel is still very useful. If you Google "Excel VBA tutorial" you should find lots of relevent information. http://www.mrexcel.com/ is a good source of Excel information too. Whenever I get stuck, my first stop is often a google search starting with "Excel" followed by a very few keywords related to what I'm doing. It's rare that I don't find at least a hint (and often I find a complete solution).
VBA is an object oriented programming language. Having chosen some activity you want to automate, much of what you need to figure out is what objects are involved with what you want to do and what methods and properties of those objects need to be used. (Methods "do" something to an object and properties "get" information from an object. In Excel, objects are used to refer to and manipulate parts (either large or small) of an Excel spreadsheet.
For many things, perhaps the easiest way to figure out what objects/methods/properties are involved with a particular action in Excel is to use the macro recorder. In Excel 2003, choose Tools | Macro | Record New Macro and click OK in the Record Macro dialog. Now do something interesting in Excel (start with something basic, like entering a value or a formula in a cell, or changing the formatting of a range of cells). When you're done, choose Tools | Macro | Stop Recording. Then choose Tools | Macro | Macros (you'll see a list of the names of the macros that exist) and click Edit. That will bring up the VBA programming window where you can see VBA code that does what you did. VBA code created by the Macro Recorder can be very inefficient and not very general. But it is a very effective way to discover what objects/methods/properties are needed to accomplish a task.
How helpful is the above? Is it at an appropriate level of detail? If anyone wants a more basic, step-by-step introduction to VBA and object-oriented programming sort of thing, I think that's beyond the scope of what can be done here. For that sort of information, I'd again suggest a Google search for "Excel VBA tutorial".
-Jim Thomas
Edited 6/29/08 by Jim Thomas
Edited 6/29/08 by Jim Thomas | 
