Office-JS可以触发VBA工作簿或工作表事件过程吗?

问题描述

我正在尝试查看是否有一种方法可以在Office-JS中执行某些操作以触发事件过程在Excel VBA中运行，但是看来在执行JavaScript代码期间已禁用了VBA事件.

I'm trying to see if there is a way to do something in Office-JS that would trigger an event procedure to run in Excel VBA, but it looks like VBA events are disabled during the execution of the JavaScript code.

例如，假设我有一本工作簿，其工作表名为"Sheet1"，在该工作表模块中，我有以下代码:

Let's say for example that I have a workbook with a worksheet named "Sheet1" and in that sheet module I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" And Target.Value2 <> vbNullString Then
        MsgBox Target.Value2
    End If
End Sub

我期望的是，如果我更改单元格A1的内容，新内容将显示在消息框中，但是如果我在脚本实验室(基于空白代码段)，即使成功编辑了单元格A1，也不会触发该事件:

What I'm expecting is that if I change the content of cell A1, the new content will be displayed in a message box, but if I run the following snippet in Script Lab (based of the blank snippet), the event does not get triggered even though cell A1 gets edited successfully:

$("#run").click(() => tryCatch(run));

async function run() {
  await Excel.run(async (context) => {

    const sheet = context.workbook.worksheets.getItem("Sheet1");
    sheet.getRange("A1").values = 'Hello from Office-JS';

    await context.sync();
  });
}

/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
  try {
    await callback();
  } catch (error) {
    // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
    console.error(error);
  }
}

所以我的问题是:是否可以进行设置，以便JavaScript代码可以触发Worksheet_Change事件，否则，是否可以触发另一个工作表或工作簿事件?

So my question would be: Is there a way to set things up so that the JavaScript code can trigger the Worksheet_Change event and if not, is there another worksheet or workbook event that can be triggered?

一些上下文:我正在尝试找到此问题的解决方案，并认为一个好的解决方法是将VBA代码写入具有Office-JS的单元，然后使用Worksheet_Change事件过程执行在该单元中编写的VBA代码.

A bit of context:I was trying to find a solution to this question and thought that a good workaround would be to write VBA code to a cell with Office-JS and then use the Worksheet_Change event procedure to execute the VBA code written in that cell.

不幸的是，到目前为止还没有奏效，但是我认为值得问这个更具体的问题，因为解决这将是最后一个踏脚石，以便允许从Office-JS执行VBA代码(这很漂亮很好).

Unfortunately, that didn't work so far, but I think it's worth asking this more specific question as solving this would be the last stepping stone in order to allow VBA code to be executed from Office-JS (which would be pretty nice).

推荐答案

经过更多测试之后，我设法发现通过使用Office-Js创建新工作表触发了工作簿对象的Workbook_NewSheet事件过程: /p>

After some more testing, I managed to find that the Workbook_NewSheet event procedure of the workbook object gets triggered by creating a new worksheet with Office-Js:

var sheets = context.workbook.worksheets;
var sheet = sheets.add("NewSheet");
sheet.getRange("A1").values = "Hello from Office-JS";

因此，有可能在ThisWorkbook模块中具有以下代码以在该上下文中显示消息框:

So, it would be possible to have the following code in the ThisWorkbook Module to display the Message box in that context:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    If Sh.Name = "NewSheet" Then
        If Sh.Range("$A$1").Value2 <> vbNullString Then
            MsgBox Sh.Range("$A$1").Value2
        End If
    End If
End Sub

这里的好处是，即使Office-JS代码在创建后写入工作表，VBA中的事件也只能在JavaScript代码完成后才运行，这意味着单元格A1中的值将及时更改有关何时运行VBA代码的信息.

The nice thing here is that even if the Office-JS code writes to the sheet after creating it, the event in VBA only gets to run after the JavaScript code has finished meaning that the value in cell A1 will be changed in time for when the VBA code runs.

这篇关于Office-JS可以触发VBA工作簿或工作表事件过程吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！