Tips for golfing in VBA

Exploit the ByRef default when calling subs

It is sometimes possible to use a Sub call in place of a Function to save a few additional characters...

This (87 chars)

Sub a()
b = 0
Do Until b = 5
b = c(b)
Loop
End Sub
Function c(d)
c = d + 1
End Function

can be re-worked to (73 chars):

Sub a()
b = 0
Do Until b = 5
c b
Loop
End Sub
Sub c(d)
d = d + 1
End Sub

Notice this will NOT loop forever, though it appears you are never reassigning b's value.

The above doesn't use a Function call, but instead exploits the ByRef ("By Reference") functionality of the Sub call. What this means is the passed argument is the same variable as in the calling function (as opposed to a ByVal, "By Value" passing, which is a copy). Any modifications to the passed variable will translate back to the calling function.

By default, vba takes all arguments as ByRef, so there is no need to use up characters to define this.

The above example may not translate perfectly for you, depending on the return value of your function. (i.e. returning a different data type than what is passed), but this also allows for the possibility of getting a return value whilst still modifying your original variable.

For example:

Sub a()
b = 0
Debug.Print c(b) ' This will print 0, and b will equal 1.'
End Sub
Function c(d)
c = d
d = d + 1
End Function

Write and run the VBA code in the Immediate Window

The Immediate Window evaluates any valid VBA executable statement. Simply enter a statement in the Immediate Window as you would in the code editor. It quickly executes VBA code and it can save many additional characters because:

1. Putting the question mark (?) at the beginning of the statement tells the Immediate Window to display the result of your code.

2. You don't need to use a Sub and End Sub in your code.

---

Here is the example of VBA code in the Immediate Window to answer PPCG's post with tag code-golf : The Letter A without A

?Chr(88-23);

answered by Joffan.

---

_{Credit images: Excel Campus}

Conditional Checks Before Looping

Some conditional checks are redundant when used in conjunction with loops. For example, a For loop will not process if the starting condition is outside the scope of the running condition.

In other words, this (49 chars):

If B > 0 Then
For C = A To A + B
'...
Next
End If

Can be turned into this (24 chars):

For C = A To A + B ' if B is 0 or less, then the code continues past Next, unabated.
'...
Next