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.

  • \$\begingroup\$ Converted to Community Wiki as per policy. \$\endgroup\$ Mar 16, 2012 at 15:24
  • \$\begingroup\$ Sorry that wasn't automatic on my part! \$\endgroup\$
    – Gaffi
    Mar 16, 2012 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\$ Mar 16, 2012 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, 2012 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, 2012 at 17:59

33 Answers 33


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.



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


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

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.

As noted in the comments, the above can be further reduced as:

Dim y%
  • 4
    \$\begingroup\$ You can use identifier type character %, i.e. Dim y%, to save 10 bytes. \$\endgroup\$ Aug 21, 2016 at 6:23

Automate Testing for Immediate Window Functions

This response seeks to make the testing of Anonymous VBE Immediate Window Functions that take input from the range [A1] and output to the immediate window.

Testing immediate window functions can take hours if done by hand, so rather than doing that, take advantage of the Microsoft Visual Basic for Applications Extensibility 5.3 reference and and helper function to automate testing these functions. (you will need to give Excel access to the VBE project model - remember to revoke this access when you are done!)


As sample of code that can do this is shown below - must be pasted into the top of Module1 to function.

Private Sub helper()
    ''  At runtime, the program will be pasted below, with `?` replaced as `Debug.Print`

    ''  At runtime, the program will be pasted above
End Sub

Sub cgTester(ByVal program As String, ParamArray testCases())

    ''  Using `MicroSoft Visual Basic for Applications Extensibility 5.3` and
    ''  having access to the VBA Project Model

    Dim testCase    As Variant, _
        vbProj      As VBIDE.VBProject, _
        vbComp      As VBIDE.VBComponent

    Debug.Print vbCrLf; vbCrLf; program; vbCrLf; vbCrLf

    Set vbProj = Application.ThisWorkbook.VBProject
    Set vbComp = vbProj.VBComponents.Item("Module1")
    Debug.Assert (vbComp.CodeModule.Lines(2, 1) = _
        "    ''  At runtime, the program will be pasted below, with `?` replaced as `Debug.Print`")
    Debug.Assert (vbComp.CodeModule.Lines(4, 1) = _
        "    ''  At runtime, the program will be pasted above")

    ''  "paste" in the program above, where indicated
    Call vbComp.CodeModule.InsertLines(3, Replace(program, "?", "Debug.?"))
    Call vbComp.CodeModule.DeleteLines(4, 1)

    For Each testCase In testCases
        Let [A1].Value = testCase
        Debug.Print "<- `"; testCase; "`"; vbCrLf; "->";
        Call helper                                         '' Call the program
        Debug.Print                                         '' Print for spacing
    Next testCase

    Call vbComp.CodeModule.InsertLines(3, "")   ''  Clear out the program to be ready for next use
    Call vbComp.CodeModule.DeleteLines(4, 1)
End Sub


Shown as used on my solution to the Facey McFaceface question.

program = "b=[Left(Upper(A1))]+Mid([Lower(A1)],2):l=len(b):y=[Right(A1)=""y""]:?b;IIf(y,"""",""y"")"" Mc""IIf(y,Left(b,l-1+(InStrRev(b,""e"")=l-1)),b)""face"

cgTester program, "boat", "Face", "Dog", "Family", "Lady", "Donkey", "Player", "yyy", "DJ Grand Master Flash"

b=[Left(Upper(A1))]+Mid([Lower(A1)],2):l=len(b):y=[Right(A1)="y"]:?b;IIf(y,"","y")" Mc"IIf(y,Left(b,l-1+(InStrRev(b,"e")=l-1)),b)"face

<- `boat`
->Boaty McBoatface

<- `Face`
->Facey McFaceface

<- `Dog`
->Dogy McDogface

<- `Family`
->Family McFamilface

<- `Lady`
->Lady McLadface

<- `Donkey`
->Donkey McDonkface

<- `Player`
->Playery McPlayerface

<- `yyy`
->Yyy McYyface

<- `DJ Grand Master Flash`
->Dj grand master flashy McDj grand master flashface

Use base64 Compression

With the Microsoft XML, v3.0 Reference, MSXML2.DOMDocument objects may be used decode a base64 string into a byte array, which may be used as necessary for the question.

The most basic code needed to perform this is

Set d=New MSXML2.DOMDocument
Set d=d.createElement("b64")
d.Text="[Your base64 String]

where b is the resulting byte array (0-indexed). More information about the basis for this method and about encoding/decoding in base64 in VBA may be found here

I've written up a short segment of code to generate the above snippet of code from a supplied byte array

Public Function GolfBase64Code(ByRef bytes() As Byte, _
                   Optional ByVal DOMname As String = "d", _
                   Optional ByVal bytesName As String = "b")
    Debug.Assert DOMname <> "" And bytesName <> ""

    Dim DOM             As New MSXML2.DOMDocument, _
        node            As MSXML2.IXMLDOMElement, _
        lines(0 To 4)   As String, _
        base64String    As String

    Set node = DOM.createElement("b64")
    Let node.DataType = "bin.base64"
    Let node.nodeTypedValue = bytes
    Let base64String = Replace(node.Text, vbLf, "")
    Set node = Nothing
    Set DOM = Nothing

    Let lines(0) = "Set " & DOMname & "=New MSXML2.DOMDocument"
    Let lines(1) = "Set " & DOMname & "=" & DOMname & ".createElement(""b64"")"
    Let lines(2) = DOMname & ".DataType=""bin.base64"
    Let lines(3) = DOMname & ".Text=""" & base64String
    Let lines(4) = bytesName & "=" & DOMname & ".nodeTypedValue"
    Let GolfBase64Code = VBA.Join(lines, vbLf)
End Function

And an example to show the usage of the function

Private Sub GolfBase64CodeExample()
    Const hexString As String = _
        "030A37BE2F9B072B0E3CF59B8A911B0B0EBD9378B83EB05A70B50280D0B19CD2" & _
        "2093209C3D1126D6DF1997F590A3A3478AF7085929ADA32C7DFC0D7DD57A3051" & _
        "D431542BB242B1148A962914B0F1532C04130A093EBBE9161877B8E2AC7280C7" & _
    Dim bytes(0 To 127) As Byte, _
        i               As Integer

    For i = 0 To 127 Step 1
        bytes(i) = Application.WorksheetFunction.Hex2Dec(Mid(hexString, 2 * i + 1, 2))
    Next i
    cat "''''''''''''''''''''''''''''''''''''''''"
    cat "''"
    cat "'' Previous Code"
    cat "''"
    cat "''''''''''''''''''''''''''''''''''''''''"
    cat "w=Evaluate(""=Hex2Dec(""""""&Mid(""" & hexString & """,4*p+1,4)&"""""")"")"
    cat "'  From https://codegolf.stackexchange.com/revisions/127105/39"
    cat "''''''''''''''''''''''''''''''''''''''''"
    cat "''"
    cat "'' Golfed Code (-1 byte)"
    cat "''"
    cat "''''''''''''''''''''''''''''''''''''''''"
    cat GolfBase64Code(bytes, "a", "b")
    cat "w=b(2*p)*256+b(2*p+1)"
    cat "'  From https://codegolf.stackexchange.com/revisions/127105/40"
End Sub

Private Sub cat(Optional ByRef value As Variant = "")
    Debug.Print value
End Sub

Output from the above tester subroutine above

'' Previous Code


'  From https://codegolf.stackexchange.com/revisions/127105/39

'' Golfed Code (-1 byte)

Set a=New MSXML2.DOMDocument
Set a=a.createElement("b64")

'  From https://codegolf.stackexchange.com/revisions/127105/40

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.