Advice on best practices when creating new projects, splititng out code to modules 3

[JasonEnsor]

Hi Guys,

I am just looking for some thoughts and opinions about the structure of my VBA code. In my current projects I am slowly starting to move a lot of my Userform code in to procedures in order to allow re-use of code. In theory my userform will just end up being a events with calls to my procedures. I was just curious as to whether it is worth storing each procedure in it's own module file. Also is it a good idea to have specific error handlers for all my procedures or can I produce a generic error handler that then calls a case statement in a module. This is my attempt at trying to reduce repetition of code and enabling me to make updates only in one place.

One practice I am starting to use is creating a module for global variables, a module for Consts, A general Utilities module that I am populating with reusable code bit by bit and module for general comments regarding the application.

What do you guys do when starting a new project?

Just trying to get ideas to help improve my structure and approach really as I am not bound by any company standards etc... I am trying to get myself in to a proper consistent structure.

Regards

J.

 

[SkipVought]

hi,

I think that much depends on the circumstances. If you have reuseable code, that is code that you would reuse in another workbook or even other applications, you might consider organizing them in one or more modules. I have, for instance, in my PERSONAL.xlsb workbook several such modules. One has 100 or so data access functions that can return ad hoc data to a sheet based on one or more arguments. I have a module with 2 or 3 procedures that are related to CDO mail. I have a module that has dozens of functions and procedures that perform screen scraping via a terminal emulator. In these instances, I can simply open a workbook containing such modules and simply DRAG a module from one project into another.

Most of my workbook applications include data access. So when I begin a new project (workbook), my first module is named DataAcquisition. My second is usually Utilities, which might include filtering, sorting, formating routines. The larger the project, the greater opportunity to reorganize procedures into logical groupings. The logic of logical itself is a matter of purpose and perspective, which leads toward "whatever works for you." As you gain experience, you will develop a cohesive style of constructing a project in the gross, down to code.

Good luck!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

a module for global variables" avoid, avoid, avoid Global variables. Whenever possible, and if there is no other way to handle it. Usually there IS a better way. Use the proper scope of variables: the smaller, the better.

“a module for Consts” yes, yes, yes. The bigger (global) the better. Total opposite of variables.

“reduce repetition of code and enabling me to make updates only in one place.” That’s the best way to go.

“code in to procedures in order to allow re-use of code” Bravo! And make the code as ‘independent’ as possible – don’t pass the Form’s name or Form itself and hard code the names of controls from that Form. Pass the control(s) to the procedure, this way you can name your controls whatever you want on the Form, and it will work just fine.

“it is worth storing each procedure in it's own module file” – depends. If you are going to re-use it, then yes. If not, why bother?


Have fun.

---- Andy

 

[JasonEnsor]

Hi Both,

Thanks for the advice.

I would normally avoid global variables, however I use them this time as

1. I refer to several workbooks throughout my current project and so I thought it would be better to do this as a global declaration then use Set to set the value, as some sheets, change based on a selection from a combo box.

2. I use an instance of Word, so again by setting a global object I am able to pass the reference to procedures after I set the value

3. I store the last row from the workbooks so that when I look to find used range I always have my endpoint set.

4. I create an instance of a Utility Class and A Swimmer Class so that I can reference that data throughout the project

' # Public Workbook Declarations
Public wb_Admin As Workbook
Public ws_SwimmerDetails As Worksheet
Public ws_Extras As Worksheet

Public wb_PaymentsWB As Workbook
Public ws_MonthlyPayments As Worksheet
Public ws_CarriedForward As Worksheet

' # Application Associations
Public wordApp As Word.Application
Public wordDoc As Object

Public thisSwimmer As New A_Swimmer
Public util As New Utility

Public SwimDetailsLastRow As Integer
Public SwimDetailsRange As Range

Public NxtMonth As Integer
Public swimmerFee As Currency
Public PaymentLastRow As Integer
Public NewRecordRow As Integer
Public CFPaymentLastRow As Integer
Public CurrentPaymentRow As Integer

Do you think I should re-evaluate using globals?

Thank you again for the advice, it's always good to get another perspective on coding standards.

As a side note do you guys think it be worthwhile and beneficial looking in to VB6 or VB.Net to help with me coding VBA?

J.

 

[SkipVought]

I generally do not use Public (global) variables.

I agree with Andy about globals and constants.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

I see a variable as global for: UserName, ComputerName, etc.
Something that is set once and you don't change it in your code again.

I would also add:
Set and follow naming standards, Somehitng like (examples):
Dim strName As String
Dim intCounter As Integer
Dim rstMoney As Recordset

txtLastName for TextBox
cboDept for ComboBox
cmdOK for Command Button
frmMain for Form
fraSelect for Frame
optGender for OptionButtons
etc.

Use proper aligning of the code.

Use comments if the code is not obvious.

I have usually this approach:
If (when) I win big and leave my job, somebody has to take over my programs.
Are they going to curse me, or say “I am so glad I got HIS programs to work with”


Have fun.

---- Andy

 

[dhookom]

Gotta give Skip a star. I was hoping to glean some good practice information from him ;-)

Now, if Skip would just make his standard modules available to us Excel wanna-bees.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

@Duane,

Your kind affirmation is appreciated, especially since you are such a giant in Access. I certainly have gleaned much from your posts over the years.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}