VBA and MacrosVBA和宏

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.请注意，XLSX不支持宏。XLSM文件格式与XLSX几乎相同，并支持宏。

Formats	Basic	Storage Representation存储表示
XLSM	✔	vbaProject.bin file in container容器中的vbaProject.bin文件
XLSX	✕	Not supported in format (use XLSM)格式不支持（使用XLSM）
XLSB	✔	vbaProject.bin file in container容器中的vbaProject.bin文件
XLS	✔	Intercalated in CFB container插入CFB容器

X (✕) marks features that are not supported by the file formats. There is no way to embed VBA in the XLSX format.标记文件格式不支持的功能。无法在XLSX格式中嵌入VBA。

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. 当bookVBA选项为true时，VBA宏存储在工作簿对象的vbaraw属性中公开的特殊数据blob中。They are supported in XLSM, XLSB, and BIFF8 XLS formats. 它们支持XLSM、XLSB和BIFF8 XLS格式。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.vbaraw属性存储原始字节。SheetJS Pro offers a special component for extracting macro text from the VBA blob, editing the VBA project, and exporting new VBA blobs.提供了一个特殊组件，用于从VBA blob中提取宏文本、编辑VBA工程和导出新的VBA blob。

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:下载示例文件后，演示将提取VBA blob并创建一个包含VBA blob的新工作簿。单击按钮创建文件并在支持VBA的电子表格编辑器中打开：

• Web Browser
• NodeJS

Result

Loading...

Live Editor

function SheetJSVBAFormula() { return ( <button onClick={async () => { /* Extract VBA Blob from test file */ const url = "/vba/SheetJSVBAFormula.xlsm"; const raw_data = await (await fetch(url)).arrayBuffer(); const blob = XLSX.read(raw_data, {bookVBA: true}).vbaraw; /* generate worksheet and workbook */ const worksheet = XLSX.utils.aoa_to_sheet([ ["Cell", "A1", "RC"], [ {t:"n", f:"LEN(A1)"}, // A2 {t:"s", f:"GetFormulaA1(A2)"}, // B2 {t:"s", f:"GetFormulaRC(A2)"} // C2 ] ]); const workbook = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1"); /* add VBA blob to new workbook */ workbook.vbaraw = blob; /* create an XLSM file and try to save to SheetJSVBANeu.xlsm */ XLSX.writeFile(workbook, "SheetJSVBANeu.xlsm", { bookVBA: true }); }}><b>Click to Generate file!</b></button> ); }

function SheetJSVBAFormula() { return ( <button onClick={async () => {
  /* Extract VBA Blob from test file从测试文件中提取VBA Blob */
  const url = "/vba/SheetJSVBAFormula.xlsm";
  const raw_data = await (await fetch(url)).arrayBuffer();
  const blob = XLSX.read(raw_data, {bookVBA: true}).vbaraw;

  /* generate worksheet and workbook生成工作表和工作簿 */
  const worksheet = XLSX.utils.aoa_to_sheet([
    ["Cell", "A1", "RC"],
    [
      {t:"n", f:"LEN(A1)"},      // A2
      {t:"s", f:"GetFormulaA1(A2)"},  // B2
      {t:"s", f:"GetFormulaRC(A2)"}   // C2
    ]
  ]);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");

  /* add VBA blob to new workbook将VBA blob添加到新工作簿 */
  workbook.vbaraw = blob;

  /* create an XLSM file and try to save to SheetJSVBANeu.xlsm创建XLSM文件并尝试保存到SheetJSVBANeu.xlsm */
  XLSX.writeFile(workbook, "SheetJSVBANeu.xlsm", { bookVBA: true });
}}><b>Click to Generate file!</b></button> ); }

0) Install the dependencies:安装依赖项：

npm init -y
npm i --save https://cdn.sheetjs.com/xlsx-0.20.1/xlsx-0.20.1.tgz

1) Save the following script to generate_file.js:将以下脚本保存到generate_file.js：

generate_file.js

const XLSX = require("xlsx");
(async() => {
/* Extract VBA Blob from test file从测试文件中提取VBA Blob */
const url = "https://docs.sheetjs.com/vba/SheetJSVBAFormula.xlsm";
const raw_data = await (await fetch(url)).arrayBuffer();
const blob = XLSX.read(raw_data, {bookVBA: true}).vbaraw;

/* generate worksheet and workbook */
const worksheet = XLSX.utils.aoa_to_sheet([
  ["Cell", "A1", "RC"],
  [
    {t:"n", f:"LEN(A1)"},      // A2
    {t:"s", f:"GetFormulaA1(A2)"},  // B2
    {t:"s", f:"GetFormulaRC(A2)"}   // C2
  ]
]);
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");

/* add VBA blob to new workbook */
workbook.vbaraw = blob;

/* create an XLSM file and try to save to SheetJSVBANeu.xlsm创建XLSM文件并尝试保存到SheetJSVBANeu.xlsm */
XLSX.writeFile(workbook, "SheetJSVBANeu.xlsm", { bookVBA: true });
})();

2) Run the script:运行脚本：

node generate_file.js

This script will generate SheetJSVBANeu.xlsm.此脚本将生成SheetJSVBANeu.xlsm。

Extracting VBA Blobs提取VBA Blob

To extract blobs, bookVBA: true must be set in the read or readFile call.若要提取Blob，必须在read或readFile调用中设置bookVBA: true。

The following example extracts the embedded VBA blob in a workbook:以下示例提取工作簿中嵌入的VBA blob：

• Web Browser
• NodeJS

Result

Loading...

Live Editor

function SheetJSExtractVBA(props) { const [msg, setMsg] = React.useState("Select a macro-enabled file"); return ( <> <b>{msg}</b><br/> <input type="file" onChange={async(e) => { /* parse workbook with bookVBA: true */ const wb = XLSX.read(await e.target.files[0].arrayBuffer(), {bookVBA: true}); /* get vba blob */ if(!wb.vbaraw) return setMsg("No VBA found!"); const blob = wb.vbaraw; /* download to vbaProject.bin */ setMsg("Attempting to download vbaProject.bin"); const url = URL.createObjectURL(new Blob([blob])); const a = document.createElement("a"); a.download = "vbaProject.bin"; a.href = url; document.body.appendChild(a); a.click(); document.body.removeChild(a); }}/> </> ); }

function SheetJSExtractVBA(props) {
  const [msg, setMsg] = React.useState("Select a macro-enabled file");
  return ( <>
    <b>{msg}</b><br/>
    <input type="file" onChange={async(e) => {
      /* parse workbook with bookVBA: true */
      const wb = XLSX.read(await e.target.files[0].arrayBuffer(), {bookVBA: true});

      /* get vba blob */
      if(!wb.vbaraw) return setMsg("No VBA found!");
      const blob = wb.vbaraw;

      /* download to vbaProject.bin */
      setMsg("Attempting to download vbaProject.bin");
      const url = URL.createObjectURL(new Blob([blob]));
      const a = document.createElement("a");
      a.download = "vbaProject.bin"; a.href = url;
      document.body.appendChild(a); a.click();
      document.body.removeChild(a);
    }}/>
  </> );
}

0) Install the dependencies:安装依赖项：

npm init -y
npm i --save https://cdn.sheetjs.com/xlsx-0.20.1/xlsx-0.20.1.tgz

1) Save the following script to extract_vba.js:将以下脚本保存到extract_vba.js：

extract_vba.js

const fs = require("fs"), XLSX = require("xlsx");
const wb = XLSX.readFile(process.argv[2], { bookVBA: true });
if(!wb.vbaraw) throw new Error("Could not find VBA blob!");
fs.writeFileSync("vbaProject.bin", wb.vbaraw);

2) Run the script:运行脚本：

node extract_vba.js SheetJSMacroEnabled.xlsm

This script will generate vbaProject.bin. It can be added to a new workbook.此脚本将生成vbaProject.bin。它可以添加到新工作簿中。

Exporting Blobs导出Blob

To ensure the writers export the VBA blob:要确保编写器导出VBA blob，请执行以下操作：

• The output format must support VBA (xlsm or xlsb or xls or biff8)输出格式必须支持VBA（xlsm或xlsb或xls或biff8）
• The workbook object must have a valid vbaraw field工作簿对象必须具有有效的vbaraw字段
• The write or writeFile call must include the option bookVBA: truewrite或writeFile调用必须包括选项bookVBA: true

This example uses vbaProject.bin from the sample file:此示例使用示例文件中的vbaProject.bin：

Result

Loading...

Live Editor

function SheetJSVBAPrepared() { return ( <button onClick={async () => { /* Download prepared VBA blob */ const url = "/vba/vbaProject.bin"; const blob = new Uint8Array(await (await fetch(url)).arrayBuffer()); /* generate worksheet and workbook */ const worksheet = XLSX.utils.aoa_to_sheet([ ["Cell", "A1", "RC"], [ {t:"n", f:"LEN(A1)"}, // A2 {t:"s", f:"GetFormulaA1(A2)"}, // B2 {t:"s", f:"GetFormulaRC(A2)"} // C2 ] ]); const workbook = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1"); /* add VBA blob to new workbook */ workbook.vbaraw = blob; /* create an XLSM file and try to save to SheetJSVBAPreparedNeu.xlsm */ XLSX.writeFile(workbook, "SheetJSVBAPreparedNeu.xlsm", { bookVBA: true }); }}><b>Click to Generate file!</b></button> ); }

function SheetJSVBAPrepared() { return ( <button onClick={async () => {
  /* Download prepared VBA blob下载准备好的VBA blob */
  const url = "/vba/vbaProject.bin";
  const blob = new Uint8Array(await (await fetch(url)).arrayBuffer());

  /* generate worksheet and workbook生成工作表和工作簿 */
  const worksheet = XLSX.utils.aoa_to_sheet([
    ["Cell", "A1", "RC"],
    [
      {t:"n", f:"LEN(A1)"},      // A2
      {t:"s", f:"GetFormulaA1(A2)"},  // B2
      {t:"s", f:"GetFormulaRC(A2)"}   // C2
    ]
  ]);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");

  /* add VBA blob to new workbook将VBA blob添加到新工作簿 */
  workbook.vbaraw = blob;

  /* create an XLSM file and try to save to SheetJSVBAPreparedNeu.xlsm创建XLSM文件并尝试保存到SheetJSVBAPreparedNeu.xlsm */
  XLSX.writeFile(workbook, "SheetJSVBAPreparedNeu.xlsm", { bookVBA: true });
}}><b>Click to Generate file!</b></button> ); }

Details细节

Code Names代码名称

Excel will use ThisWorkbook (or a translation like DieseArbeitsmappe) as the default Code Name for the workbook. Excel将使用ThisWorkbook（或类似DieseAlpeitsmappe的翻译）作为工作簿的默认代码名。Each worksheet will be identified using the default Sheet# naming pattern even if the worksheet names have changed.即使工作表名称已更改，也将使用默认的Sheet#命名模式来标识每个工作表。

A custom workbook code name will be stored in wb.Workbook.WBProps.CodeName. For exports, assigning the property will override the default value.自定义工作簿代码名称将存储在wb.Workbook.WBProps.CodeName中。对于导出，指定特性将覆盖默认值。

Worksheet and Chartsheet code names are in the worksheet properties object at wb.Workbook.Sheets[i].CodeName. Macrosheets and Dialogsheets are ignored.工作表和图表代码名称位于wb.Workbook.Sheets[i].CodeName的工作表属性对象中。宏工作表和对话框工作表将被忽略。

The readers and writers preserve the code names, but they have to be manually set when adding a VBA blob to a different workbook.读取器和编写器保留代码名称，但在将VBA blob添加到其他工作簿时必须手动设置代码名称。

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".旧版本的Excel还支持存储自动化命令的非VBA“macrossheet”图纸类型。这些都暴露在带有的对象中!type属性设置为"macro"。

Under the hood, Excel treats Macrosheets as normal worksheets with special interpretation of the function expressions.在后台，Excel将宏表视为对函数表达式进行特殊解释的普通工作表。

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:只有当存在宏时，才会设置vbaraw字段。将显式标记宏表。将这两个检查组合在一起会产生一个简单的函数：

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');
}