Add VBA code to Excel file

djjeavons 0 1K Views

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview