Windows版Excel中Office自定义函数TS_BIND无法触发调用求助

DevOps 小满

2026-7-24

Excel自定义函数TS_BIND在Windows端无法调用的问题

我们开发了自定义函数TS_BIND，用于实现两个单元格的双向数据绑定：选中需要保持值一致的两个单元格，修改其中一个的值，另一个会同步更新。该函数在Mac版和网页版Excel中运行正常，但在Windows版Excel中完全不会被内部调用。本加载项采用Shared Runtime模式，相关环境信息如下：

MS Office版本及内部版本号：Version 2208 - Build 15601.20578
Windows版本及内部版本号：Version 21H2 - Build 19044.2728

functions.ts代码

/* eslint-disable @typescript-eslint/no-explicit-any */
/** global clearInterval, console, CustomFunctions, setInterval **/

interface IRangeData {
  index: number;
  address: string;
  value: any;
}

/**
 * Two-way data binding between two cells. Select two cells that need to have the same value, this function will keep them in sync. Alter the value of one cell and the other cell will be updated with the same value.
 * @customfunction BIND
 * @param {any} cell1 The first cell to bind
 * @param {any} cell2 The second cell to bind
 * @returns {any} The updated value
 * @requiresAddress
 * @requiresParameterAddresses
 */
export function bind(cell1: any, cell2: any, invocation: CustomFunctions.Invocation): any {
  Excel.run(async (context) => {
    let range1: IRangeData;
    let range2: IRangeData;

    let tsCache = context.workbook.worksheets.getItemOrNullObject("ts-bind-cache");
    await context.sync();
    if (tsCache.isNullObject) tsCache = context.workbook.worksheets.add("ts-bind-cache");
    tsCache.visibility = "Hidden";

    const addressesRange = tsCache.getRange("A1").getResizedRange(999, 1);
    addressesRange.load("values");
    await context.sync();

    const addresses = addressesRange.values.filter((address) => address[0] !== null && address[0] !== "");

    const sheetCellCode1 = invocation.parameterAddresses[0]?.replace(/['+]/g, "");
    const sheetCellCode2 = invocation.parameterAddresses[1]?.replace(/['+]/g, "");

    addresses.forEach((address, index) => {
      if (address[0] === sheetCellCode1) {
        range1 = {
          index,
          address: address[0],
          value: address[1],
        };
      }
      if (address[0] === sheetCellCode2) {
        range2 = {
          index,
          address: address[0],
          value: address[1],
        };
      }
    });

    if (!range1?.address) {
      const cacheRange1 = tsCache.getRange(`A${addresses.length + 1}:B${addresses.length + 1}`);
      cacheRange1.values = [[sheetCellCode1, cell1]];
      await context.sync();
    }

    if (!range2?.address) {
      const cacheRange2 = tsCache.getRange(`A${addresses.length + 2}:B${addresses.length + 2}`);
      cacheRange2.values = [[sheetCellCode2, cell2]];
      await context.sync();
    }

    if (cell1 !== cell2) {
      if (range1?.value !== cell1) {
        const sheetName = sheetCellCode2.split("!")[0];
        const sheet = context.workbook.worksheets.getItemOrNullObject(sheetName);
        await context.sync();

        if (!sheet.isNullObject) {
          const range = sheet.getRange(sheetCellCode2.split("!")[1]);
          range.values = [[cell1]];
          await context.sync();

          if (range1) {
            const cacheRange1 = tsCache.getRange(`B${range1.index + 1}`);
            cacheRange1.values = [[cell1]];
          }
          if (range2) {
            const cacheRange2 = tsCache.getRange(`B${range2.index + 1}`);
            cacheRange2.values = [[cell1]];
          }
          await context.sync();
        }
      }

      if (range2?.value !== cell2) {
        const sheetName = sheetCellCode1.split("!")[0];
        const sheet = context.workbook.worksheets.getItemOrNullObject(sheetName);
        await context.sync();

        if (!sheet.isNullObject) {
          const range = sheet.getRange(sheetCellCode1.split("!")[1]);
          range.values = [[cell2]];
          await context.sync();

          if (range1) {
            const cacheRange1 = tsCache.getRange(`B${range1.index + 1}`);
            cacheRange1.values = [[cell2]];
          }
          if (range2) {
            const cacheRange2 = tsCache.getRange(`B${range2.index + 1}`);
            cacheRange2.values = [[cell2]];
          }
          await context.sync();
        }
      }
    }
  });

  return cell1;
}

manifest.xml代码

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<OfficeApp xmlns="http://schemas.microsoft.com/office/appforoffice/1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0" xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="TaskPaneApp">
  <Id>749ebdde-13cf-459b-829b-89c11cfa2b23</Id>
  <Version>1.1.1.0</Version>
  <ProviderName>TermSheet LLC</ProviderName>
  <DefaultLocale>en-US</DefaultLocale>
  <DisplayName DefaultValue="TermSheet for Excel"/>
  <Description DefaultValue="TermSheet Add-In for Microsoft Excel."/>
  <IconUrl DefaultValue="https://localhost:3000/assets/icon-32.png"/>
  <HighResolutionIconUrl DefaultValue="https://localhost:3000/assets/icon-64.png"/>
  <SupportUrl DefaultValue="https://termsheet.com/"/>
  <AppDomains>
    <AppDomain>https://termsheet.com</AppDomain>
  </AppDomains>
  <Hosts>
    <Host Name="Workbook"/>
  </Hosts>
  <Requirements>
    <Sets DefaultMinVersion="1.1">
      <Set Name="SharedRuntime" MinVersion="1.1"/>
    </Sets>
  </Requirements>
  <DefaultSettings>
    <SourceLocation DefaultValue="https://localhost:3000/taskpane.html"/>
  </DefaultSettings>
  <Permissions>ReadWriteDocument</Permissions>
  <VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1_0">
    <Hosts>
      <Host xsi:type="Workbook">
        <Runtimes>
          <Runtime resid="Taskpane.Url" lifetime="long"/>
        </Runtimes>
        <AllFormFactors>
          <ExtensionPoint xsi:type="CustomFunctions">
            <Script>
              <SourceLocation resid="Functions.Script.Url"/>
            </Script>
            <Page>
              <SourceLocation resid="Functions.Page.Url"/>
            </Page>
            <Metadata>
              <SourceLocation resid="Functions.Metadata.Url"/>
            </Metadata>
            <Namespace resid="Functions.Namespace"/>
          </ExtensionPoint>
        </AllFormFactors>
        <DesktopFormFactor>
          <GetStarted>
            <Title resid="GetStarted.Title"/>
            <Description resid="GetStarted.Description"/>
            <LearnMoreUrl resid="GetStarted.LearnMoreUrl"/>
          </GetStarted>
          <FunctionFile resid="Taskpane.Url"/>
          <ExtensionPoint xsi:type="PrimaryCommandSurface">
            <OfficeTab id="TabHome">
              <Group id="CommandsGroup">
                <Label resid="CommandsGroup.Label"/>
                <Icon>
                  <bt:Image size="16" resid="Icon.16x16"/>
                  <bt:Image size="32" resid="Icon.32x32"/>
                  <bt:Image size="80" resid="Icon.80x80"/>
                </Icon>
                <Control xsi:type="Button" id="TaskpaneButton">
                  <Label resid="TaskpaneButton.Label"/>
                  <Supertip>
                    <Title resid="TaskpaneButton.Label"/>
                    <Description resid="TaskpaneButton.Tooltip"/>
                  </Supertip>
                  <Icon>
                    <bt:Image size="16" resid="Icon.16x16"/>
                    <bt:Image size="32" resid="Icon.32x32"/>
                    <bt:Image size="80" resid="Icon.80x80"/>
                  </Icon>
                  <Action xsi:type="ShowTaskpane">
                    <TaskpaneId>ButtonId1</TaskpaneId>
                    <SourceLocation resid="Taskpane.Url"/>
                  </Action>
                </Control>
              </Group>
            </OfficeTab>
          </ExtensionPoint>
        </DesktopFormFactor>
      </Host>
    </Hosts>
    <Resources>
      <bt:Images>
        <bt:Image id="Icon.16x16" DefaultValue="https://localhost:3000/assets/icon-16.png"/>
        <bt:Image id="Icon.32x32" DefaultValue="https://localhost:3000/assets/icon-32.png"/>
        <bt:Image id="Icon.80x80" DefaultValue="https://localhost:3000/assets/icon-80.png"/>
      </bt:Images>
      <bt:Urls>
        <bt:Url id="Functions.Script.Url" DefaultValue="https://localhost:3000/functions.js"/>
        <bt:Url id="Functions.Metadata.Url" DefaultValue="https://localhost:3000/functions.json"/>
        <bt:Url id="Functions.Page.Url" DefaultValue="https://localhost:3000/taskpane.html"/>
        <bt:Url id="GetStarted.LearnMoreUrl" DefaultValue="https://go.microsoft.com/fwlink/?LinkId=276812"/>
        <bt:Url id="Taskpane.Url" DefaultValue="https://localhost:3000/taskpane.html"/>
      </bt:Urls>
      <bt:ShortStrings>
        <bt:String id="Functions.Namespace" DefaultValue="TS"/>
        <bt:String id="GetStarted.Title" DefaultValue="Get started with TermSheet!"/>
        <bt:String id="CommandsGroup.Label" DefaultValue="TermSheet for Excel"/>
        <bt:String id="TaskpaneButton.Label" DefaultValue="TermSheet for Excel"/>
      </bt:ShortStrings>
      <bt:LongStrings>
        <bt:String id="GetStarted.Description" DefaultValue="Manage your TermSheet worksheets."/>
        <bt:String id="TaskpaneButton.Tooltip" DefaultValue="Click to Open TermSheet"/>
      </bt:LongStrings>
    </Resources>
  </VersionOverrides>
</OfficeApp>

问题排查与解决方案

异步逻辑未正确处理
原函数中Excel.run是异步操作，但函数本身未使用await等待执行完成，Windows端Excel对异步自定义函数的规范要求更严格，会直接跳过未正确等待的逻辑。
修改方式：将函数改为异步函数，添加await和异常捕获：

export async function bind(cell1: any, cell2: any, invocation: CustomFunctions.Invocation): Promise<any> {
  try {
    await Excel.run(async (context) => {
      // 保留原函数内部逻辑
    });
  } catch (error) {
    console.error("TS_BIND执行异常:", error);
    throw error; // 抛出错误便于Excel显示异常提示
  }
  return cell1;
}

Shared Runtime依赖上下文激活
Windows端的Shared Runtime需要加载项的任务窗格被激活过才能正常运行自定义函数。可以在加载项启动时自动打开任务窗格，或者引导用户先点击一次加载项按钮激活上下文。
参数地址处理兼容性
原代码中用replace(/['+]/g, "")处理参数地址，Windows端返回的地址格式可能与Mac/网页端存在差异，导致地址匹配失败。建议保留原始地址格式进行缓存匹配，或使用Excel API的Range.getAddress()方法获取标准地址。
Office版本补丁更新
当前使用的Office 2208版本存在部分Shared Runtime相关的已知bug，建议更新到最新的Office版本，或安装对应补丁修复兼容性问题。

内容的提问来源于stack exchange，提问作者Marcos Natã Santos

Windows版Excel中Office自定义函数TS_BIND无法触发调用求助

functions.ts代码 #

manifest.xml代码 #

问题排查与解决方案 #

functions.ts代码

manifest.xml代码

问题排查与解决方案