I searched documentation Office Excel API, but cannot find any mentions at all, how it can be implemented.
The Problem:
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:
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)).
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)).
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)
I will appreciate any help!
Example to clarify problem
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:
Imagine a task:
We need to copy some information from sheet 1 in workbook 1
then we need to create a book and just put the values in new workbook 2
then we need to suggest to user where it(new workbook 2) needs to be saved.
then we need save and close workbook 2.
VBA resolves the problem easily, but in case JS API - there is no full solution of this problem (without third-party applications).
You can compare API JS and VBA by follwoing links below:
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.
How to make macro executable from some value change on sheet?
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.
Unfortunately direct set values to cell doesn't trigger VBA Worksheet_change
Why am I using JS API
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
Update 2019/09/20 - Possible Workaround
First of all, great thanks to @DecimalTurn, who made this workaround possible. See his original answer below
I a little bit modify it and added additional JS script and VBA script to make this solution complete. So:
ThisWorkbook module: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 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
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
How it can be used?
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"
)
Your macro can have any name, because this VBAWorker will take care of it and will change that name to unify it.
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..
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.
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
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.
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']];