General Build Questions

[MagicMike]

Hello everyone.

I am quite new to all this but I am enjoying learning and struggling through all of the things that I am trying to accomplish. Being new at this and not having any formal training I am running into a small problem with knowing where all of my code is going. I have 4 different books (2 on VBA in excel and 2 on VB2008) and I'm having difficulty in VBA with where I should put the code for specific things and finding data in large modules without having to scroll through the entire thing.
My question is this: Is there a typical format or structure that is learned when starting to write code? How do I organize the code better? I am trying to use different modules where I can but in large forms the data gets unruley.
I have experience in siemens step 7 ladder logic programming and that seems alot more intuitive but I find myself a bit lost in VB and not knowing where to put things.
Any help or suggestions would be great. There are alot of very talented people on here from what I have seen.

Thanks,

Mike

Also, I searched around and was not sure where to make this post so I hope I was close :doh:

[Dr.K]

Well, sites like this are your best resource. Also, I like Bill Jelen's VBA book.

A common nomenclature is to use lowercase prefixes before every thing you name, so that they type of variable/object is immediately clear.

Integer Variables: intRow, intStartCount, intTemp
String Variables: strUserName, strInputText, strTemp

General Objects: objFS, objAccessApp
Excel Objects: Workbook = wb, Worksheet = wks, Range = rng

For UserForms, I use uf for the form, and ufo for a specific instance:
Dim ufoGetPerf As New ufGetPerf


As for how much organization you need at the Module level, it depends on how large and/or complicated your Project is. For a simple project, keep it all in one Module. If you start getting a lot of code, you'll need to break it up into several Modules, at which point you'll probably want to put common Functions and Subs into thier own modules.

Note that variable scoping becomes much more complicated with multiple modules. When planning out your Modules, you must consider what variables might need to be passed from one to the other.

My main Toolpak AddIn that I built form my department is about 10,000 lines of code... so it has a ton of Modules! Here is how I organized them:

mdl0_Main
mdl1_LipperFeeds
mdl2_DataAcquisition
mdl3_BuckslipTools
mdl4_RequestProcessing
mdl5_UserTools
mdl6_GeneralFunctions
mdl7_WebStockFunctions
mdl8_DataMart_Functions

The number keeps them in the correct order, and the the underscore makes the number and title easier to read.

[Bob Phillips]

There are no hard and fast rules, but my 'style' is to have modules that hold functionally related code, and I don't worry overmuch about how many modules I have.

I tend to keep the userform code module just dealing with the actual form interface, get info from the user, give the result back. Anything that is don to support these input/output functions I put in what I call helper modules, a standard module that is specifically servicing that form.

Ditto with worksheet/workbook event code.

And I use classes, I use them a lot.