Microsoft releases Script Lab – a tool for creating JS add-ins for Office

Posting an exciting announcement from Microsoft:

 

This morning we announced Script Lab, a crazy Microsoft Garage project for anyone who wants to learn about building add-ins!


Script Lab (formerly known as “Add-in Playground” and “Project Bornholm”) has three main features:

  • Code in a pane inside your Office file. IntelliSense shows suggestions while you type so you can easily discover and use the Office JavaScript objects and methods. And if you don’t want to start from scratch there are plenty of samples preinstalled with Script Lab. Your snippets can use any TypeScript features like the magical async/await and conveniences like arrow functions and template strings. But it’s not only script: your snippets can also use HTML, CSS, and references to external libraries and data on the web. Script Lab uses the Monaco editor, the same tech that powers VS Code, so it’s beautiful and lightweight.
  • Run the code in another pane beside the editor. Execution can include logic, API calls to Office, UI in the pane, and even output to a console. Every time you make a code change you can refresh the editor and run the new version in seconds.
  • Share your snippets through GitHub. If you create a snippet you’d like to share, you can use Script Lab to save it. Then send the link to someone else to try it and tweak it on their computer. The Import feature lets you load other people’s snippets. We think sharing will be especially useful for asking and answering questions about add-ins on Stack Overflow (http://stackoverflow.com/questions/tagged/office-js).

-The Script Lab team: Michael Z, Bhargav, Jakob, Daniel, and Michael S

When is ByRef not ByRef?

Somewhat related to my last post here, I neglected to mention that ByRef is the default setting in VBA – so if you don’t specify ByVal, you get ByRef.

However, a recent thread at MrExcel reminded me that this is not actually always the case.

A Property Let or Property Set procedure can be written with ByRef parameters but when the code actually runs, the variables will be passed ByVal regardless of what you specified. Equally interestingly, this applies to arrays too, even though you can’t pass an array ByVal according to all the documentation. I assume that a copy of the array is made under the covers and that is passed ByRef. I should also note (with thanks to Pedro at MrExcel) that you can only pass an array variable to a Property Let procedure if it was declared as a dynamic array.

Property Get procedures on the other hand obey the usual rules – ByRef is used unless otherwise specified.

ByVal or ByRef – what’s the difference?

This is a question I see increasingly frequently in forums for some reason, so I thought I’d cobble together a brief synopsis.

ByRef passes a pointer to the variable, so any changes are reflected everywhere that variable is used.

ByVal passes a copy of a variable to a routine, so any changes to that variable will not be reflected in the original routine. In the case of objects, they are always passed as references (pointers) but if you pass an object ByVal you pass a copy of the pointer to the object rather than the original pointer (note: not a copy of the object)

Note: Array variables are always passed ByRef.

(This does not apply to Variants that happen to contain arrays, only to variables that are actually declared as an array.)

 

So here are some examples that will hopefully serve to illustrate the above:

  1. Using a simple data type:
Sub foo()

Dim i As Long
i = 1
Call Change_ByRef(i)
' i is changed by the Change_ByRef procedure and that change IS reflected here too because it was passed by reference
MsgBox "i is now: " & i
Call Change_ByVal(i)
' i is changed only within the Change_ByVal procedure because a COPY of it was passed
MsgBox "i is still: " & i

End Sub

Sub Change_ByRef(ByRef lInput As Long)
lInput = 14
End Sub

Sub Change_ByVal(ByVal lInput As Long)
lInput = 21
End Sub

2. Using an object variable, showing that when passed ByVal, you cannot change the orginal variable to make it point to a different object:

Sub Object_foo()

Dim r As Range
Set r = Range("A1")
Call Change_object_ByRef(r)
' r is changed to a different cell by the Change_ByRef procedure and
' that change IS reflected here too because it was passed by reference
MsgBox "r is now: " & r.Address
Call Change_object_ByVal(r)
' r is changed to a different cell by the Change_ByVal procedure but
' that change IS NOT reflected here because it was passed by value.
MsgBox "r is still: " & r.Address

End Sub

Sub Change_object_ByRef(ByRef rInput As Range)
' change the range variable to one row down
Set rInput = rInput.Offset(1)
End Sub

Sub Change_object_ByVal(ByVal rInput As Range)
' change the range variable to one row down
Set rInput = rInput.Offset(1)
End Sub

3. Again using an object variable, but this time showing that, even when passing ByVal, because you are still passing a pointer to the original object, you can change the properties of the object (you just can’t change which object the variable refers to):

Sub Object_foo2()

Dim r As Range
Set r = Range("A1")
Call Change_object_property_ByRef(r)
' r is given a different value by the Change_ByRef procedure and
' that change IS reflected here because an object reference is passed
MsgBox "r is now: " & r.Value
Call Change_object_property_ByVal(r)
' r is given a different value by the Change_ByVal procedure and
' that change IS reflected here because an object reference is still passed
MsgBox "r is now: " & r.Value

End Sub

Sub Change_object_property_ByRef(ByRef rInput As Range)
' change its value
rInput.Value = "changed byref"
End Sub

Sub Change_object_property_ByVal(ByVal rInput As Range)
' change its value
rInput.Value = "changed byval"
End Sub

For details of an exception to this rule, see the next post.

Power Query book

You might think this is a shameless plug for some fellow MVPs (and it is to some extent) but if you have begun to use Power Query (or ‘Get and Transform’ as some bright spark decided to call it in 2016) or are thinking about it, or if you spend a lot of time cleaning up and manipulating data in Excel, then you owe it to yourself to get this book:

 

M is for Data Monkey by Ken Puls and Miguel Escobar.

 

I have only just begun to discover how fantastic the Product Formerly Known as Power Query is and, for an Excel user rather than BI specialist, this book is absolutely invaluable since it approaches tasks that will be familiar to most of us in a way that makes sense. If you don’t already have it, it really needs to be on your shopping / Christmas list. Trust me, you won’t regret it!

When is a FormatCondition not a FormatCondition?

I was recently reminded of this quirk of the Excel object model (you guessed it – by a forum post). Suppose you have this code:

Dim fc As FormatCondition
For Each fc In ActiveSheet.UsedRange.FormatConditions
   ' do something to fc here
Next fc

you’d probably be slightly surprised if you received a Type Mismatch error on the second line. After all, you’re using a FormatCondition control variable to loop through the FormatConditions collection – what could be simpler?

Well, as it happens, the FormatConditions collection isn’t restricted to only FormatCondition objects. Yes, I know. It’s not my fault, so don’t shout at me.

It can actually contain all the following object types, which relate to the various fancy conditional formatting options available since Excel 2007 (shudder):

FormatCondition
DataBar
AboveAverage
ColorScale
UniqueValues
Top10
IconSetCondition

so in the original code, you actually need:
Dim fc as Object
and you can then test the object types using TypeName or TypeOf according to your needs/preference.

Default picture size with Shapes.AddPicture

I have often resisted using the Shapes.AddPicture method, sticking with the deprecated Pictures.Insert method, because with the former you have to specify the dimensions of the picture (i.e. both Height and Width) as well as the location and usually I don’t know them (and can’t be bothered with the code to get them). Unfortunately the Pictures.Insert method gives you no control over whether the image is linked or embedded.

 

However, this morning I realised that you can avoid this problem by simply passing -1 as the value for both Height and Width! (a real “why the hell didn’t I think of that before?” moment)

 

So the code is simply:

ActiveSheet.Shapes.AddPicture Filename:="path to pic", linktofile:=msoFalse, _

savewithdocument:=msoCTrue, Left:=0, Top:=0, Width:=-1, Height:=-1

Late bound MSForms.DataObject

Expanding on a previous theme (Transposing arrays using in-memory controls. You remember that, right?), I frequently see posts relating to getting text from the clipboard in VBA – which unfortunately does not have the VB6 Clipboard object to play with. The standard method, rather than messing about with the Windows API if you don’t have to (sometimes there’s no avoiding it), is to use the MSForms.DataObject.

 

For example, to copy the current cell’s contents to the clipboard, you might use this:

Public Sub CopyCellContents()
Dim objData As MSForms.DataObject
On Error Resume Next
Set objData = New MSForms.DataObject

' copy current cell formula to clipboard
With objData
.SetText ActiveCell.Formula
.PutInClipboard
End With
End Sub

Of course, this requires setting a reference to the Microsoft Forms 2.0 Object Library so then you have to explain how to set a reference (or tell the poster to insert a userform and then remove it). Being lazy, I spent a lot of time figuring out how to late bind the code so that I didn’t have to keep explaining the references, so here’s the late bound version:

Public Sub CopyCellContents()
Dim objData As Object
On Error Resume Next
' this is a late bound MSForms.DataObject
Set objData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

' copy current cell formula to clipboard
With objData
.SetText ActiveCell.Formula
.PutInClipboard
End With
End Sub

Enjoy! 🙂

What’s in a colon?

A few times this week I’ve seen instances of people tearing their hair out over code not doing what was expected, due to one missing piece of punctuation: the humble colon.

In each case, the issue was that a named argument was being passed to a method – or at least, that was the intention, but the colon was missing. Consider this example:
 
Activesheet.Protect password = "foobar"
 
At first glance, if you’re in a rush or you wrote the code and therefore see what you think is there, this looks fine and you’d expect your sheet to be protected (which it is) with a password that’s “foobar” (which it is not).

The problem, in case you haven’t spotted it, is that the code should read:
 
Activesheet.Protect password:="foobar"
 
Note the colon before the = sign.
 
In the original version, what happens is that the variable ‘password’ is tested for equality with the value “foobar”, which will return either True or False. This returned value is then used as the value of the first parameter to the Protect method (which does happen to be the Password parameter) and the sheet is protected accordingly with either “True” or “False” as the password!

Of course, if you are a wise programmer and have Option Explicit set, this will probably never happen to you because you will get a run-time error telling you that the variable ‘password’ has not been declared (unless of course you happen to have already used that as a variable name…)

For the record, the other instance of this problem I saw was:
 
Activeworkbook.Close savechanges = False
 
 
I leave you to determine what actually happened. 😉

 

VBA references and early binding vs late binding

At some point, many VBA programmers will find themselves dealing with a references problem. This usually manifests itself as an error message along the lines of “Can’t find project or library” with the debugger highlighting what appears to be a fairly innocuous line of code – often involving a native VBA function like Date, Left or Format.

The cause of the problem is usually a reference to a non-default object library – for example, if you are working in Excel but need to automate Word or Outlook, you will typically set a reference to the required library and then happily program against it. The issue arises when you come to distribute the project and it is run by someone who has a different version (usually an earlier one) of the programs in question. So for example, you may have developed using Office 2010 and then distributed the project to someone using 2007. When they try to run it, the phone calls begin…

Unfortunately, although the non-default project references will usually update themselves to match a later version of software, that does not often happen with earlier versions, and that is what causes code errors. Using the example of the Date function, unless you have explicitly written VBA.Date in the code, the compiler doesn’t know where the Date function is located and has to look through the referenced libraries to try and find it – as soon as it comes across a broken reference, the error is raised.

 

So – what to do? The most obvious options are:

  • Write code to update the references on the target machine. This is difficult to implement well and, even if you can, it will fail if the security restrictions on the target machine are not set as you need them.
  • Develop in the lowest version of the software that you need to support. This is a simple ‘fix’ and will work in many cases but by no means all. For instance, if someone saves your project in a later version of the software that will update the references and if it is then distributed to someone with an earlier version, you are back to square one.
  • Late bind your code. This often the most effective solution and what I will cover now.

 

In code terms, the difference between early and late binding is simple:

If you declare a variable as Object, you are late binding it.

There is no more to it than that in the strict definition of the term.

 

However, the real benefit of late binding – i.e. fixing the references issues – is that it allows you to not set a reference to the other library at all. If there’s no reference, there’s nothing to break – problem solved.

In order to do this in practice, we require three basic steps, rather than the one simple one mentioned above:

  1. Declare your object variables as Object, rather than as, say, Word.Document or Outlook.Application
  2. Instead of using the New operator to instantiate the objects, you must use CreateObject with the relevant class name.
  3. Either declare any constants you use from the target library, or use their literal values instead (the former I believe is better practice).

 

Here is an early bound and a late bound example of some simple code to manipulate Outlook from Excel – I have highlighted the differences with comments in the code:

Early bound version

Sub SendOLMail_EarlyBound()
    ' declare variables for the Application and Mailitem objects
   Dim oAPP                       As Outlook.Application
    Dim oItem                      As Outlook.MailItem

    ' instantiate the Application
   Set oAPP = New Outlook.Application

    ' create a new email
   Set oItem = oAPP.CreateItem(olMailItem)

    ' set basic properties and display the email
   With oItem
        .To = "foo@bar.com"
        .Subject = "this is a test"
        .Body = "nothing to see here"
        .Display
    End With

End Sub

Late bound example

Sub SendOLMail_LateBound()
    Dim oAPP                       As Object
    Dim oItem                      As Object
    ' need to declare this constant as it has no meaning without
   ' the reference set to the Outlook library
   Const olMailItem               As Long = 0

    ' instantiate the Application - cannot use New without a reference
   ' so we must use CreateObject
   Set oAPP = CreateObject("Outlook.Application")

    ' #######################################
   ' NOTE: THE REST OF THE CODE IS IDENTICAL
   ' #######################################

    ' create a new email
   Set oItem = oAPP.CreateItem(olMailItem)

    ' set basic properties and display the email
   With oItem
        .To = "foo@bar.com"
        .Subject = "this is a test"
        .Body = "nothing to see here"
        .Display
    End With

End Sub