Start New Discussion within our Software Development Community

This snippet demonstrates how you can add VBA code to an Excel file at run time.

Prerequisites
In this snippet I am using Microsoft Excel 2013 with the Microsoft Excel 15.0 object library.

Before you can use this code, you will have to change a setting within Microsoft Excel to authorise the use of the VBA Project Object Model from external programs. This is a security feature to protect end users from malicious programs and is disabled by default. If you do not enable this feature then the code below will result in an exception stating "Programmatic access to Visual Basic Project is not trusted" as soon as you attempt to reference the VBA environment.

Enabling trust to the VBA Project Object Model

  1. Open Excel and access the Options section.
  2. Select the Trust Center section
  3. Click the Trust Center Settings button
  4. Select the Macro Settings section
  5. Tick the box labelled "Trust access to the VBA project object model"

The code
You will need the following Imports statements:

Imports Microsoft.Office.Interop
Imports Microsoft.Vbe.Interop
Imports System.Runtime.InteropServices
Imports System.Text

Then:

Dim excelApplication As New Excel.Application
Dim excelWorkbooks As Excel.Workbooks = excelApplication.Workbooks
Dim excelWorkbook As Excel.Workbook = excelWorkbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)

Try

    'Create a VB Project and add a standard module component
    Dim vbProject As VBProject = excelWorkbook.VBProject
    Dim vbComponent As VBComponent = vbProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule)

    'Your code
    Dim codeString As New StringBuilder
    codeString.AppendLine("Public Sub SaySomething()")
    codeString.AppendLine("MsgBox ""Hello""")
    codeString.AppendLine("End Sub")

    'Add your code to the standard module component
    vbComponent.CodeModule.AddFromString(codeString.ToString)

    'Save and quit
    excelWorkbook.SaveAs("C:\Temp\MyVBAExcelFile.xlsm", Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled)

Catch ex As Exception

    MessageBox.Show(ex.Message, "Exception Details", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

Finally

    excelApplication.Quit()

    Marshal.ReleaseComObject(excelWorkbooks)
    Marshal.ReleaseComObject(excelWorkbook)
    Marshal.ReleaseComObject(excelApplication)

End Try
Imports Microsoft.Office.Interop
Imports Microsoft.Vbe.Interop
Imports System.Runtime.InteropServices
Imports System.Text

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim excelApplication As New Excel.Application
        Dim excelWorkbooks As Excel.Workbooks = excelApplication.Workbooks
        Dim excelWorkbook As Excel.Workbook = excelWorkbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)

        Try

            'Create a VB Project and add a standard module component
            Dim vbProject As VBProject = excelWorkbook.VBProject
            Dim vbComponent As VBComponent = vbProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule)

            'Your code
            Dim codeString As New StringBuilder
            codeString.AppendLine("Public Sub SaySomething()")
            codeString.AppendLine("MsgBox ""Hello""")
            codeString.AppendLine("End Sub")

            'Add your code to the standard module component
            vbComponent.CodeModule.AddFromString(codeString.ToString)

            'Save and quit
            excelWorkbook.SaveAs("C:\Temp\MyVBAExcelFile.xlsm", Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled)

        Catch ex As Exception

            MessageBox.Show(ex.Message, "Exception Details", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

        Finally

            excelApplication.Quit()

            Marshal.ReleaseComObject(excelWorkbooks)
            Marshal.ReleaseComObject(excelWorkbook)
            Marshal.ReleaseComObject(excelApplication)

        End Try

    End Sub
	
End Class

I am a highly motivated individual and pride myself in my technical and people skills. I am a team player and enjoy motivating individuals to achieve both their goals and the goals of the business.

I have over 17 years of software development experience and have managed a number of teams during this time. I am passionate about software development, tools and processes that can aid development teams to become as efficient and effective as possible. I enjoy creating and delivering new products that are customer focused and solve real business problems.

The article starter is a financial contributor. Sponsored articles offer a bounty for quality replies.