excel - 有没有办法从 Excel Addin 将宏插入到工作表中？

Question

我搜索了文档 Office Excel API ，但根本找不到任何提及，如何实现。

问题:

幸运的 API 功能或某种错误可以通过 VBA 宏轻松解决。

但是，要做到这一点，我们只有两种可能性:

手动将宏插入到工作表中，这将在工作表上捕获一些事件，并且来自插件的 JS 将触发该事件(此解决方案来自非常古老的论坛(如果我能找到链接，我将在此处插入))。

在插件运行时通过JS代码插入宏到工作表(context.workbook.worksheet？)，甚至它也不能被插件执行，但在这种情况下，最终用户根本不需要管理宏——我们可以进行宏的处理使用相同的逻辑运行更流畅(JS 将宏插入工作表中，更改工作表中的一些值，然后触发一些宏，然后(例如在 JS 中超时或仅通过 JS 中的另一个事件我们可以轻松删除整个带有此宏的工作表))。

所以问题是，是否有可能以某种方式使用 Excel API 制作类似于解决方案 2 的东西？
(从插件插入/删除VBA代码的某种功能)

我将不胜感激任何帮助!

澄清问题的例子

我正在使用 JavaScript API，但不幸的是，该 API 并没有涵盖 VBA 中已经存在的全部功能(我希望还没有)。让我用一个简单的例子来解释它:

想象一个任务:

我们需要从工作簿 1 的工作表 1 中复制一些信息

然后我们需要创建一本书并将值放入新工作簿 2

然后我们需要向用户建议需要保存它(新工作簿 2)的位置。

然后我们需要保存并关闭工作簿 2。

VBA 很容易解决这个问题，但在 JS API 的情况下 - 这个问题没有完整的解决方案(没有第三方应用程序)。

您可以通过以下链接比较 API JS 和 VBA:

Workbooks JS API

Workbooks VBA API

所以我想要做的 - 是在 JavaScript 中编写实际的 VBA 宏并将这个 VBA 宏插入到工作表中以使宏可执行。

如何通过工作表上的某些值更改使宏可执行？

我发现，如果您拨打 select使用 Worksheet_SelectionChange 直接应用于单元格和捕获选择更改的方法在 VBA 中 - 它完美地工作。

不幸的是，直接设置单元格值不会触发 VBA Worksheet_change
我为什么要使用 JS API

目前我已经有一个用于类似任务的 VBA 项目，但是随着项目的发展和发展 - 这里有一些功能，这里有一些功能，我看到插件 - 是解决关键问题的最佳解决方案 - 它更易于维护，管理，开发，推送更新，安装，它看起来更好 - 因为插件只是一个简单的网站

2019/09/20 更新 - 可能的解决方法

首先，非常感谢@DecimalTurn，他使这个变通方法成为可能。见 his original answer below

我稍微修改了它并添加了额外的 JS 脚本和 VBA 脚本来完成这个解决方案。所以:

在您执行从 JS 到 ThisWorkbook 的任何操作之前，需要插入以下 VBA 宏。模块:

1.1. VBA 宏将处理我们将传输的所有 VBA 代码

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    On Error GoTo endline
    Const SheetName As String = "_WorksheetSheetWorker"

    CheckIfVBAAccessIsOn

    If InStr(1, Sh.name, SheetName, vbBinaryCompare) >= 0 Then
        If Sh.Range("$A$1") <> vbNullString Then

            Const ModuleName As String = "m_TempMacroJS"

            Dim ws As Worksheet
            Set ws = ThisWorkbook.Sheets(SheetName)
            'We will take MacroName from sheet which we added from JS
            Dim MacroName As String
            MacroName = ws.Range("A2").Value2

            Dim rng As Range
            Set rng = ws.Range("A1")
            Dim pathToMacroBas As String

            'Export the content of the cell to a .bas file
            pathToMacroBas = ThisWorkbook.path & "\" & ModuleName & ".bas"
            Open pathToMacroBas For Output As #1
            Print #1, "Attribute VB_Name = """ & ModuleName & """ " & vbNewLine & ws.Range("A1").Value2
            Close #1

            'Declare VBProject Object
            Dim vbaProject As VBProject
            Set vbaProject = ThisWorkbook.VBProject

            'Delete pre-existing module with the same name
            On Error Resume Next
            ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents(ModuleName)
                On Error GoTo 0

                'Load the code as a new Module
                vbaProject.VBComponents.Import ThisWorkbook.path & "\" & ModuleName & ".bas"
                Dim vbaModule As VBIDE.VBComponent
                Set vbaModule = vbaProject.VBComponents(ModuleName)

                'Run the code and transfer working sheet to macro
                'You can use this worksheet to transfer values to macro as JSON
                Application.Run ModuleName & "." & MacroName, ws

                'Cleanup
                ThisWorkbook.VBProject.VBComponents.Remove vbaModule 
                'Optional
                Kill pathToMacroBas
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
            End If
        End If
        Exit Sub
      endline:
      End Sub

1.2 以编程方式启用的 VBA 宏 Trust access to the VBA project object model .请注意:您仍然需要启用 Microsoft Visual Basic for Applications Extensibility 5.3
我在链接 here 上找到了解决方案并稍作修改 - 宏创建 VBScript 并启用 Trust access to the VBA project object model直接在注册。我还无法处理的问题是延迟。保存和关闭现有工作簿需要延迟时间。

Sub CheckIfVBAAccessIsOn()

    '[HKEY_LOCAL_MACHINE/Software/Microsoft/Office/10.0/Excel/Security]
    '"AccessVBOM"=dword:00000001

    Dim strRegPath As String
    strRegPath = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & "\Excel\Security\AccessVBOM"

    If TestIfKeyExists(strRegPath) = False Then
      MsgBox "A change has been introduced into your registry configuration. All changes will be saved. Please reopen book."
      WriteVBS
      ThisWorkbook.Save
      Application.Quit
    End If

  End Sub

  Function TestIfKeyExists(ByVal path As String)
    Dim WshShell As Object
    Set WshShell = CreateObject("WScript.Shell")
    On Error Resume Next
    Dim RegValue As Boolean
    RegValue = WshShell.RegRead(path)
    If RegValue = True Then
      TestIfKeyExists = True
    Else
      TestIfKeyExists = False
    End If
    On Error GoTo 0
  End Function

  Sub WriteVBS()
    Dim objFile         As Object
    Dim objFSO          As Object
    Dim codePath        As String
    codePath = Me.path & "\reg_setting.vbs"

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(codePath, 2, True)

    objFile.WriteLine (" On Error Resume Next")
    objFile.WriteLine ("")
    objFile.WriteLine ("Dim WshShell")
    objFile.WriteLine ("Set WshShell = CreateObject(""WScript.Shell"")")
    objFile.WriteLine ("")
    objFile.WriteLine ("MsgBox ""Please wait until Excel will closes! Click OK to complete the setup process.""")
    objFile.WriteLine ("")
    objFile.WriteLine ("Dim strRegPath")
    objFile.WriteLine ("Dim Application_Version")
    objFile.WriteLine ("Application_Version = """ & Application.Version & """")
    objFile.WriteLine ("strRegPath = ""HKEY_CURRENT_USER\Software\Microsoft\Office\"" & Application_Version & ""\Excel\Security\AccessVBOM""")
    objFile.WriteLine ("WScript.echo strRegPath")
    objFile.WriteLine ("WshShell.RegWrite strRegPath, 1, ""REG_DWORD""")
    objFile.WriteLine ("")
    objFile.WriteLine ("If Err.Code <> o Then")
    objFile.WriteLine ("   MsgBox ""Error"" & Chr(13) & Chr(10) & Err.Source & Chr(13) & Chr(10) & Err.Message")
    objFile.WriteLine ("End If")
    objFile.WriteLine ("")
    objFile.WriteLine ("WScript.Quit")

    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing

    'run the VBscript code
    ' > The macro will fail to execute the VB script if you use a
    '   [codepath] which contains blanks!
    '
    ' > To fix this issue, we add a pair of double quotes (" ") around
    '   [codepath];
    Shell "cscript " & Chr(34) & codePath & Chr(34), vbNormalFocus

  End Sub

我根据@DecimalTurn 建议编写的第二部分在 JS 中创建工作表，然后从 VBA 中捕获此事件并将整个代码包装在一个 JS 实例中:

    const VBAWorker = function(){
      /* This is a name of tempurary sheet to execute macro */
      this._executedMacroName = "JSSubRunner"
      /* This is the name of sheet worker*/
      this._WorksheetSheetWorkerName = "_WorksheetSheetWorker"
      /* These options can be applied to already existed sheet*/
      this._worksheetExistenceDecisionOptions = {
        replaceSheet : "replaceSheet",
        findNewAvailableName : "findNewAvailableName"
      }
    }


    /**
     * Function to run macro using sheet worker
     * @param {String} VBAMacro is a code which will be executed
     * @param {String} transferredValues (optional) are a values which we need 
     * to place into executable macro
     * @param {String} worksheetDesicion (optional) is a desicion which we will if the worker worksheet exists
     * default = "replaceSheet", possible = "findNewAvailableName"
     */
    VBAWorker.prototype.run= async function(VBAMacro, transferredValues = "", worksheetDesicion = "replaceSheet"){
      const defaultWorksheetName = this._WorksheetSheetWorkerName
      let worksheetName = defaultWorksheetName
      const preparedVBAMacro = this._changeMacroName(VBAMacro) 
      await Excel.run(async (context) => {
        /* First we need to check out existence of sheet worker*/
        let sheets = context.workbook.worksheets;
        sheets.load("items/name");

        await context.sync()
        /**
         *  In this case we will deside what to do 
         *  if we will find sheet with the same name
         * */ 
        const isSheetExists = this._checkWorksheetExistence(sheets)
        const decisionOptions = this._worksheetExistenceDecisionOptions
        if (isSheetExists){
          switch (worksheetDesicion){
            case decisionOptions.replaceSheet:
              let sheetToReplace = sheets.getItem(worksheetName)
              sheetToReplace.delete()
              await context.sync()
            break;
            case decisionOptions.findNewAvailableName:
              worksheetName = this._changeNameOfWorkerWorksheet(sheets) 
            break;
          }
        } else {
          /* we will keep worksheetName as default */
        }

        let sheet = sheets.add(worksheetName);
        let macroExeCell = sheet.getCell(0,0)
        let macroNameCell = sheet.getCell(1,0)
        let macroValuesCell = sheet.getCell(0,1)
        macroExeCell.values = preparedVBAMacro
        macroNameCell.values = this._executedMacroName
        let preparedValues = []
        const limit = 32700 
        const lengthOfString = transferredValues.length
        // console.log(transferredValues.length)
        // console.log(transferredValues.length / limit)
          if (lengthOfString > limit) {
            try {
              let done = false

              /* during cell lenght limit we will slice string to many*/
              let lastStep = false
              let current = limit
              let oldcurrent = 0

              do {
                let end = current
                let start = oldcurrent
                /* Check that the next simbol not equals to "=" */
                if(transferredValues.slice(end, end + 1) == "="){
                  current += 1
                  end = current
                }

                if (lengthOfString < start ){
                  start = lengthOfString
                }  
                if (lengthOfString < end){
                  end = lengthOfString
                  lastStep = true
                }

                preparedValues.push(transferredValues.slice(start, end))

                if (lastStep){
                  done = true
                } else {
                  oldcurrent = current
                  current += limit
                }
              } while (done == false)
              /* Write values to sheet*/
              await preparedValues.forEach(async (el, i)=>{
                macroValuesCell = sheet.getCell(0 + i,1)
                macroValuesCell.values = [[el]]
              })
            } catch (error) {
              console.log(error)
            }
          } else {
            /* If string.length is less then limit we just put it directly to one cell*/
            macroValuesCell.values = [[transferredValues]]
          }
        return await context.sync();
      });
    }

    /**
     * Function to search available name of sheet and return it
     * @param {Array} sheets - worksheet items with 
     * returns suggestedName (string)
     */
    VBAWorker.prototype._changeNameOfWorkerWorksheet = function(sheets){
      try {
        let suggestCounter = 0
        let suggestedName; 
        let suggestedNameIsFree = false;
        let worksheetName = this._WorksheetSheetWorkerName
        do {
          suggestedName = worksheetName + suggestCounter 
          suggestCounter = suggestCounter +1
          suggestedNameIsFree = !this._checkWorksheetExistence(sheets)
        } while (suggestedNameIsFree = false);
        return suggestedName

      } catch (error) {
        console.log(error)
      }
    }

    /**
     * Function to check worksheet name existence
     * @param {Array} sheets - worksheet items with names
     * returns true or false
     */
    VBAWorker.prototype._checkWorksheetExistence = function(sheets){
      let isSheetExists = false
      sheets.items.forEach(el=>{
        if(el.name == this._WorksheetSheetWorkerName){
          isSheetExists = true
          return;
        }
      }) 
      return isSheetExists
    }

    /**
     * Function to change name of running macro
     * @param {String} VBAMacro is a string that contains executed macro
     * The name of running sub will be changed to "_JSSubRunner"
     */
    VBAWorker.prototype._changeMacroName =function(VBAMacro){
      const regex = /(Sub\s+)(.*)([(])/i
      const renamedVBAMacro = VBAMacro.replace(regex, `Sub ${this._executedMacroName} (`)
      return renamedVBAMacro
    }

    export default VBAWorker

如何使用？

您可以将它用作调用 VBAWorker 的简单实例:

  const VBAWorkerInst = new VBAWorker()
  await VBAWorkerInst.run(
    "your VBA code goes here",
    "your values in string (JSON for example) goes here",
    "optional option:) - a name of decision what we need to do, if sheet already existed"
  )

您的宏可以有任何名称，因为这个 VBAWorker 会处理它并更改该名称以统一它。

请注意:因为 Excel 是异步的，所以我们需要等到所有 promise 都得到解决!所以上面的代码必须包裹在异步函数中，否则你可以捕获promise回调。

我还没有经过测试，但我认为运行多个宏是可能的，我们可以使用与值相同的策略来编写更有用的代码。

所以这就是全部:) 真的希望随着时间的推移会有更简单的解决方案..

Answer 1

嗨，此功能目前不存在 Office.js API。我会在 Office 加载项用户语音网站上发布需求:https://officespdev.uservoice.com .谢谢你。