16
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Converted to Community Wiki as per policy. \$\endgroup\$ – dmckee Mar 16 '12 at 15:24
  • \$\begingroup\$ Sorry that wasn't automatic on my part! \$\endgroup\$ – Gaffi Mar 16 '12 at 15:26
  • \$\begingroup\$ 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. \$\endgroup\$ – dmckee Mar 16 '12 at 15:28
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – Mr. Llama Mar 20 '12 at 17:55
  • 2
    \$\begingroup\$ @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. :-) \$\endgroup\$ – Gaffi Mar 20 '12 at 17:59

33 Answers 33

8
\$\begingroup\$

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
\$\endgroup\$
7
\$\begingroup\$

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.

enter image description here

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

enter image description here


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

?Chr(88-23);

answered by Joffan.


Credit images: Excel Campus

\$\endgroup\$
  • 1
    \$\begingroup\$ That uses a REPL environment though? That means it is only a snippet, not a program or function \$\endgroup\$ – caird coinheringaahing May 28 '17 at 18:06
  • \$\begingroup\$ I think another example to add may be that you can also create variables inline; e.g a="value":?a first sets the value of a, then prints it. \$\endgroup\$ – Greedo Aug 3 '17 at 20:42
6
\$\begingroup\$

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
\$\endgroup\$
5
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Wait a second, you accepted your own answer to your question. Not cool, dude. \$\endgroup\$ – Taylor Scott May 28 '17 at 17:39
  • 1
    \$\begingroup\$ @TaylorScott It's a wiki post - no points, and in this case, there is not one single best answer. :) I accept the answer ~5 years ago to avoid having a flag in my questions list. \$\endgroup\$ – Gaffi May 28 '17 at 17:41
  • 1
    \$\begingroup\$ @TaylorScott also if you accept your own answer, you don't get +15 or +2 (for accepting) \$\endgroup\$ – caird coinheringaahing May 28 '17 at 18:04
5
\$\begingroup\$

Evaluate() And []

As has been pointed out previously, hard-coded range calls can be reduced using the square brackets [A1] notation. However it has many more uses than just that.

According to MSDN documentation, the Application.Evaluate() method takes a single argument, which is a Name, as defined by the naming convention of Microsoft Excel.

N.B. [string] is shorthand for Evaluate("string") (note the "" speech marks denoting string datatype), although there are a few important differences which are covered at the end.

So what does that all mean in terms of usage?

Essentially, [some cell formula here] represents a cell in an excel worksheet, and you can put pretty much anything into it and get anything out of it that you could with a normal cell

What goes in

In summary, with examples

  • A1-style references. All references are considered to be absolute references.
    • [B7] returns a reference to that cell
    • As references are absolute, ?[B7].Address returns "B$7$"
  • Ranges You can use the range, intersect, and union operators (colon, space, and comma, respectively) with references.
    • [A1:B5] returns a range reference to A1:B5 (Range)
    • [A1:B5 A3:D7] returns a range reference to A3:B5 (Intersect)
    • [A1:B5,C1:D5] returns a range reference to A1:D5(technically A1:B5,C1:D5) (Union)
  • Defined names
    • User defined such as [myRange] referring to A1:G5
    • Automatic, like table names [Table1] (possibly less useful for codegolf)
    • Anything else you can find in the [Formulas]>[Name Manager] menu
  • Formulas
    • Very useful; any formula that can go in a cell can go in Evaluate() or []
    • [SUM(1,A1:B5,myRange)] returns arithmetic sum of values in the ranges myRange here refers to a workbook name not a VBA variable
    • [IF(A1=1,"true","false")] (1 byte shorter than vba equivalent Iif([A1]=1,"true","false"))
    • Array formulas[CONCAT(IF(LEN(A1:B7)>2,A1:B7&" ",""))] - joins all strings in range whose length is greater than 2 with a space
  • External references
    • You can use the ! operator to refer to a cell or to a name defined in another workbook
    • [[BOOK1]Sheet1!A1] returns a range reference to A1 in BOOK1 or ['[MY WORKBOOK.XLSM]Sheet1!'A1] for the same in MY WORKBOOK
    • Note the ' for workbooks with spaces in their names, and the lack of extension for default named workbooks (BOOK+n)
  • Chart Objects (See the MSDN article)

NB I asked a question over on SO for this info, so take a look there for a better explanation

What comes out

Similar to what goes in, what comes out includes anything that a worksheet cell can return. These are the standard Excel data types (and their VBA equivalents):

  • Logical (Boolean)
  • Text (String)
  • Numerical (Double)
  • Error (Variant/Error) (these are non-breaking errors, ie. they do not halt code execution, ?[1/0] executes fine)

But there are a few things that can be returned which cells cannot:

  • Range reference (Range) (see above sections)
  • Arrays (Variant())

Arrays

As has been shown, [] can be used to evaluate array formulas which return one of the standard Excel data types; e.g. [CONCAT(IF(LEN(A1:B7)>2,A1:B7&" ",""))] which returns Text. However Evaluate can also return arrays of Variant type. These can be

Hardcoded:

[{1,2;3,4}] - outputs a 2D array: , is the column separator, ; separates rows. Can output a 1D array

Array Formulae:

[ROW(A1:A5)] - outputs a 2D array {1,2,3,4,5}, i.e (2,1) is the second item (yet some functions output 1D arrays)

  • For whatever reason, some functions do not return arrays by default

[LEN(A1:A5)] only outputs the Length of the text in the 1st cell of a range

  • However these can be coerced to give arrays

Split([CONCAT(" "&LEN(A1:A5))]) gives a 1D array 0 to 5, where the first item is empty

[INDEX(LEN(A1:A5),)] is another workaround, essentially you must employ an array handling function to get the desired array returning behaviour, similar to adding in a meaningless RAND() to make your worksheet formulae volatile.

  • I asked a question on SO to try to get some better info on this please edit/comment with better options if you find them

Evaluate() vs []

There are a few differences between Evaluate() and [] to be aware of

  1. Strings vs hardcoded
    • Perhaps the most important difference, Evaluate takes a string input where [] required a hardcoded input
    • This means your code can build up the string with variables e.g. Evaluate("SUM(B1,1,"&v &",2)") would sum [B1],1,2 and variable v
  2. Arrays

    When returning an Array, only Evaluate can be used with an array index, so

    v=Evaluate("ROW(A1:A5)")(1,1) ''#29 bytes
    

    is equivalent to

    i=[ROW(A1:A5)]:v=i(1,1) ''#23 bytes
    
\$\endgroup\$
  • \$\begingroup\$ Sorry for the mammoth post, if anyone feels they can make areas more concise/ if you have tips to add, then feel free. Also, although I researched some of this, a fair portion was found through experimenting in VBA, so if you spot mistakes of feel there's something missing, don't hesitate to comment/edit accordingly! \$\endgroup\$ – Greedo Aug 3 '17 at 11:19
  • 1
    \$\begingroup\$ Actually the last section on arrays is a bit off - it can be used in the immediate window i=[ROW(A1:A5)]:v=i(2,1):?v \$\endgroup\$ – Taylor Scott Aug 3 '17 at 12:39
  • \$\begingroup\$ @TaylorScott So you can, I was totally unaware that you could set/ hold values in variables that weren't already defined, but I suppose that's because I'm still getting to grips with the Immediate window 1-liners. Have updated accordingly \$\endgroup\$ – Greedo Aug 3 '17 at 20:38
  • \$\begingroup\$ Does this work with Match? I tried, but it always returns errors. I was passing an array instead of a range, though. \$\endgroup\$ – seadoggie01 Sep 7 '18 at 18:50
5
\$\begingroup\$

Combine Next Statements

Next:Next:Next

May be condensed down to

Next k,j,i

where the iterators for the For loops are i,j, and k - in that order.

For example the below (69 Bytes)

For i=0To[A1]
For j=0To[B1]
For k=0To[C1]
Debug.?i;j;k
Next
Next
Next

May be condensed down to 65 Bytes

For i=0To[A1]
For j=0To[B1]
For k=0To[C1]
Debug.?i;j;k
Next k,j,i

And as far as how this impacts formatting and indentation, I think the best approach to handling this is left aligning the next statement with the outer most for statement. Eg.

For i=0To[A1]
    For j=0To[B1]
        For k=0To[C1]
            Debug.?i;j;k
Next k,j,i
\$\endgroup\$
4
\$\begingroup\$

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)
\$\endgroup\$
3
\$\begingroup\$

Reduce Range("A1") and Like Calls

Range("A1").Value(17 Bytes) and the simpler Range("A1")(11 Bytes) may be reduced down to [A1] (4 Bytes)

\$\endgroup\$
  • 2
    \$\begingroup\$ Or in general, the use of [] to replace Evaluate() in VBA is a lot more versatile than just range calls. E.g you can use it to replace WorksheetFunction.FuncName(args) with just [FuncName(args)], such as ?[SUMPRODUCT({1,3,5},{2,7,-1})] or the very useful [MIN(1,2,3)]/[MAX(1,2,3)] \$\endgroup\$ – Greedo Jun 29 '17 at 10:20
  • \$\begingroup\$ Greedo, that is an incredibly important functionality that I was not aware of you should add this as its own answer. \$\endgroup\$ – Taylor Scott Jun 29 '17 at 16:03
  • 1
    \$\begingroup\$ Sure thing, I'll write something up shortly. Yes Evaluate("str") or shorthand [str] is very powerful in VBA, I've only recently found out, and I'm guessing it will be useful for golfing too! \$\endgroup\$ – Greedo Jun 29 '17 at 16:17
  • \$\begingroup\$ Very useful, enoughso that I am going back through my answers to see if I can drop my bytecount on any because of it \$\endgroup\$ – Taylor Scott Jun 29 '17 at 16:28
3
\$\begingroup\$

Remove Spaces

VBA will auto-format to add a lot of spacing that it doesn't actually need. There' an answer on meta that makes a lot of sense to me why we can discount bytes added by auto-formatting. It's important to always verify you haven't removed too much, though. For an example, here's an answer of mine that was reduced almost 22% just by removing spaces:

Original version: (188 bytes)

Sub g(n)
For i = 0 To 1
For x = -3 To 3 Step 0.05
y = n ^ x * Cos(Atn(1) * 4 * x)
If y < m Then m = y
If i = 1 Then Cells(500 * (1 - y / m) + 1, (x + 3) * 100 + 1) = "#"
Next
Next
End Sub

Reduced version: (146 bytes)

Sub g(n)
For i=0To 1
For x=-3To 3Step 0.05
y=n^x*Cos(Atn(1)*4*x)
If y<m Then m=y
If i=1Then Cells(500*(1-y/m)+1,(x+3)*100+1)="#"
Next
Next
End Sub

If you copy / paste the reduced version into VBA, it will automatically expand into the original. You can play around with where it is valid to remove spaces. The ones I've found so far all seem to follow the pattern where VBA is expected a certain command to appear because, without it, it's not valid code. Here are some that I've found so far:

  • Before and after any mathematical operation: +-=/*^ etc.
  • Before To and Step in a For statement: For x=-3To 3Step 0.05
  • Actually, before any reserved word (To, &, Then, etc.) if it's preceded by a literal such as a number, ), ?, %, etc.
  • After the & when combining strings: Cells(1,1)=Int(t)&Format(t,":hh:mm:ss")
  • After function calls: If s=StrReverse(s)Then
  • Within function calls: Replace(Space(28)," ",0)
\$\endgroup\$
  • \$\begingroup\$ What's the rule on added non-space characters through auto-formatting. Something like ? vs Print I see is acceptable, but putting at type declaration like & immediately after a variable eg Debug.?a&"z" gives Debug.Print a&; "z". The ; character added is essential for the code to run - is this still allowed? \$\endgroup\$ – Greedo Jun 29 '17 at 10:49
  • 2
    \$\begingroup\$ @Greedo I see it the way the meta answer described it: If you can copy / paste it into the editor and the code will run, it's OK. I.E., if VBA automatically adds the character back when you paste the code, then it's OK. \$\endgroup\$ – Engineer Toast Jun 29 '17 at 12:09
  • 1
    \$\begingroup\$ @EngineerToast you can actually drop one more byte off of your example: If i=1 Then may become If i=1Then because, as a general rule a reserved word that follows a literal, be that a number, ), ?, %,.. etc, does not need to be separated by a space \$\endgroup\$ – Taylor Scott Jul 2 '17 at 14:27
  • 1
    \$\begingroup\$ @EngineerToast, you can also drop a byte from your third bullet point as you can drop the spaces between a ) (or any other non-number literal) and & \$\endgroup\$ – Taylor Scott Jul 2 '17 at 14:43
3
\$\begingroup\$

Prepare For Pixel Art

Pixel art is by far one of Excel's strongest areas, as there is no need to construct a canvas, as it is already there for you - all you need to do is make some small adjustments

1) Make the cells square

Cells.RowHeight=48

or, Alternatively, for 1 byte more, but far easier to work with

Cells.ColumnWidth=2

2) Color the needed range

Any Range object may be colored by calling

`MyRangeObj`.Interior.Color=`ColorValue`

Note: this can and should be combined with other tricks noted on this wiki, such as referencing ranges by the use of the [] notation (eg [A1:R5,D6]) over the use of a Cells(r,c) or a Range(cellAddress) call. Further, as noted on this wiki, this may be combined with negative color value to golf down the size of color references

Quick References

Range Reference

Cell A1 may be referenced in any of the following ways

Range("A1")
Cells(1,1)
[A1]

and the Range A1:D4 may be referenced in any of the following ways

Range("A1:D4")
Range("A1").Resize(4,4)
Cells(1,1).Resize(4,4)
[A1].Resize(4,4,)
[A1:D4]

Color Reference

Black  0
White  -1
Red    255
Aqua   -256
\$\endgroup\$
3
\$\begingroup\$

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.

\$\endgroup\$
  • 2
    \$\begingroup\$ in the last one, you do not need the assignment call, meaning that a = f(w) can be reduced to f w \$\endgroup\$ – Taylor Scott Jun 15 '17 at 14:12
3
\$\begingroup\$

STDIN and STDOUT

Inputting to Subroutines and Functions via input variables

Public Sub A(ByRef B as String)

May be reduced down to

Sub a(b$) 

The Public and ByRef calls are the default for VBA and thus implicit, and may (almost) always be dropped.

The type literal $ forces b to be of the type String.

Other type literals

  • ! Single
  • @ Currency
  • # Double
  • % Integer
  • $ String
  • & Long
  • ^ LongLong (64 Bit Only)

Furthermore, it is generally accepted that you may leave the input variable as the default type, Variant and leave any type-based errors unhandled. Eg. Sub E(F) in which F is expected to be of type Boolean[] (which would be passed to the routine like E Array(True, False, False))

Inputting to Subroutines and Immediate Window Functions via Cells

VBA does not have a fully functional console and thus does not have any official STDIN, and thus allows for some play with passing input.

In excel, it is generally accepted to take input from a cell or range of cells, which may be done like

s=[A1]

which implicitly puts the .value from the cell [A1] (which may also be referenced as cells(1,1) or range("A1")

Example Problem: Display the input in a messagebox

Via Subroutine Sub A:msgbox[A1]:End Sub

Via Immediates Window Function msgbox[A1]

Inputting Via Conditional Compilation Arguments

VBA Projects support taking arguments from the command line or via the VBAProject Properties (view via the project explorer -> [Your VBA Project] -(Right Click)-> VBAProject Properties -> Conditional Compilation Arguments)

This is largely useful for Error Code Challenges

Given the Conditional Compilation Argument n=[some_value] this allows for executing code that will produce an error code, based off of the value of n. note, this calls for an addition of 2 bytes to your code for the n= in the conditional compilation arguments section of the VBAProject Properties Pane.

Example Code

...
#If n=3 then
return ''  Produces error code '3', Return without GoSub
#ElseIf n=20 then
resume ''  Produces error code '20', Resume without Error
#EndIf
...

Outputting Via Function Value

Not Much to say here, the general form of quoted below is about as compact as it can be made.

Public Function A(b)
    ...
    A=C
End Function

NOTE: in the vast majority of cases it is more byte convert the method to a subroutine and output to the VBE immediates window (see Below)

Outputting From Subroutines and Functions via the VBE Immediates Window

Outputting to the VBE immediates window (AKA the VBE Debug Window) is a common output method for VBA for text based challenges, however, it is important to remember that the Debug.Print "Text" call may be substantially golfed.

Debug.Print "Text"

is functionally identical to

Debug.?"Text"

as ? autoformats to Print.

Outputting from Subroutines and VBE Immediates Window functions via Other Methods

On rare occasion, when the situation is just right, you may take input from some of the more trivial inputs available to VBA such as the font size adjuster, font selector, and zoom. (Eg. Emulating the Word Font Size Selector)

\$\endgroup\$
2
\$\begingroup\$

Quick Note on Formatting

Because StackExchange uses Markdown and Prettify.js it is possible to add a language flag to your coding answers, which generally makes them look more professional. While I cannot guarantee that this will make you any better at golfing in VBA, I can guarantee that it will make you look like you are.

Adding either of the flags below will transform

Public Sub a(ByRef b As Integer) ' this is a comment

to

Public Sub a(ByRef b As Integer) ' this is a comment

VBA Language Tags

<!-- language: lang-vb -->

<!-- language-all: lang-vb -->

Note: the latter transforms all code segments in your answer, while the prior transforms only the immediately following code segments

\$\endgroup\$
  • \$\begingroup\$ lang-vb eh? - Strange that that formats better than lang-vba for vba answers. \$\endgroup\$ – Greedo Aug 12 '17 at 15:45
  • 1
    \$\begingroup\$ @Greedo, that is because while lang-vba will provide highlighting, its actually just the default highlighting. Apparently VBA has not actually been implemented in Prettify.js, so we'll have to stick with the VB highlighting \$\endgroup\$ – Taylor Scott Aug 12 '17 at 15:47
2
\$\begingroup\$

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
\$\endgroup\$
  • 1
    \$\begingroup\$ in If - then statements the "End - if" part is optional provide you write the code in the single line. \$\endgroup\$ – Prison Mike Apr 16 '17 at 5:41
  • \$\begingroup\$ @newguy Correct. See also this other answer: codegolf.stackexchange.com/a/5788/3862 \$\endgroup\$ – Gaffi Apr 16 '17 at 11:29
  • \$\begingroup\$ The statement above can be condensed further to d=iif(a=b or c=b,0,d) \$\endgroup\$ – Taylor Scott Jun 28 '17 at 20:32
2
\$\begingroup\$

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

\$\endgroup\$
  • 1
    \$\begingroup\$ 2 characters, don't forget to count the space! \$\endgroup\$ – 11684 Mar 11 '13 at 21:12
  • \$\begingroup\$ I happen to almost never identify the variable, even in normal code! \$\endgroup\$ – dnep Apr 13 '18 at 15:04
2
\$\begingroup\$

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
\$\endgroup\$
2
\$\begingroup\$

Infinite Loops

Consider replacing

Do While a<b
'...
Loop

or

Do Until a=b
'...
Loop

with the antiquated but lower byte count

While a<b
'...
Wend

If you need to exit a Sub or Function prematurely, then instead of Exit ...

For i=1To 1000
    If i=50 Then Exit Sub
Next

consider End

For i=1To 1E3
    If i=50 Then End
Next

Note that End halts all code execution and clears out any global variables, so use wisely

\$\endgroup\$
  • \$\begingroup\$ You should consider rewriting the last section (For and End) to reflect that the case 100 will never be met and adds an unecessary byte \$\endgroup\$ – Taylor Scott Jun 28 '17 at 20:18
  • \$\begingroup\$ Also, while this is very legible, you may consider removing the whitespace to show the possible benefits of these methods better (it is always good to abuse the autoformatting in VBA for codegolfing) \$\endgroup\$ – Taylor Scott Jun 28 '17 at 20:20
2
\$\begingroup\$

Use Spc(n) over Space(n), [Rept(" ",n)] or String(n," ")

When trying to insert several spaces of length n, use

Spc(n)              ''  6  bytes

over

B1=n:[Rept(" ",B1)] ''  19 bytes
String(n," ")       ''  13 bytes
Space(n)            ''  8  bytes

Note: I am not sure why, but it seems that you cannot assign the output of Spc(n) to a variable, and that it must rather be printed directly - so in certain cases Space(n) is still the best way to go.

\$\endgroup\$
2
\$\begingroup\$

Reduce Debug.Print and Print calls

Debug.Print [some value] 

(12 Bytes; note the trailing space) may be reduced to

Debug.?[some value]

(7 Bytes; note the lack of trailing space).

Similarly,

Print 

(6 Bytes) may be reduced to

?

(1 Byte).

Furthermore, when operating in the context of an anonymous VBE immediate window function the Debug statement may be dropped entirely, and instead printing to the VBE immediate window via ? may be assumed to be STDIN/STDOUT

Special characters

When printing strings you can also use special characters in place of &. These allow for alternate formats in what's printed or whitespace removal

To join variable strings, instead of

Debug.Print s1 &s2

You can use a semicolon ;

Debug.?s1;s2

This semicolon is the default behaviour for consecutive strings, as long as there is no ambiguity So these are valid:

Debug.?s1"a"
Debug.?"a"s1

But not

Debug.?s1s2 'as this could be interpreted as a variable named "s1s2", not 2 variables
            'use Debug.?s1 s2 instead (or s1;s2)
Debug.?"a""b" 'this prints a"b, so insert a space or ; for ab

Note that a ; at the end of a line suppresses a newline, as newlines are by default added after every print. Counting bytes returned by VBA code is under debate

To join with a tab use a comma , (actually 14 spaces, but according to )

Debug.?s1,s2 'returns "s1              s2"

Finally, you can use type declarations to join strings instead of ;, each having its own slight effect on formatting. For all of the below a = 3.14159 is the first line

Debug.?a&"is pi" -> " 3 is pi" 'dims a as long, adds leading and trailing space to a
Debug.?a!"is pi" -> " 3.14159 is pi" 'dims a as single, adds leading and trailing space
Debug.?a$"is pi" -> "3.14159is pi" 'dims a as string, no spaces added
\$\endgroup\$
2
\$\begingroup\$

Use a helper Sub to Print

If the code requires using more than six Debug.? statements you can reduce bytes by replacing all Debug.? lines with a call to a Sub like the one below. To print a blank line you need to call p "" since a parameter is required.

Sub p(m)
Debug.?m
End Sub
\$\endgroup\$
  • 1
    \$\begingroup\$ To print a new line with that you would only need p"" or if it can be unterminated, p". However, in the vast majority of cases where this may apply, it makes more sense to use a string variable and only print the string variable at the end. \$\endgroup\$ – Taylor Scott Jun 12 '18 at 11:08
1
\$\begingroup\$

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)
\$\endgroup\$
  • \$\begingroup\$ 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)) \$\endgroup\$ – Gaffi May 30 '12 at 21:15
1
\$\begingroup\$

Bit-Shifted RGB Values

Because of the way that Excel handles colors, (unsigned, 6-character hexadecimal integer) you can make use of negatively signed integers, of which excel will only use the right 6 bytes to assign a color value

This is a bit confusing so some examples are provided below

Lets say you want to use the color white, which is stored as

rgbWhite

and is equivalent to

&HFFFFFF ''#value: 16777215

to golf this down all we need to do is find a negative hexadecimal value which terminates in FFFFFF and since negative Hex values in must be in the format of FFFFXXXXXX (such that X is a valid hexadecimal) counting down from FFFFFFFFFF (-1) to FFFF000001 (-16777215)

Knowing this we can generalize that the formula

Let Negative_Color_Value = -rgbWhite + Positive_Color_Value - 1
                         = -16777215 + Positive_Color_Value - 1
                         = -16777216 + Positive_Color_Value

Using this, some useful conversions are

rgbWhite = &HFFFFFF = -1 
rgbAqua  = &HFFFF00 = -256
16747627 = &HFF8C6B = -29589
\$\endgroup\$
  • \$\begingroup\$ Good to know what was going on there, I just discovered it by accident in that question - but this clear explanation shows exactly how it works. Another thing to bear in mind is that certain key colours can be reduced with mathematical operators; rgbWhite=2^24-1 although could possibly be reduced further if you miss off the -1 to get roughly there \$\endgroup\$ – Greedo Aug 12 '17 at 16:42
  • 1
    \$\begingroup\$ In fact, here is a list of colours with their mathematical representations, which are shorter than either the positive or negative decimal versions: &000080: 2^7, &000100: 2^8, &000200: 2^9, &000400: 2^10, &000800: 2^11, &001000: 2^12, &002000: 2^13, &004000: 2^14, &008000: 2^15, &010000: 2^16, &01FFFF: 2^17-1, &020000: 2^17, &03FFFF: 2^18-1, &040000: 2^18, &07FFFF: 2^19-1, &080000: 2^19, &0FFFFF: 2^20-1, &100000: 2^20, &1FFFFF: 2^21-1, &3FFFFF: 2^22-1, &400000: 2^22, &7FFFFF: 2^23-1 n.b. not necessarily complete, but I think I did a fairly thorough job \$\endgroup\$ – Greedo Aug 12 '17 at 16:43
1
\$\begingroup\$

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?).

\$\endgroup\$
  • \$\begingroup\$ You should mention that the resulting array has an additional empty string at the end. \$\endgroup\$ – Howard Apr 2 '13 at 5:07
  • \$\begingroup\$ @Howard Yes, I should. It was in my head while I was typing this up, must have slipped my mind. \$\endgroup\$ – Gaffi Apr 2 '13 at 11:46
1
\$\begingroup\$

Omit terminal " when printing to Immediate Window

Given the function below, which is to be used in the debug window

h="Hello":?h", World!"

The Terminal " may be dropped for -1 Byte, leaving the string unclosed and the program shall execute the same, without error

h="Hello":?h", World!

Even more surprising than this is that this may be done within fully defined subroutines.

Sub a
h="Hello":Debug.?h", World!
End Sub

The subroutine above runs as expected and autoformats to

Sub a()
h = "Hello": Debug.Print h; ", World!"
End Sub
\$\endgroup\$
1
\$\begingroup\$

Use Truthy and Falsey Variables in conditionals

Sometimes called implicit type conversion - directly using a number type in a If,[IF(...)] or IIf(...) statement, by using the truthy and falsey nature of that number can absolutely save you some bytes.

In VBA, any number type variable that is non-zero is considered to be truthy ( and thus zero, 0, is the only value falsey) and thus

If B <> 0 Then 
    Let C = B
Else 
    Let C = D
End If 

may be condensed to

If B Then C=B Else C=D

and further to

C=IIf(B,B,D)
\$\endgroup\$
1
\$\begingroup\$

Address Sheets By Name

Instead of Addressing a WorkSheet object by calling

Application.ActiveSheet
''  Or 
ActiveSheet   

One may use

Sheets(n)  ''  Where `n` is an integer
''  Or 
[Sheet1]

Or More preferably one may access the object directly and use

Sheet1
\$\endgroup\$
1
\$\begingroup\$

Exponentiation and LongLongs in 64-Bit VBA

The general form of exponentiation,

A to the power of B

Can be represented as in VBA as

A^B 

But Only in 32-Bit Installs of Office, in 64-Bits installs of Office, the shortest way that you may represent without error this is

A ^B

This is because in 64-Bit versions of VBA ^ serves as both the exponentiation literal and the LongLong type declaration character. This means that some rather odd-looking but valid syntax can arise such as

a^=2^^63^-1^

which assigns variable a to hold the max value of a Longlong

\$\endgroup\$
1
\$\begingroup\$

Compress Byte Arrays as String

For challanges that require the solution to hold constant data, it shall may be useful to compress that data as a single string and to then iterate across the string to fetch the data.

In VBA, any byte value may be directly converted to a character with

Chr(byteVal)

And a long or integer value may be converted to two characters with

Chr(longVal / 256) & Chr(longVal Mod 256)

So for a byte array a compression algorithm would look something like

For Each Var In bytes
    Select Case Var
        Case Is = Asc(vbNullChar)
            outstr$ = outstr$ & """+chr(0)+"""
        Case Is = Asc(vbTab)
            outstr$ = outstr$ & """+vbTab+"""
        Case Is = Asc("""")
            outstr$ = outstr$ & """"""
        Case Is = Asc(vbLf)
            outstr$ = outstr$ & """+vbLf+"""
        Case Is = Asc(vbCr)
            outstr$ = outstr$ & """+vbCr+"""
        Case Else
            outstr$ = outstr$ & Chr$(Var)
    End Select
Next
Debug.Print "t="""; outstr$

Noting that the Select Case section is implemented due to the characters which may not be stored as literals in the string.

From the resulting string, which will look something like

t="5¼-™):ó™ˆ"+vbTab+"»‘v¶<®Xn³"+Chr(0)+"~ίšÐ‘š;$ÔÝ•óŽ¡¡EˆõW'«¡*{ú{Óx.OÒ/R)°@¯ˆ”'®ïQ*<¹çu¶àªp~ÅP>‹:<­«a°;!¾y­›/,”Ì#¥œ5*B)·7    

The data may then be extracted using the Asc and Mid functions, eg

i=Asc(Mid(t,n+1))
\$\endgroup\$
1
\$\begingroup\$

Use Improper Constants

VBA allows, in some cases, for the use of unlisted or improper constants in expressions which require constant values. These are often legacy values, and shorter in length than the proper values.

For example the values below may be used when letting a value be held by the rng.HorizantalAlignment property.

\$ \small \begin{align} \textbf{Improper} &&\textbf{Proper} && \textbf{General Constant}\hspace{1.3cm}&&& \texttt{xlHAlign}~~\textbf{Constant} \\\hline 1\hspace{1.25cm} && 1\hspace{.7cm} && \texttt{xlGeneral}\hspace{2.95cm} &&& \texttt{xlHAlignGeneral} \\ 2\hspace{1.25cm} && -4131 && \texttt{xlLeft}\hspace{3.6cm} &&& \texttt{xlHAlignLeft} \\ 3\hspace{1.25cm} && -4108 && \texttt{xlCenter}\hspace{3.18cm} &&& \texttt{xlHAlignCenter} \\ 4\hspace{1.25cm} && -4152 && \texttt{xlRight}\hspace{3.39cm} &&& \texttt{xlHAlignRight} \\ 5\hspace{1.25cm} && 5\hspace{.7cm} && \texttt{xlFill}\hspace{3.61cm} &&& \texttt{xlHAlignFill} \\ 6\hspace{1.25cm} && -4130 && \texttt{xlJustify}\hspace{2.97cm} &&& \texttt{xlHAlignJustify} \\ 7\hspace{1.25cm} && 7\hspace{.7cm} && \texttt{xlCenterAcrossSelection} &&& \texttt{xlHAlignCenterAcrossSelection} \\ 8\hspace{1.25cm} && -4117 && \texttt{xlDistributed}\hspace{2.15cm} &&& \texttt{xlHAlignDistributed} \end{align} \$

This means, for example that setting a cell to having its text centered with

rng.HorizantalAlignment=-4108

may be shortened down to

rng.HorizantalAlignment=3

by replacing the improper constant value 3 for the proper value -4108. Note that if you fetch the rng.HorizantalAlignment property value, it will return the proper value. In this case, that would be -4108.

\$\endgroup\$
1
\$\begingroup\$

In Excel, Create Numeric Arrays by Coercing Ranges

When a constant single dimensional set of numerics of mixed length, \$S\$ is needed, it shall be more efficient to use [{...}] notation to declare a range and then coerce that into a numeric array rather than to use the Split(String) notation or similar.

Using this method, a change of \$\Delta_\text{byte count} =-5~\text{bytes}\$ shall be accrued for all \$S\$.

Example

For instance,

x=Split("1 2 34 567 8910")

may be written as

x=[{1,2,34,567,8910}]

It is further worth noting that while this method does not allow for direct indexing, it does allow for iteration over the for loop directly, ie

For Each s In[{1,3,5,7,9,2,4,6,8}]
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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