Running Dynamic VBA Code

Forget opening the Developer window, creating a module and using keywords (Sub or Function). I want to execute code - and I want to do it now.

This article covers how to run VBA script dynamically. It requires two pieces of assumed knowledge: (1) Creating and interacting with User Forms; (2) Having an active and personal xla or xlam.

This article is separated into 3 parts:

Enter dynamic VBA script into User Form
VBA's Extensibility Reference
Main script

Enter dynamic VBA script into User Form

Assume a User Form containing a TextBox property called input_enteredVBA. The dynamic VBA is entered into this property upon calling the User Form. It is then stored in the project's main code by calling Property Get.

Property Get getVBAInput() As String
    getVBAInput = input_enteredVBA.Text
End Property

VBA's Extensibility Reference

The secret sauce behind this project is creating a module in the back-end that holds all prequisite keywords and the dynamic code.

The reference Microsoft Visual Basic for Applications Extensibility 5.3 Library contains the required library and functions to do this. It is accessed through

Tools > Reference > Microsoft Visual Basic for Applications Extensibility 5.3 Library

Be sure the reference is selected for your active and personal xla or xlam.

Main script

The main script is explained in 3 steps

Accept user's dynamic code
Automating module creation
Finalising the module's script

All of this is entered in your xla or xlam add-in, which is assumed to be active. You have the choice of running this script via a shortcut or a Ribbon (which I personally use)

Accept User's Dynamic Code

Given a User Form object called UF_runEnteredVBA, .Show it, and save the user's inputs into inputVBA.

    ' Open User Form and store the entered script
    With UF_runEnteredVBA
        .Show vbModal
        If .isCancelled Then
            Exit Sub
        Else
            Dim inputVBA As String: inputVBA = .input_enteredVBA.Text
        End If
    End With

Automating Module Creation

    ' Initialise VB Project variables
    Dim proj As VBIDE.VBProject: Set proj = ActiveWorkbook.VBProject
    Dim modComp As VBIDE.VBComponent: Set modComp = proj.VBComponents.Add(vbext_ct_StdModule)
    If IsEmpty(modComp) Or (modComp Is Nothing) Then Exit Sub
    Dim code As VBIDE.CodeModule: Set code = modComp.CodeModule

This section automates work that would ordinarily be done in the Developer window. proj is the VB Project - set to equal the ActiveWorkbook as it is assumed the user desires to activate code on their immediate Workbook. modComp is set to the Module Component object of proj. It is initialised with Add, and effectively creates a module. vbext_ct_StdModule is a constant for the standard module (among the many types).

Finally there's VBIDE.CodeModule, which is object of the modComp. This allows code to automatically be enterted into the module. Effectively code is being written write code.

Finalising the module's script

Here - the user's dynamic code is entered into the module and executed.

    With code
        Call .DeleteLines(1, .CountOfLines)
        Call .InsertLines(1, "Public Sub runEnteredMacro()" & vbNewLine & "On Error Resume Next")
        Call .InsertLines(3, inputVBA & vbNewLine & "On Error GoTo 0" & vbNewLine & "End Sub")
    End With

As a measure of safety, the module is cleared via DeleteLines. Next the usual Public Sub keyword is entered along with a dummy procedure name runEnteredMacro() (On Error Resume Next is also added to avoid any errors ruining the pace of execution).

With vbNewLine - 2 rows worth of script are consumed. Therefore, the dynamic code and keyword closures begin on line 3 in InsertLines(3,...). And that's it! We've created a module with the user's dynamic code.

All that is left is to execute the dynamic script.

    ' Run code and remove created module
    Application.Run "'" & ActiveWorkbook.Name & "'!" & modComp.Name & ".runEnteredMacro"
    Call proj.VBComponents.Remove(modComp)

Through Application.Run, the created procedure is executed and the project is cleaned by deleting the created module, otherwise additional modules will be created everytime.

Recall that all the code explained will be located in an active and personal xla or xlam.

The final solution below.