Manage VBA Codes of Excel Macro-Enabled Workbook with C++

Add a VBA Module in C++

Aspose.Cells allows you to add a new VBA Module and Macro Code using Aspose.Cells. Please use the Workbook.VbaProject.Modules.Add() method to add the new VBA Module inside the workbook.

The following sample code creates a new workbook and adds a new VBA Module and Macro Code and saves the output in the XLSM format. Once, you open the output XLSM file in Microsoft Excel and click the Developer > Visual Basic menu commands, you will see a module named “TestModule” and inside it, you will see the following macro code.

 Sub ShowMessage()

    MsgBox "Welcome to Aspose!"

End Sub

Here is the sample code to generate the output XLSM file with VBA Module and Macro Code.

#include <iostream>
#include "Aspose.Cells.h"

using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Create new workbook
    Workbook workbook;

    // Access first worksheet
    Worksheet worksheet = workbook.GetWorksheets().Get(0);

    // Add VBA Module
    int32_t idx = workbook.GetVbaProject().GetModules().Add(worksheet);

    // Access the VBA Module, set its name and codes
    VbaModule module = workbook.GetVbaProject().GetModules().Get(idx);
    module.SetName(u"TestModule");

    U16String codes = u"Sub ShowMessage()\r\n"
                      u"    MsgBox \"Welcome to Aspose!\"\r\n"
                      u"End Sub";
    module.SetCodes(codes);

    // Save the workbook
    U16String outputPath = outDir + u"output_out.xlsm";
    workbook.Save(outputPath, SaveFormat::Xlsm);

    std::cout << "VBA module added and workbook saved successfully!" << std::endl;

    Aspose::Cells::Cleanup();
    return 0;
}

Modify VBA or Macro in C++

You can modify VBA or Macro Code using Aspose.Cells. Aspose.Cells has added the following namespace and classes to read and modify the VBA project in the Excel file.

• Aspose::Cells::Vba
• VbaProject
• VbaModuleCollection
• VbaModule

This article will show you how to change the VBA or Macro Code inside the source Excel file using Aspose.Cells.

The following sample code loads the source Excel file which has the following VBA or Macro code inside it:

 Sub Button1_Click()

    MsgBox "This is test message."

End Sub

After the execution of Aspose.Cells sample code, the VBA or Macro code will be modified like this:

 Sub Button1_Click()

    MsgBox "This is Aspose.Cells message."

End Sub

You can download the source Excel file and the output Excel file from the given links.

#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    U16String inputFilePath = srcDir + u"sample.xlsm";
    U16String outputFilePath = outDir + u"output_out.xlsm";

    Workbook workbook(inputFilePath);

    VbaProject vbaProject = workbook.GetVbaProject();
    VbaModuleCollection modules = vbaProject.GetModules();

    for (int i = 0; i < modules.GetCount(); ++i)
    {
        VbaModule module = modules.Get(i);
        U16String code = module.GetCodes();
        U16String searchStr = u"This is test message.";
        U16String replaceStr = u"This is Aspose.Cells message.";
        
        if (code.IndexOf(searchStr) != -1)
        {
            U16String newCode;
            const char16_t* codeData = code.GetData();
            const char16_t* searchData = searchStr.GetData();
            int codeLen = code.GetLength();
            int searchLen = searchStr.GetLength();
            
            int pos = 0;
            int searchPos;
            
            while ((searchPos = code.IndexOf(searchStr)) != -1)
            {
                for (int j = pos; j < searchPos; j++)
                {
                    newCode += codeData[j];
                }
                
                newCode += replaceStr;
                
                pos = searchPos + searchLen;
            }
            
            for (int j = pos; j < codeLen; j++)
            {
                newCode += codeData[j];
            }
            
            module.SetCodes(newCode);
        }
    }

    workbook.Save(outputFilePath);

    std::cout << "VBA module codes updated successfully." << std::endl;

    Aspose::Cells::Cleanup();
    return 0;
}

Advance Topics

• Add a Library Reference to VBA Project in Workbook
• Assign Macro to Form Control
• Check if Digital Signature of VBA Code is Valid
• Check if VBA Code is Signed
• Check if VBA Project in a Workbook is Signed
• Check if VBA Project is Protected and Locked for Viewing
• Copy VBA Macro UserForm DesignerStorage from Template to Target Workbook
• Digitally Sign a VBA Code Project with Certificate
• Export VBA Certificate to File or Stream
• Filter VBA Project while Loading a Workbook
• Find out if VBA Project is Protected
• Password Protect the VBA Project of Excel Workbook