Load/Save Excel VBA in C#, VB.NET

By running VBA within the Office applications, developers/programmers can build customized solutions and programs to enhance the capabilities of those applications. The VBA function of Excel is very powerful. Below I will show you how to use VBA by Spire.XLS.

VBA is the acronym for VB.NET for Applications. It is an implementation of Microsoft's event-driven programming language VB.NET 6 and its associated integrated development environment (IDE), which are built into most Microsoft Office applications. VBA is closely related to VB.NET and uses the VB.NET Runtime Library, but can normally only run code within a host application rather than as a standalone program. It can be used to control one application from another via OLE Automation.

Spire.XLS for .NET is a professional Excel .NET component that can be linked into any type of .NET 2.0, 3.5 or 4.0 projects, either ASP.NET web sites or Windows Forms application. Spire.XLS for .NET offers a combination of APIs and GUI controls for speeding up Excel programming in .NET platform-create new Excel documents from scratch, edit existing Excel documents and convert Excel files. At the same time, Spire.XLS supports VBA and it can load/Save Excel VBA.

Here comes to the steps:

  • Write a template with VBA program with which you can execute your work in Excel.
  • Create another workbook to load the VBA template.

In this demo, it generates a new worksheet named "test" with the VBA template we provide.

Please check the codes as below:

[C#]
using Spire.Xls;

namespace NumberFormat
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a workbook
            Workbook workbook = new Workbook();

            //Initailize worksheet
            workbook.LoadFromFile("VBASample.xls");
            Worksheet sheet = workbook.Worksheets[0];

            //VBA function
            sheet.Range["A1"].Text = "test";

            //Save the file
            workbook.SaveToFile("Sample.xls");

            //Launch the file
            System.Diagnostics.Process.Start("Sample.xls");
        }
    }
}
[VB.NET]
Imports Spire.Xls

Module Module1

    Sub Main()
        'Create a workbook
        Dim workbook As New Workbook()

        'Initailize worksheet
        workbook.LoadFromFile("VBASample.xls")
        Dim sheet As Worksheet = workbook.Worksheets(0)

        'VBA function
        sheet.Range("A1").Text = "test"

        'Save doc file.
        workbook.SaveToFile("Sample.xls")

        'Launching the MS Word file.
        System.Diagnostics.Process.Start("Sample.xls")
    End Sub
End Module