VBA Code Generation Trick

Mike Wolfe -

In yesterday's article about copying and pasting code, I promised to reveal the Notepad++ trick I use for turning an existing block of code into a string that can be printed to the immediate window or saved directly into a code module.

For example, let's use this code from a recent article on the difference between the bang (!) and dot (.) operators:

Option Compare Database
Option Explicit

Private Sub Command2_Click()
    Debug.Print "Bang:    "; Me!tbText0
    Debug.Print "Dot:     "; Me.tbText0
    Debug.Print "Default: "; tbText0
End Sub

How do we transform the above code into the routine below?

'Build multi-line VBA string
Sub GenerateCode()
    Dim s As String

    s = s & "Option Compare Database" & vbNewLine
    s = s & "Option Explicit" & vbNewLine
    s = s & vbNewLine
    s = s & "Private Sub Command2_Click()" & vbNewLine
    s = s & "    Debug.Print ""Bang:    ""; Me!tbText0" & vbNewLine
    s = s & "    Debug.Print ""Dot:     ""; Me.tbText0" & vbNewLine
    s = s & "    Debug.Print ""Default: ""; tbText0" & vbNewLine
    s = s & "End Sub" & vbNewLine
    s = s & vbNewLine

    Debug.Print s
End Sub

Improving the process with Notepad++

Performing such a transformation manually would be tedious and error-prone.  Instead, I copy the code into Notepad++, perform a few find and replace functions, then copy it back into the VBE.

  1. Find: "
    Replace: ""
    Search mode: (o) Normal
    [Replace All]
  2. Find: ^(.*)$
    Replace:    s = s & "$1" & vbNewLine
    Search mode: (o) Regular expression
    [Replace All]
  3. Find: & "" & vbNewLine
    Replace: & vbNewLine
    Search mode: (0) Normal
    [Replace All]
  4. [Ctl] + [Home]
    Type:    Dim s As String
    [Enter] to add a blank line

Automating the process with Notepad++

The above steps are better than manually typing all the boilerplate, but it's still quite a bit of manual effort.  I'm lazy, so I looked for another way to improve the process.  Enter the Macro Recording feature of Notepad++.

To record a macro we can run again in the future, we just need to add a few steps to the above process:

  1. Macro -> Start Recording
  2. See above
  3. See above
  4. See above
  5. Macro -> Stop Recording
  6. Macro -> Save Current Recorded Macro...
    Name: Build multi-line VBA string
    [OK]

Testing the macro

To test the Notepad++ macro we just recorded, follow these steps:

  1. Copy normal code from VBA
  2. Paste into an empty Notepad++ tab
  3. Macro -> Build multi-line VBA string
  4. Copy the text from the Notepad++ tab
  5. Paste into a new VBA routine

Direct import

If you want to avoid the macro recording steps altogether, you can directly import the macro actions into the appropriate text file.  Notepad++ macros are defined within %AppData%\Notepad++\shortcuts.xml.

Here's what my file looks like after recording the macro:

There's no way to import/export a macro through the user interface, but you can directly edit the shortcuts.xml file itself.

If you have not recorded any Notepad++ macros, you will need to split the Macros tag into two separate tags.  From this...

<Macros />

...to this...

<Macros>

</Macros>

Then, copy and paste the following text in between the Macros tags:

<Macro name="Build multi-line VBA string" Ctrl="no" Alt="no" Shift="no" Key="0">
    <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
    <Action type="3" message="1601" wParam="0" lParam="0" sParam='&quot;' />
    <Action type="3" message="1625" wParam="0" lParam="0" sParam="" />
    <Action type="3" message="1602" wParam="0" lParam="0" sParam='&quot;&quot;' />
    <Action type="3" message="1702" wParam="0" lParam="768" sParam="" />
    <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
    <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
    <Action type="3" message="1601" wParam="0" lParam="0" sParam="^(.*)$" />
    <Action type="3" message="1625" wParam="0" lParam="2" sParam="" />
    <Action type="3" message="1602" wParam="0" lParam="0" sParam='    s = s &amp; &quot;$1&quot; &amp; vbNewLine' />
    <Action type="3" message="1702" wParam="0" lParam="768" sParam="" />
    <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
    <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
    <Action type="3" message="1601" wParam="0" lParam="0" sParam='&amp; &quot;&quot; &amp; vbNewLine' />
    <Action type="3" message="1625" wParam="0" lParam="0" sParam="" />
    <Action type="3" message="1602" wParam="0" lParam="0" sParam="&amp; vbNewLine" />
    <Action type="3" message="1702" wParam="0" lParam="768" sParam="" />
    <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
    <Action type="0" message="2316" wParam="0" lParam="0" sParam="" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="&#x000D;" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="&#x000A;" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="&#x000D;" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="&#x000A;" />
    <Action type="0" message="2302" wParam="0" lParam="0" sParam="" />
    <Action type="0" message="2302" wParam="0" lParam="0" sParam="" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam=" " />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam=" " />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam=" " />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam=" " />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="D" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="i" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="m" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam=" " />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="s" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam=" " />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="A" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="s" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam=" " />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="S" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="t" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="r" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="i" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="n" />
    <Action type="1" message="2170" wParam="0" lParam="0" sParam="g" />
</Macro>

Save the file.  Then close and re-open Notepad++.  The new macro should now be available in the Macro menu.

Base image by World-fly from Pixabay (modified by Mike Wolfe)