Intermediate

VBA Code Generation Trick

Using Notepad++ to transform existing code into a VBA string that generates itself.

Mike Wolfe

• 5 min read
VBA Code Generation Trick

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)

Never miss an article.

Sign up to receive a Sunday morning email with links and recaps of the seven articles published that week.
(I'll never sell your email. Unsubscribe any time.)


All original code samples by Mike Wolfe are licensed under CC BY 4.0