Office Scripts: The future is here

Microsoft has been working hard to make Office available on every platform. One of the biggest changes has been the ability to run Office the a browser or Office online. However, one drawback has been that automation in Office online has been limited to Web Add-ins which are aimed solidly at professional developers. However, Visual Basic for Applications (VBA) macros are only supported in Windows and Mac clients. They are not supported on the web platform, the iOS or Android platforms. And, well, they still are not.  wlEmoticon-disappointedsmile.png  However, something new has arrived which will allow you to create “macros” anywhere, on any platform (eventually).

Microsoft recently announced Office Scripts. It is an end-user approachable, web and collaborative supported scripting language. It is still in preview and only supported in Excel online, for now.

First, to access this you must have your administrator enable it in the Office 365 Portal:

turn_on_scripts

For citizen programmers this line from the above page says it all:

Scripts allow you to record and replay your Excel actions on different workbooks and worksheets. If you find yourself doing the same things over and over again, an Office Script can help you by reducing your whole workflow to a single button press.

So, I enabled it in my test tenant and created the quintessential “Hello Wold” Action Recording. When your administrator enables this on your tenant, you will get this new Automation tab:

AutomationTab

From there, I clicked on Record Actions, typed “Hello World!” in cell A1, and then stopped the macro. It then asked me to name it and give a description (optional), and then Save. From there it opened the Code Editor and here is what I see:

async function main(context: Excel.RequestContext) {
// Set range A1 on selectedSheet
let workbook = context.workbook;
let worksheets = workbook.worksheets;
let selectedSheet = worksheets.getActiveWorksheet();
selectedSheet.getRange("A1").values = [
["Hello World"]
];
}

view raw
HelloWorld.js
hosted with ❤ by GitHub

Once you open a new workbook, you can then click on the Code Editor button and you will see all your recorded scripts:

recordings

The future is here. Check it out now. You can learn more about Office Scripts from here: https://docs.microsoft.com/en-us/office/dev/scripts/tutorials/excel-tutorial.

 

EXCEL: Auto-Run Macros Disabled without Warning

This entry applies if your security settings in Excel are set to “Disable all macros except digitally signed macros” like this:

disabledmacros

In this scenario, when you open a workbook that is digitally signed, you will get a yellow bar across the top of the screen, that says: “SECURITY WARNING Macros have been disabled”, like this:

macrowarning

But if the workbook is NOT signed and you attempt to open the workbook with macros, you will not see anything, no warning at all. The workbook will appear to open, but all macros will be disabled. If you try to run a macro, you will get the following warning:

macrowarning2

However, the insidious thing is your AutoRun macros will also not execute and you will get no warning. If your code performs some type of data refresh of the spreadsheet, you might not realize this did not happen and what you will be left with is stale data. For many financial institutions this can be a scary prospect.

To workaround this problem, I have created an XLAM that can warn your users that they opened an untrusted workbook. You can access the source and the add-in both on GitHub: VBAProjectNotSignedWarning-Addin

What this add-in does is look for XLSM file to be opened, it then checks to see if the VBAProjectSigned property is false. If the workbook code is not signed, macros are assumed to be in the file, and the user will get the following warning:

certwarningaddin

The SOURCE is provided in the depot because for it to run properly in your environment, you might want to open the XLSM sign the VBA Project with your corporate code certificate, secure the project with a password, and then Save A Copy as an XLAM (Excel Add-in). Once created, you can then place the add-in in the XLSTART folder in Program Files for each user in your organization. Then when they try to open an unsigned macro workbook, they will get a handy warning message.

The code for this is fairly simple. I will not go into each module, just the main function that performs the check of each Workbook that is opened via the Application.Workbook_Open event:

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
If Right(UCase(Wb.Name), 4) = "XLSM" Then
If Wb.VBASigned = False Then
MsgBox "The workbook '" & Wb.Name & "' is not signed. " & _
"If there are auto-run macros in the workbook, " & _
"they will not be run.", vbExclamation, "Signed Workbook Check"
End If
End If
End Sub

view raw
WorkbookOpen.vb
hosted with ❤ by GitHub