Take the 2-minute tour ×
Programming Puzzles & Code Golf Stack Exchange is a question and answer site for programming puzzle enthusiasts and code golfers. It's 100% free, no registration required.

Similar to this, this, and this question...

What general tips do you have for golfing in VBA? I'm looking for ideas that can be applied to code golf problems in general that are at least somewhat specific to VBA (e.g. "remove comments" is not an answer). Please post one tip per answer.

While I have worked with other languages, I'm strongest in VBA, and I don't see many golfers using VBA on this site.

share|improve this question
    
Converted to Community Wiki as per policy. –  dmckee Mar 16 '12 at 15:24
    
Sorry that wasn't automatic on my part! –  Gaffi Mar 16 '12 at 15:26
    
No trouble. Actually, they've taken the power to make questions CW away from users (you can still do answers, I think). You could flag for moderator attention, but as little activity as CodeGolf gets that is hardly necessary. –  dmckee Mar 16 '12 at 15:28
    
VBA is a relatively verbose language with few syntax shortcuts. If you're going for best score, VBA may not be a good choice. If you're looking to hone your skills, more power to ya. –  GigaWatt Mar 20 '12 at 17:55
    
@GigaWatt Honing my skills it is. Actually since playing around with different challenges, I've already picked up a few new tricks for working with VBA! I don't expect to win any real code-golf challenges with VBA, but it's good practice. :-) –  Gaffi Mar 20 '12 at 17:59
add comment

11 Answers 11

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, 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
share|improve this answer
add comment
up vote 4 down vote accepted

Variable Declaration

In most cases in VBA, you can leave out Option Explicit (often omitted by default, anyway) and skip Dim'ing many of your variables.

In doing so, this (96 Chars):

Option Explicit

Sub Test()
Dim S As String
Dim S2 As String
S = "Test"
S2 = S
MsgBox S2
End Sub

Becomes this (46 chars):

Sub Test()
S = "Test"
S2 = S
MsgBox S2
End Sub

If you need to use certain objects (for example, arrays), you may still need to Dim that variable.

share|improve this answer
add comment

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
share|improve this answer
add comment

Reducing If Statements

When assigning a variable using a conditional If ... Then ... Else check, you can reduce the amount of code used by eliminating the End If by putting the entire check on one line.

For example, this (37 chars):

If a < b Then
c = b
Else
c = a
End If

Can be reduced to this (30 chars)

If a < b Then c = b Else c = a

If you have more than one nested conditional, you can minimize them this way as well:

If a Then If b Then If c Then Z:If d Then Y:If e Then X Else W Else V:If f Then U 'Look ma! No "End If"!

Note the : allows you to add more than one line/command within an If block.

In simple cases like this, you can usually also remove the Else by setting the variable in before the If check (25 chars):

c = a
If a < b Then c = b

Even better, the above can be further reduced to this using the IIf() function (20 chars):

c = IIf(a < b, b, a)
share|improve this answer
add comment

Simplify built-in functions

When using certain functions frequently, reassign them to a user-defined function.

The following code (127 chars) can be reduced from:

Sub q()
w = 0
x = 1
y = 2
z = 3
a = Format(w, "0.0%")
b = Format(x, "0.0%")
c = Format(y, "0.0%")
d = Format(z, "0.0%")
End Sub

to (124 chars):

Sub q()
w = 0
x = 1
y = 2
z = 3
a = f(w)
b = f(x)
c = f(y)
d = f(z)
End Sub
Function f(g)
f = Format(g, "0.0%")
End Function

Combining this with the ByRef trick, you can save even more characters (down to 114):

Sub q()
w = 0
x = 1
y = 2
z = 3
a = f(w)
b = f(x)
c = f(y)
d = f(z)
End Sub
Sub f(g)
g = Format(g, "0.0%")
End Sub

Do this judiciously, as VBA takes up a lot of characters to define a Function. The second code block would actually be larger than the first with any number fewer Format() calls.

share|improve this answer
add comment

Ending For Loops

When using For loops, a Next line does not need a variable name (though it is probably better to use in normal coding).

Therefore,

For Variable = 1 to 100
    'Do stuff
Next Variable

can be shortened to:

For Variable = 1 to 100
    'Do stuff
Next

(The savings depends on your variable name, though if you're golfing, that's probably just 1 character + 1 space.)

share|improve this answer
1  
2 characters, don't forget to count the space! –  11684 Mar 11 '13 at 21:12
add comment

Multiple If .. Then checks

As in other languages, multiple If checks can usually be combined into a single line, allowing for the use of And/Or (i.e. &&/|| in C and others), which in VBA replaces both a Then and an End If.

For example, with a nested conditional (93 chars):

'There are MUCH easier ways to do this check (i.e. a = d).
'This is just for the sake of example.
If a = b Then
    If b = c Then
        If c = d Then
            MsgBox "a is equal to d"
        End If
    End If
End If

can become (69 chars):

If a = b And b = c And c = d Then
    MsgBox "a is equal to d"
End If

This also works with non-nested conditionals.

Consider (84 chars):

If a = b Then            
    d = 0
End If

If c = b Then            
    d = 0
End If

This can become (51 chars):

If a = b Or c = b Then            
    d = 0
End If
share|improve this answer
add comment

Using With (Sometimes! See footnote)

Using the With statement can reduce your code size significantly if you use some objects repeatedly.

i.e. this (80 chars):

x = foo.bar.object.a.value
y = foo.bar.object.b.value
z = foo.bar.object.c.value

can be coded as (79 chars):

With foo.bar.object
    x = .a.value
    y = .b.value
    z = .c.value
End With

The above isn't even the best-case scenario. If using anything with Application, such as Excel.Application from within Access, the improvement will be much more significant.


*Depending on the situation, With may or may not be more efficient than this (64 chars):

Set i = foo.bar.object
x = i.a.value
y = i.b.value
z = i.c.value
share|improve this answer
add comment

Use Array() Choose() instead of Select or If...Then

When assigning a variable based on a the value of another variable, it makes sense to write out the steps with If...Then checks, like so:

If a = 1 Then
b = "a"
ElseIf a = 2 Then
b = "c"
'...
End If

However, this can take up a lot of code space if there are more than one or two variables to check (there are still generally better ways to do even that anyway).

Instead, the Select Case statement helps reduce the size of the checks by encasing everything in one block, like so:

Select Case a
Case 1:
b = "a"
Case 2:
b = "c"
'...
End Select

This can lead to much smaller code, but for very simple cases such as this, there is an even more efficient method: Choose() This function will pick a value from a list, based on the value passed to it.

b = Choose(a,"a","c",...)

The option to select (a in this case) is an integer value passed as the first argument. All subsequent arguments are the values to choose from (1-indexed, like most VBA). The values can be any data type so long as it matches the variable being set (i.e. objects don't work without the Set keyword) and can even be expressions or functions.

b = Choose(a, 5 + 4, String(7,"?"))

An additional option is to use the Array function to get the same effect while saving another character:

b = Array(5 + 4, String(7,"?"))(a)
share|improve this answer
    
An interesting and possibly very beneficial use for golfing is to use this in conjunction with an IIf() or another Choose(): A1=IIf(a=Choose(b,c,d,e),Choose(Choose(f,g,h,i),j,k,l),Choose(IIf(m=n,Choose(p,‌​q,r,s),IIf(t=u,v,w)),x,y,z)) –  Gaffi May 30 '12 at 21:15
add comment

Split a string into a character array

Sometimes it can be useful to break apart a string into individual characters, but it can take a bit of code to do this manually in VBA.

ReDim a(1 To Len(s))
' ReDim because Dim can't accept non-Const values (Len(s))
For i = 1 To Len(s)
    a(i) = Mid(s, i, 1)
Next

Instead, you can use a single line, relatively minimal chain of functions to get the job done:

a = Split(StrConv(s, 64), Chr(0))

This will assign your string s to Variant array a. Be careful, though, as the last item in the array will be an empty string (""), which will need to be handled appropriately.

Here's how it works: The StrConv function converts a String to another format you specify. In this case, 64 = vbUnicode, so it converts to a unicode format. When dealing with simple ASCII strings, the result is a null character (not an empty string, "") inserted after each character.

The following Split will then convert the resulting String into an array, using the null character Chr(0) as a delimiter.

It is important to note that Chr(0) is not the same as the empty string "", and using Split on "" will not return the array you might expect. The same is also true for vbNullString (but if you're golfing, then why would you use such a verbose constant in the first place?).

share|improve this answer
    
You should mention that the resulting array has an additional empty string at the end. –  Howard Apr 2 '13 at 5:07
    
@Howard Yes, I should. It was in my head while I was typing this up, must have slipped my mind. –  Gaffi Apr 2 '13 at 11:46
    
+1 for turning a bug in the core library into a feature –  Peter Taylor Apr 2 '13 at 14:44
add comment

Converting a string to a number

If you have a string value such as "1" (note a string, not 1), you can use a number of different conversions to get this as a manipulable number.

  • y=Val(x)
  • y=Int(x)
  • y=CLng(x)
  • Etc.

You can convert it using a math operation, like so:

  • y=x+0
  • y=x*1

These usually work in most cases, and only costs 5 characters, as opposed to 8 from the smallest example above.

However, the smallest way to do this conversion only is to do the following:

Dim y As Integer
x="123"
y=x

where y=x is a 3 char conversion, but this requires the explicit Dimming of y, which is often generally unnecessary when golfing in the first place.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.