我搜索了Office Excel API的文档，但没有发现任何关于如何实现它的提及。
问题：
VBA宏可以轻松解决API功能不足或某种错误的问题。
但是，实现这一点只有两个可能性：
手动将宏插入表格中，该宏将在表格上捕获某些事件，并且来自 addin 的 JS 将触发该事件（此解决方案来自非常古老的论坛（如果我找到链接，我会在此处插入它））。
在 addin 运行时通过 JS 代码将宏插入工作表中（context.workbook.worksheet？），即使它不能由 addin 执行，但在这种情况下，最终用户根本不需要管理宏 - 我们可以使用相同的逻辑使宏运行过程更加平滑（JS 将宏插入表格中，更改表格中的某些值，然后触发某些宏，然后（例如在 JS 中的某个超时之后或仅由 JS 中的另一个事件）我们可以轻松地删除具有此宏的整个工作表）。
因此，问题是，是否可以通过 Excel API 实现与解决方案2类似的东西？ （某种插入/删除来自 addin 的 VBA 代码的函数）
我将感激任何帮助！
示例以澄清问题
我正在使用 JavaScript API，但不幸的是，API没有涵盖已存在于 VBA 中的全部功能性（我希望尚未），让我们通过一个简单的例子来解释一下：
假设有一个任务：
我们需要从工作簿1的表格1中复制一些信息
然后，我们需要创建一个工作簿，并仅将值放入新的工作簿2中
然后，我们需要向用户建议它（新工作簿2）需要保存在哪里。
然后，我们需要保存并关闭工作簿2。
VBA轻松解决了这个问题，但在 JS API 的情况下 - 没有这个问题的完整解决方案（没有第三方应用程序）。
您可以通过下面的链接比较 API JS 和 VBA：
因此，我想要做的是用 JavaScript 编写实际的 VBA 宏，并将此 VBA 宏插入表格中，使该宏可执行。
如何从表格中的某个值更改使宏可执行？
我发现，如果直接对单元格调用 select 方法，并使用 VBA 中的 Worksheet_SelectionChange 捕获选择更改 - 它可以完美地工作。
不幸的是，直接将值设置到单元格中不会触发 VBA 的 Worksheet_change。
为什么要使用 JS API
目前我已经有一个类似任务的 VBA 项目，但随着项目的增长和演变 - 一些功能在这里，一些功能在那里，我发现插件 - 是解决关键问题的最佳方案 - 它更容易维护、管理、开发、推送更新、安装，而且它看起来更好 - 因为插件只是一个简单的网站。
更新 2019/09/20 - 可能的解决方法
首先，非常感谢 @DecimalTurn，他使这个解决方法成为可能。请参见他下面的原始答案
我稍微修改了它，并添加了额外的 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宏可编程方式启用信任访问VBA项目对象模型。 请注意：您还需要启用Microsoft Visual Basic for Applications Extensibility 5.3
我在链接此处找到了解决方案，并稍作修改-该宏创建VBScript并直接在注册表中启用信任访问VBA项目对象模型。 我尚未解决的问题是延迟时间。需要延迟保存和关闭现有工作簿。
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
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是异步的，我们需要等待所有承诺都解决！因此，上面的代码必须包装在异步函数中，或者您可以捕获承诺回调。
我还没有经过测试，但我认为可以运行多个宏，我们可以使用与值相同的策略编写更有用的代码。
所以这就是目前的全部内容:) 真的希望随着时间的推移会有更简单的解决方案..