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

34 Answers 34

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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