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
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
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
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
