Add VBA Code and References to Excel Document

 

We can add VBA Code as
well as References (required by the VBA code to compile) to any Excel Document
using .NET Code.

Method to Add VBA Code

public
static void
AddVBACode(Workbook Workbook, String VBCode, String
ModuleName)

{

Microsoft.Vbe.Interop.VBProject
Project = Workbook.VBProject;

     
Microsoft.Vbe.Interop.VBComponent Module =
Project.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);

     
Module.Name = ModuleName;

     
Microsoft.Vbe.Interop.CodeModule Code =
Module.CodeModule;

     
Code.AddFromString(VBCode);

}

(We can change
the Module Type to Standard Module or Class Module, by changing the Microsoft.Vbe.Interop.vbext_ComponentType enum in the add method)

Code Snippet to
add the references

The references
can be added using either the file path or the GUID. Its always recommended to
check, if the Reference is already added in the target document, before
attempting to add it programmatically. The collection Workbook.VBProject.References collection contains the
References already added in the Workbook document object.

Workbook.VBProject.References.AddFromFile("C:\\Program Files\\Microsoft
Office\\Office12\\XLSTART\\my-Add-In.xla");

Workbook.VBProject.References.AddFromGuid("{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0);

To
retrieve the GUID’s for the References to be added, we can add the references
to any Excel Workbook and run the following macro :-

Option
Explicit

Sub ListGUID()
    Dim Ref As Object, N&
    Sheets.Add
    Application.ScreenUpdating = False
    Cells.Font.Size = 8
    With Rows("1:1")
          .Font.Size = 9
          .Font.Bold = True
          .Font.ColorIndex = 9
          .Font.Underline = xlUnderlineStyleSingle
    End With
    [A1:D1] = _
    Array("Description", "Name", "Use:
ThisWorkbook.VBProject.References.AddFromGuid", "Path")
    For Each Ref In ActiveWorkbook.VBProject.References
          [A65536].End(xlUp).Offset(1, 0) =
Ref.Description
          [B65536].End(xlUp).Offset(1, 0) = Ref.Name
          [C65536].End(xlUp).Offset(1, 0) =
"""" & Ref.GUID & """" &
", " & Ref.Major & ", " & Ref.minor
          [D65536].End(xlUp).Offset(1, 0) =
Ref.FullPath
    Next
    Columns("A:D").EntireColumn.AutoFit
    Set Ref = Nothing
End Sub