Skip to main content

VBA and Macros

File Format Support (click to show)

Note that XLSX does not support macros. The XLSM file format is nearly identical to XLSX and supports macros.

FormatsBasicStorage Representation
XLSMvbaProject.bin file in container
XLSXNot supported in format (use XLSM)
XLSBvbaProject.bin file in container
XLSIntercalated in CFB container

X (✕) marks features that are not supported by the file formats. There is no way to embed VBA in the XLSX format.

VBA Macros are stored in a special data blob that is exposed in the vbaraw property of the workbook object when the bookVBA option is true. They are supported in XLSM, XLSB, and BIFF8 XLS formats. The supported format writers automatically insert the data blobs if it is present in the workbook and associate with the worksheet names.

The vbaraw property stores raw bytes. SheetJS Pro offers a special component for extracting macro text from the VBA blob, editing the VBA project, and exporting new VBA blobs.

Demos

The export demos focus on an example that includes the following user-defined functions:

Function GetFormulaA1(Cell As Range) As String
GetFormulaA1 = Cell.Formula
End Function

Function GetFormulaRC(Cell As Range) As String
GetFormulaRC = Cell.Formula2R1C1
End Function

Copying Macros

After downloading the sample file, the demo extracts the VBA blob and creates a new workbook including the VBA blob. Click the button to create the file and open in a spreadsheet editor that supports VBA:

Result
Loading...
Live Editor

Extracting VBA Blobs

To extract blobs, bookVBA: true must be set in the read or readFile call.

The following example extracts the embedded VBA blob in a workbook:

Result
Loading...
Live Editor

Exporting Blobs

To ensure the writers export the VBA blob:

  • The output format must support VBA (xlsm or xlsb or xls or biff8)
  • The workbook object must have a valid vbaraw field
  • The write or writeFile call must include the option bookVBA: true

This example uses vbaProject.bin from the sample file:

Result
Loading...
Live Editor

Details

Code Names

Excel will use ThisWorkbook (or a translation like DieseArbeitsmappe) as the default Code Name for the workbook. Each worksheet will be identified using the default Sheet# naming pattern even if the worksheet names have changed.

A custom workbook code name will be stored in wb.Workbook.WBProps.CodeName. For exports, assigning the property will override the default value.

Worksheet and Chartsheet code names are in the worksheet properties object at wb.Workbook.Sheets[i].CodeName. Macrosheets and Dialogsheets are ignored.

The readers and writers preserve the code names, but they have to be manually set when adding a VBA blob to a different workbook.

Macrosheets

Older versions of Excel also supported a non-VBA "macrosheet" sheet type that stored automation commands. These are exposed in objects with the !type property set to "macro".

Under the hood, Excel treats Macrosheets as normal worksheets with special interpretation of the function expressions.

Detecting Macros in Workbooks

The vbaraw field will only be set if macros are present. Macrosheets will be explicitly flagged. Combining the two checks yields a simple function:

function wb_has_macro(wb/*:workbook*/)/*:boolean*/ {
if(!!wb.vbaraw) return true;
const sheets = wb.SheetNames.map((n) => wb.Sheets[n]);
return sheets.some((ws) => !!ws && ws['!type']=='macro');
}