- Try to use Integer and Long variables rather than Single, Double or Currency as they use single CPU instructions and are generally much faster.
- Use Single rather than Double variables, if the precision is acceptable as single variables use less memory and sometimes work faster in math expressions.
- Don't use Variants unless you need to store Empty or Null values as each Variant takes 16 bytes, as opposed to 8 bytes for Double or Currency, and is generally slower.
- Add the 'Option Explicit' directive to each module to force variable declaration. Select the Tools->Options->Editor->Require Variable Declaration to set the option for all new modules.
- Never use Single, Double or Variant variables for the controlling variable in For...Next loops.
- Always set value of objects to Nothing when they are finished with to reclaim used resources.
- You can highlight a block of text in the code window and press the Tab key to indent the block or Alt-Tab to unindent it.
- If you use the IIF statement you will need to distribute MSAFINX.DLL with your program.
- Remove leading zeros in a text string by using Val(string).
- Here's an easy way to remove an array element from an unsorted array.
' Element to delete iDelete = 5 ' Number of elements before deletion nElements = UBound(Array) ' Replace iDelete with last item in array Array(iDelete) = Array(nElements) ' Use ReDim Preserve to shrink array by 1 ReDim Preserve Array(LBound(Array) To nElements - 1)
- To easily keep track of global variables make the variables into fields of a type definition. With this code included in a code module, you need to type only Globals. and VB lists them all.
Private Type GlobalVars INIFilePath As String SQLServerConnection As ADOConnection UserName As String Password As String DSN As String End Type Public Globals As GlobalVars