Coding Best Practise

[iheartvba]

Hi Guys,
I have lots of issues with my SQL or other long VBA code when I get an error I can't pinpoint the exact problem immediatley. What I have read on A C# forum is that Parrameters should be used so a problem can be immediatley identified in the code. Now I understand this is VBA & SQL but could I get some suggetions on how I can do coding so I meet the following 2 objectives:

1. Be able to find errors easily myself or have Access identify the error on its own.
2. If A professional programmer wants to continue Edit/Improve/Understand my Database (Apart from reading my 'Comments) they can easily understand the code.

[ADezii]

Hi Guys,
I have lots of issues with my SQL or other long VBA code when I get an error I can't pinpoint the exact problem immediatley. What I have read on A C# forum is that Parrameters should be used so a problem can be immediatley identified in the code. Now I understand this is VBA & SQL but could I get some suggetions on how I can do coding so I meet the following 2 objectives:

1. Be able to find errors easily myself or have Access identify the error on its own.
2. If A professional programmer wants to continue Edit/Improve/Understand my Database (Apart from reading my 'Comments) they can easily understand the code.

It is too broad a Topic to make any specific recommendations on. Are you referring to Syntax, Compile-time, Run-time, or Logic Errors? Several chapters, if not a book itself, can be written on how to detect and deal with Errors of all sorts. Any good reference book on Access/VBA should have a chapter or two that deals specifically with Error Handling and Debugging. I would suggest that this is the best approach to take on the subject.

[iheartvba]

It is too broad a Topic to make any specific recommendations on. Are you referring to Syntax, Compile-time, Run-time, or Logic Errors? Several chapters, if not a book itself, can be written on how to detect and deal with Errors of all sorts. Any good reference book on Access/VBA should have a chapter or two that deals specifically with Error Handling and Debugging. I would suggest that this is the best approach to take on the subject.

Thanks Mate, Thats a great starting point. Cheers

[marcf]

As a tip regarding readability of code I always ask people to do the following:

1) Come up with meaningful variable names.

2) Variable names should contain some kind of reference to their declaration ie for strings Stmyvar, integers, Intmyvar and so on.

3) When adding to your code dont just chuck stuff in at random places try to make your code do one thing at a time.

4) While jumping into subroutines to perform repiticous tasks can cut down on the length of your code sometimes having multiple subroutines branching off just to cut down on 5-10 lines of code can seriously impact readability. If you are set on doing that then I would suggest you keep a separate document that lists each function and subroutine along with their parameters and a summary of what each one does. I find it very handy to do this as if you havent worked on an app in a while you can work alot faster rather than trying to remember what each one does.

If you do those things and above all put decent comments in your code will be very readable and easy for someone else to update.

Error handling is another issue all together, my error handlers are as verbose as possible, I use a set global variables that are set in each function/event that contain useful information to be displayed (along with the error message) in the error handler, exactly what information you set is up to you and depends on your application.

Anyway thats my two pence worth


Marc

[iheartvba]

When I first started coding I thought ... Error handling, I don't need to do that Access already provides me with such usefull errors like, INVALID SQL Statement SELECT... and even if it doesn't if my program has errors, I will get a phone call from the user. Now I think...hmmm this is why an experienced programmer gets paid so much more than an unexperienced programmer. You have given great advice in the post, if only I had understood it and adhered to it sooner... anyways never too late to start.

[puppydogbuddy]

And last, but not least....a great reference source on the subject:

Error Handling and Debugging Tips and Techniques for Microsoft Access, VBA, and Visual Basic (VB6)

[rsmccli]

@puppydogbuddy
That is a very good rundown on error handling. Thanks for that.

[ADezii]

I usually like to deploy a simple, but effective Global Error Logger in my Projects. The following Public Sub-Routine will record the Date/Time, Error Number and Description, and Procedure where the Error originated, to a file named Errors.txt. This File will reside in the same Folder as the *.mdb.

Public Sub LogErrors(dteDate As Date, lngErrNum As Long, strErrDesc As String, strProcedure As String)
Open CurrentProject.Path & "\ErrLog.txt" For Append As #1
Print #1, "****************************************************************************"
Print #1, "Date/Time: " & Now() & vbCrLf & "Number: " & lngErrNum & vbCrLf & _
          "Description: " & strErrDesc & vbCrLf & "Procedure: " & strProcedure
Print #1, "****************************************************************************"
 
Close #1
End Sub

Sample Errors.txt:

****************************************************************************
Date/Time: 10/26/2008 1:34:52 PM
Number: 6
Description: Overflow
Procedure: frmPFDPension_cmdConvertSick_Click()
****************************************************************************
****************************************************************************
Date/Time: 11/26/2008 8:35:48 AM
Number: 16
Description: Expression too complex
Procedure: frmPFDPension_cmdCalculatePension_Click()
****************************************************************************

[missinglinq]

A big problem we see here all the time is in the naming of objects. Retaining the names assigned by Access when an object is created is a surefire path to confusion! A day after creating Text0 or Command2 you won’t remember what they are or what they do. Another programmer following in your footsteps certainly won’t.

Another problem, as I see it, is in what I call the “miniaturization” of code. In the early days, when 20 mb hard drives were the norm, there was a genuine need to have code as compact as possible. Programmers actually had contests to see who could accomplish a task with the least amount of code. But those days are long behind us! We now have the luxury of making code not only functional but legible as well.

OBDoctor.Visible = Me.IsPregnant

is nice and concise, but it’s also rather cryptic.

If Me.IsPregnant = True Then
OBDoctor.Visible = True
Else
OBDoctor.Visible= False
End If

lets you know exactly what is going on. Clarity is always preferable over brevity, in my mind; this is particularly true when offering advice to newbies.

Another example:

CheckIt = IIf(TestMe > 1000, "Large", "Small")

is a concise, legible way of saying

If TestMe > 1000 Then
CheckIt = “Large”
Else
CheckIt = “Small”
End If

But

=IIf(IsNull([arrival date]),"In Progress",IIf(IsNull([completion date]),"In Progress",IIf([arrival date]=[completion date],"Deadline Obtained",IIf(DateDiff("d",[arrival date],[completion date])<=5,"Deadline Obtained",IIf(DateDiff("d",[arrival date],[completion date])>5,"Deadline Missed","false")))))]

is a nightmare to scan, and even worse to debug when things go awry!

And, of course, commenting code, which most of us are loath to do, is still a necessary evil. Even clearly written code can become a muddle six months down the road.


Linq ;0)>

[ADezii]

Clarity is always preferable over brevity, in my mind; this is particularly true when offering advice to newbies.

Excellent words of wisdom by Linq!