Tips for golfing in VBA

Question

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.

Answer 1

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)

y=CDbl(x)

\$\cdots\$

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.

---

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

Dim y%
x="123"
y=x

Answer 2

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

Example

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

Usage

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

Answer 3

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.DataType="bin.base64
d.Text="[Your base64 String]
b=d.nodeTypedValue

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" & _
        "52408D3C3EAFAD631E14B23D238FC07BAF9D312E96CE25A79E372C442BB92139"
    
    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
    cat "w=Evaluate(""=Hex2Dec(""""""&Mid(""" & hexString & """,4*p+1,4)&"""""")"")"
    cat
    cat "'  From https://codegolf.stackexchange.com/revisions/127105/39"
    cat
    cat "''''''''''''''''''''''''''''''''''''''''"
    cat "''"
    cat "'' Golfed Code (-1 byte)"
    cat "''"
    cat "''''''''''''''''''''''''''''''''''''''''"
    cat
    cat GolfBase64Code(bytes, "a", "b")
    cat "w=b(2*p)*256+b(2*p+1)"
    cat
    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
''
''''''''''''''''''''''''''''''''''''''''

w=Evaluate("=Hex2Dec("""&Mid("030A37BE2F9B072B0E3CF59B8A911B0B0EBD9378B83EB05A70B50280D0B19CD22093209C3D1126D6DF1997F590A3A3478AF7085929ADA32C7DFC0D7DD57A3051D431542BB242B1148A962914B0F1532C04130A093EBBE9161877B8E2AC7280C752408D3C3EAFAD631E14B23D238FC07BAF9D312E96CE25A79E372C442BB92139",4*p+1,4)&""")")

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

''''''''''''''''''''''''''''''''''''''''
''
'' Golfed Code (-1 byte)
''
''''''''''''''''''''''''''''''''''''''''

Set a=New MSXML2.DOMDocument
Set a=a.createElement("b64")
a.DataType="bin.base64
a.Text="Awo3vi+bBysOPPWbipEbCw69k3i4PrBacLUCgNCxnNIgkyCcPREm1t8Zl/WQo6NHivcIWSmtoyx9/A191XowUdQxVCuyQrEUipYpFLDxUywEEwoJPrvpFhh3uOKscoDHUkCNPD6vrWMeFLI9I4/Ae6+dMS6WziWnnjcsRCu5ITk=
b=a.nodeTypedValue
w=b(2*p)*256+b(2*p+1)

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