VBA Macro in Excel Java

VBA is a programming language used to automate various operations in MS Excel files. Particularly, VBA macros are user-defined codes that let you speed up the spreadsheet manipulation tasks. In this article, you will learn how to work with VBA macros in Excel files programmatically. Ultimately, you will be able to add and modify VBA macros in Excel using Java.

Work with Excel VBA Macros using Java API

In order to work with VBA macros in Excel files, we will use Aspose.Cells for Java. It is a powerful spreadsheet automation API that lets you create, modify and convert Excel files. Furthermore, it simplifies the manipulation of VBA macros. You can either download the API or install it using the following Maven configurations.

<repository>
    <id>AsposeJavaAPI</id>
    <name>Aspose Java API</name>
    <url>https://repository.aspose.com/repo/</url>
</repository>
<dependency>
    <groupId>com.aspose</groupId>
    <artifactId>aspose-cells</artifactId>
    <version>21.5</version>
</dependency>

Add VBA Macros to an Excel Workbook in Java

The following are the steps to add VBA macro in Excel files in Java.

The following code sample shows how to add a VBA macro in an Excel file using Java.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Load Excel workbook
Workbook workbook = new Workbook("workbook.xlsm");
// Access first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Add VBA Module
int idx = workbook.getVbaProject().getModules().add(worksheet);
// Access the VBA Module, set its name and codes
VbaModule module = workbook.getVbaProject().getModules().get(idx);
module.setName("TestModule");
module.setCodes("Sub ShowMessage()" + "\r\n" + " MsgBox \"Welcome to Aspose!\"" + "\r\n" + "End Sub");
// Save the workbook
workbook.save("output.xlsm", SaveFormat.XLSM);

Modify VBA Macro in an Excel Workbook in Java

The following are the steps to modify VBA macro in Excel files using Java.

The following code sample shows how to modify the VBA macro in an Excel file using Java.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Create workbook object from source Excel file
Workbook workbook = new Workbook("sample.xlsm");
// Change the VBA Module Code
VbaModuleCollection modules = workbook.getVbaProject().getModules();
for (int i = 0; i < modules.getCount(); i++) {
VbaModule module = modules.get(i);
String code = module.getCodes();
// Replace the original message with the modified message
if (code.contains("This is test message.")) {
code = code.replace("This is test message.", "This is Aspose.Cells message.");
module.setCodes(code);
}
}
// Save the output Excel file
workbook.save("output.xlsm");

Get a Free License

You can use Aspose.Cells for Java without evaluation limitations using a temporary license. Get yours now.

Conclusion

VBA macros are used to automate various types of tasks in spreadsheets. In order to work with VBA macros programmatically, this article covered how to add and modify code in VBA modules using Java. To learn more, you can explore documentation of Java spreadsheet manipulation API. Also, you can post your questions or queries on our forum.

See Also

Tip: You may want to try Aspose online VBA macro removal tool.