If you fully understand how VB and VBA work, you can often save some statements. This makes your listings more concise and more readable, and indirectly optimizes your program. Here is a short list of the many tricks you can use with this goal.You don’t need to initialize a numeric variable to 0 or a string variable to a null string when the variable is local to a procedure:
Sub MyProc() Dim intValue As Long, strValue As String intValue = 0 ' strValue = "" ' ....End Sub
The default return value for Function is zero or a null string, so you don’t have to explicitly assign such values to the return value, unless you set a different value earlier in the procedure:
Function MyFunc(x As Double) As Long If x > 0 And x ' MyFunct = 0 ' End IfEnd Function
Under VB4 and later versions it’s OK for an error handler procedure to flow into an End Sub or End Function statement. In earlier versions an Exit Sub/Function was necessary. There is a lot of pre-VB4 code still around, that uses the longer approach, similar to the following code:
Sub MyProc() On Error Goto ProcError ' ... ' the body of the procedure ' ...ExitProc: ' Exit SubProcError: ' process the error ' ... Resume ExitProc 'End Sub
If an argument is defined with ByVal you can freely modify it inside the procedure without any risk of side-effects in the main program. This often saves you a local variable:
' the verbose approachSub MyProc(xArg As Long, yArg As Long) ' copy into local vars so that arguments aren't changed Dim x As Long, y As Long x = xArg: y = yArg ' ...End Sub' the concise approachSub MyProc(ByVal x As Long, ByVal y As Long) ' ...End Sub
A similar point can be made with optional arguments. Even if the argument is found to be Missing, you can still use it without the need to declare a local variable. Moreover, assigning a value to it doesn’t require you to use ByVal, because if the argument is missing the calling code won’t be affected if you change it inside your procedure:
' the verbose approachSub MyProc(Optional arg As Variant) ' copy into local var Dim var As Variant If IsMissing(arg) Then var = Now() ' the default value Else var = arg ' use the argument End If ' ...End Sub' the concise approachSub MyProc(Optional var As Variant) If IsMissing(var) Then var = Now() ' ...End Sub
Under VB5 and later versions, you can define non-Variant optional parameters, and you can also define a default value for them. This means that you can re-write old VB4 code and optimize it using the following approach:
' the verbose approachFunction MyFunc(Optional arg As Variant) As Long If IsMissing(arg) Then arg = -1 ' ...End Sub' the more concise approachFunction MyFunc(Optional arg As Long = -1) As Long ' ...End Sub
A simple technique that often lets you save code in a Function is to use the Function’s name as if it were a local variable (and in fact, it is a local variable!). In most cases the shortest version is also faster than the original one. For example:
' the verbose approachFunction AppendStrings(ParamArray args() As Variant) As String Dim i As Integer Dim result As String For i = LBound(args) To UBound(args) result = result & args(i) Next AppendStrings = resultEnd Function' the concise approachFunction AppendStrings(ParamArray args() As Variant) As String Dim i As Integer For i = LBound(args) To UBound(args) AppendStrings = AppendStrings & args(i) NextEnd Function
When you’re calling a Function but you’re not interested in its return value – as it often occurs with API functions – you don’t need a dummy variable just to hold the result. Instead, just call the function as if it were a procedure, which saves a Dim statement and makes you code faster. For example, the ClipCursor API function returns a non-zero value if successful, zero otherwise. If you aren’t interested in trapping errors, or if you are absolutely sure that it can’t raise an error (for example when you pass 0 to release the mouse clipping) you can use the function as if it were a Sub:
Private Declare Function ClipCursor Lib "user32" Alias "ClipCursor" (lpRect As _ Any) As Long' the verbose approachSub StopCursorClipping() Dim res As Long res = ClipCursor(ByVal 0&)End Sub' the concise approachSub StopCursorClipping() ClipCursor ByVal 0&End Sub