有没有一种方法可以将宏从Excel插件插入到工作表中? [英] Is there is a way to insert macro to worksheet from Excel Addin?
问题描述
我搜索了文档 Office Excel API ，但根本找不到任何提及，以及如何实现.
I searched documentation Office Excel API, but cannot find any mentions at all, how it can be implemented.
问题:
使用VBA宏可以轻松解决API功能或某些错误.
Luck of API functionality or some sort of bugs can be solved easily by VBA macros.
但是，为此，我们只有两种可能性:
But, for doing that, we have only two possibilities:
-
将宏手动插入到工作表中，这将捕获工作表上的某些事件，而addin中的JS会触发该事件(此解决方案来自非常老的论坛(如果我能找到链接，则将其插入此处) ).
Insert macro manually into sheet, which will catch some event on sheet, and JS from addin will trigger that event (this solution came from very old forum (if I will find the link, I will insert it here)).
在外接程序运行时通过JS代码将宏插入工作表(context.workbook.worksheet?)，甚至无法由外接程序执行，但是在这种情况下，最终用户根本不需要管理宏-我们可以使用相同的逻辑使宏运行的过程更加平滑(JS将宏插入工作表中，在工作表中更改一些值，然后触发某个宏，然后再执行此操作(例如，在JS中超时或仅在JS中发生另一个事件之后)我们可以使用此宏轻松删除整个工作表)).
Insert macro by JS code to sheet (context.workbook.worksheet?) during addin runtime, and even it cannot be executed by addin, but in this case the end user do not need to manage macro at all - we can make process of macro running more smooth with the same logic (JS inserts macro into sheet, change some value in sheet and then it triggers some macro, and after that (for example after some timeout in JS or just by another event in JS we can easily delete the whole sheet with this macro)).
问题是，是否可以使用Excel API以某种方式做出类似于解决方案2的内容? (某种用于从插件中插入/删除VBA代码的功能)
So the question is, is it possible somehow make something similar to solution 2 using the Excel API? (some sort of function to insert/delete VBA code from addin)
我将不胜感激！
用于阐明问题的示例
我正在使用JavaScript API，但不幸的是，该API并没有涵盖VBA中已经存在的全部功能(我希望到目前为止).让我在一个简单的示例上对其进行解释:
I'm using the JavaScript API, but unfortunately the API doesn't cover full functionally (I hope yet) which is already existed in VBA. Let me explain it on a simple example:
想象一个任务:
-
我们需要从工作簿1中的工作表1中复制一些信息
We need to copy some information from sheet 1 in workbook 1
然后我们需要创建一本书并将其值放入新工作簿2中
then we need to create a book and just put the values in new workbook 2
然后，我们需要建议用户将其保存在何处(新工作簿2).
then we need to suggest to user where it(new workbook 2) needs to be saved.
然后我们需要保存并关闭工作簿2.
then we need save and close workbook 2.
VBA可以轻松解决该问题，但是如果使用JS API，则无法完全解决此问题(没有第三方应用程序).
VBA resolves the problem easily, but in case JS API - there is no full solution of this problem (without third-party applications).
您可以通过下面的链接来比较API JS和VBA:
You can compare API JS and VBA by follwoing links below:
所以我想做的-用JavaScript编写实际的VBA宏，然后将此VBA宏插入工作表以使该宏可执行.
So what I want to do - is to write actual VBA macro in JavaScript and insert this VBA macro into sheet to make the macro executable.
如何使工作表中的某些值更改使宏可执行?
我发现，如果您调用select方法直接应用到单元格并使用VBA中的Worksheet_SelectionChange捕获选择更改-则效果很好.
I found out that, if you call select method directly applying to cell and catch selection change using Worksheet_SelectionChange in VBA - it perfectly worked.
不幸的是，将设置值直接定向到单元格不会触发VBA Worksheet_change
Unfortunately direct set values to cell doesn't trigger VBA Worksheet_change
我为什么要使用JS API
目前我已经有一个VBA项目可以完成类似的任务，但是随着项目的发展和发展-这里的一些功能，这里的一些功能，我看到插件-是解决关键问题的最佳解决方案-易于维护，管理，开发，推送更新，安装，而且看起来更好-因为插件仅是一个简单的网站
Currently I already have a VBA project for similar tasks, but as the project grow and evolve - some features here, some features here, I saw that addins - is the best solution to resolve key problems - its easier to maintain, manage, develop, push updates, install, and it just look better - because an addin is just one simple website
更新2019/09/20-可能的解决方法
首先，非常感谢@DecimalTurn，他使这种解决方法成为可能.请参见下面的他的原始答案
First of all, great thanks to @DecimalTurn, who made this workaround possible. See his original answer below
我做了一些修改，并添加了其他JS脚本和VBA脚本以使该解决方案完整.所以:
I a little bit modify it and added additional JS script and VBA script to make this solution complete. So:
- 在从JS到
ThisWorkbook模块执行任何操作之前，需要插入以下VBA宏:
- The following VBA macros needs to be inserted BEFORE you will make any actions from JS to
ThisWorkbookmodule:
1.1. VBA宏将处理我们将传输的所有VBA代码
1.1. VBA macro which will handle all VBA code which we will transfer
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
1.2 VBA macro which will programmatic way enable Trust access to the VBA project object model. Please note: You will still need also enable Microsoft Visual Basic for Applications Extensibility 5.3
I found the solution on link here and a little modify it - the macro creates VBScript and enable Trust access to the VBA project object model directly in register. The problem which I cannot handle yet - is the delay. The time of delay is needed to save and close existed workbook.
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的简单实例:
You can used it as simple instance calling the VBAWorker that way:
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会处理它并更改该名称以统一它.
Your macro can have any name, because this VBAWorker will take care of it and will change that name to unify it.
请注意::由于Excel是异步的，我们需要等到所有的诺言都解决为止！因此，上面的代码必须包装在异步函数中，否则您可以捕获promise回调.
Please note: because Excel is asynchronous we need to wait until all promises will resolved! So the code above must be wrapped in asynchronous function or you can catch the promise callback.
我尚未经过测试，但是我认为可以运行多个宏，并且我们可以使用与值相同的策略来编写更有用的代码.
I'm not tested yet, but I think it's possible to run more than one macro, and we can use the same strategy as for values to write more usefull code.
这就是现在的全部:)真希望随着时间的流逝，会有更简单的解决方案..
So that's all in this moment:) Really hope that with time there will be simpler solution..
推荐答案
潜在的解决方法
作为免责声明，此方法可能会在您正在使用的Excel文件中引入一些漏洞，因此您必须对此谨慎使用，因为您必须为宏，模块和工作表名称使用唯一的名称，以确保不会运行任何加载项未经您同意的VBA代码.
Potential Workaround
As a disclaimer, this method could introduce some vulnerability in the Excel file you are using, so you have to be careful with this by using unique names for your macro, module and sheet name to make sure no add-in will run VBA code without your consent.
这个想法是创建一个新的工作表并将宏代码写入该工作表中的单元格中(假设单元格为A1).然后，ThisWorkbook模块中已经存在一个VBA事件过程，该过程将繁重地工作以使宏运行.
The idea would be to create a new worksheet and write the code of your macro into a cell inside that worksheet (Let's say cell A1). Then, there would be a VBA Event procedure already present in the ThisWorkbook module that will do the heavy lifting to make your macro run.
假设已启用Trust access to the VBA project object model，并且已将Microsoft Visual Basic for Applications Extensibility 5.3库添加到工作簿中，则可以在ThisWorkbook中使用以下VBA事件过程:
Assuming that the Trust access to the VBA project object model was enabled and that you've added the Microsoft Visual Basic for Applications Extensibility 5.3 Library to your workbook, you could have the following VBA event procedure inside ThisWorkbook:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
If Sh.Name = "NewSheet" Then
If Sh.Range("$A$1") <> vbNullString Then
Const ModuleName As String = "MacroJs"
Const MacroName As String = "YourMacroName"
Const SheetName As String = "NewSheet"
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(SheetName)
Dim rng As Range
Set rng = ws.Range("A1")
'Export the content of the cell to a .bas file
Open ThisWorkbook.Path & "\" & ModuleName & ".bas" 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
Application.Run ModuleName & "." & MacroName
'Cleanup
ThisWorkbook.VBProject.VBComponents.Remove vbaModule 'Optional
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
End If
End Sub
此过程将由您的Office-JS代码创建工作表触发.
This procedure would be triggered by the creation of a sheet by your Office-JS code.
请注意，我还建议添加一些错误处理，以确保在运行代码时出现运行时错误的情况下，将运行清除部分.
Note that I would also advise to add some error handling to make sure that the cleanup section will run in case there is a run-time error while running the code.
然后您的JavaScript代码将如下所示:
And then your JavaScript code would look like this:
var sheets = context.workbook.worksheets;
var sheet = sheets.add("NewSheet");
sheet.getRange("A1").values = 'sub YourMacroName() \n Msgbox "Test" \n End sub';
这篇关于有没有一种方法可以将宏从Excel插件插入到工作表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
