Good Links that Discuss Proper Programming Technique

**jerseyguy1996** · 11-05-2009, 05:27 PM

Yes I know.....google it. I have and I can't seem to find anything that explains when to put code in modules and when it should go in the "thisworkbook" object or in a worksheet object.......

Here's the thing. I am writing my first vba program (well actually first program ever that actually does something useful). The objective is to take investment mean, stdev, and covariance assumptions and generate a number of efficient frontiers using this data. Then it plots a number of different target portfolios and runs Monte Carlo simulations on each target portfolio taking into account cash flows in and out of the portfolio. Then it uses that information to create a number of different worksheets that it populates with data and pretty charts. The idea is that anyone in my company can then put in their own assumptions, run the program, and out pops a perfectly formatted and ready to pdf report. A task that took a couple of days could now conceivably be done in a few minutes.....of course we would still charge the clients as if it took a few days. So far it has surprisingly come together pretty well and most of the pages of the report can now be generated with the click of a button. Here's the thing. When I go into the code it is a complete mess. One sub after the other all on the "ThisWorkBook" Object. I am sure that it isn't supposed to be a jumbled mess. Is their a sticky on this forum that explains how to use modules effectively to get rid of some of the clutter?

**shg** · 11-05-2009, 06:47 PM

I think if you polled the people that respond regularly on the forum, you'd find most have learned gradually by experience and repetition, by spending time on this and other forums, and supplemented by occasional books; Excel just has a pretty large object model.

I've heard VBA for Dummies is not bad. Walkenbach's Power Programming series is very good, but parts are a little beyond beginner's level.

**Andy Pope** · 11-06-2009, 05:32 AM

There are books on the subject, located via amazon search.
http://www.amazon.co.uk/Structured-F...7499698&sr=1-9

I would second shg's suggestions of the VBA dummies if you are just starting

**royUK** · 11-06-2009, 05:52 AM

It sounds like none of the code should be in ThisWorkBook

**teylyn** · 11-06-2009, 06:05 AM

I'm just working my way through

Steve Roman's Writing Excel Macros with VBA from O'Reilly

http://www.amazon.com/Writing-Excel-...7501328&sr=8-9

Although it's for Excel 2002, and there does not seem to be an edition for a later release, I find it quite useful. The structure works for me and the examples are well chosen and deal with the stuff I'm interested in.

I've also had a closer look at
Microsoft Excel VBA Programming for the Absolute Beginner by Duane Birnbaum and Michael Vine , but was not too impressed. The examples are all around programming games and creating user interfaces on top of Excel, but very little about manipulating user input/output, spreadsheet components etc.

hth

**jerseyguy1996** · 11-07-2009, 09:27 AM

I actually got started using VBA for Dummies and found it very helpful. The rest I have learned through trial and error and lots of google searches. The fact that my entire program is in "ThisWorkbook" and royUK above suggests that it probably shouldn't be means that I really need to learn the proper way to use the developer environment properly and to put the pieces of code where they are supposed to go. I guess I probably need to break out each sub into its own module but that is purely guessing on my part.

**DonkeyOte** · 11-07-2009, 11:16 AM

I keep meaning to get a book myself as I'm also self taught (lots (lots) of trial and error) and I often feel my lack of knowledge on the fundamentals catches me out at times... I've been recommended PED but that's pretty advanced I think so I may opt for something a little more middle-ground.

Regards your comment...

The fact that my entire program is in "ThisWorkbook" and royUK above suggests that it probably shouldn't be means that I really need to learn the proper way to use the developer environment properly and to put the pieces of code where they are supposed to go. I guess I probably need to break out each sub into its own module but that is purely guessing on my part.

I always tend to get my terminology mixed up because I try to categorise things in such a way as they make sense to me rather than concentrating on them being "technically correct"... so please forgive any technical inaccuracies in the remainder... but basically I would say - try to think of a VBE project in terms of being a collection of Objects & "standard" Modules... (ignoring Class Modules/UserForms etc... for the time being)

In general the Objects in VBE are ThisWorkbook, Sheet1 etc
In an XL file Worksheets are Objects, the Workbook itself is an Object.

On that basis I (and this personal preference) try and keep these Objects in VBE clear of code for all but Object driven Events ...
ie ThisWorkbook would contain only code related to Workbook Level events (Open, Close, generic Sheet event etc...)
Sheet1 would only contain code specific to Events occurring on Sheet1 (Change, Calculate, DoubleClick etc...)

Chip Pearson (surprise surprise) has a nice page on the subject of Events: http://www.cpearson.com/excel/Events.aspx

All other non-event driven code would reside in standard Modules (inserted via Insert Menu).

Regards my non-event driven code residing in Modules - I generally keep my Modules to the odd routine here and there (else they can be a nightmare to debug with so much code going on in one Module - not that mine ever debug of course!). On that basis I try to keep the Modules specific to a given task ... if I have 2 routines related to the same task they may share the Module.