General VBA programming advice for users 5

[jrbarnett]

I work in an environment in which I am often asked to troubleshoot VB/VBA code written by other people.
This is the VB/VBA equivalent of the database server post in thread669-1323229. I've started to put together a set of key points and advice for including on my intranet pages to give people a chance to try things out for themselves before approaching me.

1. Ensure that the phrase "Option Explicit" is in the declarations section at the top of each module.
This will help to avoid typographical errors in code while coding as well as picking up non declared variables.
2. Always declare data types for variables, sub and function parameters and function return types. It aids in troubleshooting and also helps reduce application memory requirements.
3. (Access only) Don't use macros. The feature has been deprecated and will not receive new functionality in future versions, in fact it may be removed. Everything that is available as a macro option is available as a DoCmd.RunCommand option.
4. Use the Compile option on the Debug menu (Access/Excel) or Start with Full Compile on the Run menu in (VB 6). This will pick up any syntax errors in your code and make your application run a little faster.
5. When accessing a database backend, generally DAO will be faster for an Access database (MDB/MDE file) or ADO for client/server systems such as SQL Server and MySQL.
6. Always precede DAO or ADO objects with the appropriate type to avoid ambiguity when declaring objects in your code.
7. Always check any data entered by users or read from forms/spreadsheets for sensible values before passing it to the database engine for action.

Can anybody think of anything else worth including?

I've got a mix of apps in Excel VBA, Access VBA and VB 6 with different people writing code in different systems but there's so much crossover between them in general syntax I'm hoping that one document will cover everything.

John

 

[RoyVidar]

Re #3

"New macro features in Office Access 2007

In earlier versions of Access, many commonly used functions could not be performed without writing VBA code. In Office Access 2007, new features and macro actions have been added to help eliminate the need for code."

http://office.microsoft.com/en-us/access/HA012142021033.aspx

I'm afraid that in addition to implementing Multi-Value-Field, the macro feature is not only supported, but enhanced. Gee - ain't we lucky...

Other things

Avoid implicit instantiation

[tt]Dim Something As New SomeObject ' Avoid this[/tt]

Use

[tt]Dim Something As SomeObject
Set Something = New SomeObject[/tt]

Avoid/reduse usage of public/global variables

For DAO and automation - release objects - and do so in correct order.

Roy-Vidar

 

[jrbarnett]

Thanks Roy, will update my FAQ document.

John

 

[SkipVought]

Hi,

Fully quallify objects.

Use the With...End With construct to focus and clarify code for objects.

Avoid using the Activate & Select methods.



Skip,

_[red][/red]

 

[Andrzejek]

[blue]Use meaningful comments in your code.[/blue]

Install and use MZTools (it is worth every penny)

Avoid using:
If Then
ElseIf Then
Elseif Then
End if

If you can use
Select Case

Do not use default property of controls, like lblMyName = “John”


Have fun.

---- Andy

 

[Rofeu]

declare constants even when you only use a constant value only once.

declare constants and variables at the top of your code instead of declaring them just before you need them.

Use indentations with loops, if statements, with/end with, etc.

don't forget to turn ScreenUpdating, DisplayAlerts, Calculation, etc back to it's original settings at the end of your code if it turns them off.

Cheers,

Roel

 

[PHV]

If you automate some office application then use late binding to avoid deployment problems in a mixed versions environment.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[stevexff]

A few more. These aren't VBA-specific, they really apply to any language.
[ul][li]Don't repeat yourself (DRY principle, Pragmatic Programmers). Basically, whenever you find yourself copying the same few lines of code, like in the clauses of a Case statement, extract the code and put it in a function. This means you only have one place to change if it needs maintenence, and tidies up the rest of your code which makes it easier to understand. The same rule applies if you have similar code in different places - can you pull it out into a function with parameters, so you only have to code it once?[/li]
[li]Use sensible names for your functions, subs, and objects. If you have a ComboBox that allows you to select a voltage, call it VoltageSelector, not ComboBox1. If your function calculates sales tax, call it CalcSalesTax rather than Func_22.[/li]
[li]Your brain can only deal with so much information at a time. Typically this means that if your sub or function is bigger than the page, it's too big. Break it up into more manageable chunks. (Exception: if all you are doing is setting a bunch of properties on an object, you can possibly bend this rule a bit). When you are "in the zone", coding away, you will be able to understand what's going on. Six months later when you have to maintain it, you will be asking yourself "What the @*# was I thinking?".

Smaller subs and functions are easier to debug, because there's less to go wrong. Once you've got them working, you can mentally file them under "done" and move on.[/li][/ul]

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[fumei]

Excellent advice here.

Do have a question for Rofeu. If something is used only once...what makes it a constant?

Gerry
My paintings and sculpture

 

[Zathras]

The idea is to avoid "magic numbers"

Instead of coding

frizzlestat = 3

which leaves the maintenance programmer wondering what "3" means, use

const MAX_ROTATIONS = 3
:
:
frizzlestat = MAX_ROTATIONS

Or at the very least...

frizzlestat = 3   [COLOR=green]'set maximum number of rotations[/color]

 

[stevexff]

If something is used only once...what makes it a constant?

The fact that it doesn't change? By making it a constant you guarantee that no future maintainer will misinterpret its function, and change it. Call it a safety feature?

As in "What's wrong with this program?". "Well, the constants aren't, and the variables don't..."

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[CBasicAsslember]

Establish consistent conventions for declaration types. For example:

CONSTANT – CAPITOL
iSomeVariable – integer
sSomeVariable – string

 

[jrbarnett]

Here's one I use (if only it was true for those I now have to maintain:

Within each Sub/Function/Property module, put a block at the top of the code with something like:

'------------------------------------------------
' Name: Name of sub/function/Property
' Author : Your Name
' Date : Date written / last updated
' Called by : Form, report or VB/VBA function names that use it
' Description : Text description of what this does
' Input parameters: Param1 - Text - username
' Param2 - Number - PIN number
' Return value : Return value from a function or Property Get
'-------------------------------------------------

Even if its longer than the function/sub/Property let as a whole.

John

 

[fumei]

Ah, fair enough.

Gerry
My paintings and sculpture

 

[stevexff]

I don't like CBasicAsslember's "Hungarian" variable names. With modern IDEs and combining "Option Explicit" with RoyVidar's suggestion of define-before-set, you could argue that you don't need them. But this is really a matter of personal taste, like line spacing.

But he's absolutely right about capitalising constants - this is a common convention used across a wide range of languages.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[CBasicAsslember]

Steve,

I wasn't suggesting anything specific, they were just examples. I find it quite useful to distinguish what type a variable is declared as I don't have to scroll up to find out.

 

[stevexff]

CBA

I wasn't dissing your style, and I hope you didn't read it that way.

Whole threads have been devoted to styles, and things can get pretty heated over not much at all. My personal favourite is positioning of curlies in Java, perl, C, C#, C++ and perl etc. Should they be

CalcSalesTax () {
   # do stuff
}

or

CalcSalesTax () 
{
   # do stuff
}

? It's made worse by the fact that different editors and IDEs have different default styles, so the various proponents of each all weigh in, and things go downhill from there. The compiler doesn't care, just pick one and stick to it!

If you find Hungarian notation helps you, then it's a good programming practice and deserves to be listed here.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[CBasicAsslember]

NP Steve, I didn't read it that way.

 

[strongm]

>as I don't have to scroll up to find out.

CTRL-i

 

[Remou]

This is a little off-topic and probably more basic than you want, but too often I see "how do I write a control from a report to a table?", or "how do I print a form?" ...

Do not try to force forms to be reports, reports to be queries etc.