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

Bug with VBA form controls transparency and Z-Order

There has been a bug introduced with the September update for Office 365 and Office 2016 perpetual which prevents controls on userforms from showing transparency or altering their Z-order properly.

 

Latest update from Microsoft is here. A fix is in the works and will hopefully be provided soon but, if you can’t wait, rolling back to an earlier build seems to be the best solution currently!

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.

Transpose bug in 2013 and 2016

Many of us will be used to using Application.Transpose (or WorksheetFunction.Transpose) when manipulating ranges or arrays in VBA. And, up to and including Excel 2010, we are probably all aware that you will get a run-time error if you pass an array (not range) that is over 65536 ‘rows’.

 

The good news is that this error doesn’t occur in 2013 or 2016. The bad news is that the resulting array is not the size it should be and it gets truncated with no warning at all!

 

Essentially, what happens with an array that is over 65536 rows, the last whole multiple of 65536 rows are simply removed – i.e. you lose (n \ 65000) * 65000 rows.

So if your array is 65537 rows by 1 column, the resulting array will have 1 item. If it’s 85000 rows by 1 column, the result will have 19464 items (85000-65536*1). If you have 147000 rows, the result will have 15928  (being 147000-65536*2) and so on.

 

I’ve reported this but had no feedback as yet.

Super secret SpecialCells

I’m sure we’re all familiar with the Range.Specialcells method. If you’re not, you need to be, because it’s incredibly useful, so go and check it out now. Go on, we’ll wait.

 

OK, now that we’re all on the same page, you will be aware that there are 2 arguments you can supply to the SpecialCells method:

  • Type (required).
    • This is, naturally, the type of cells you are interested in and the available values are shown as those of the XlCellType Enum. More on this in a minute.

     

  • Value (Optional).

    When the Type argument is xlCellTypeConstants or xlCellTypeFormulas, you can specify which type(s) of values you are interested in:

Enum name

Literal value

xlNumbers

1

xlTextValues

2

xlLogical

4

xlErrors

16

 

Note: these values are additive (like the buttons for a MsgBox call), so if you want numbers and text, you can specify xlNumbers + xlTextValues

As far as I can tell, you can supply any number between 1 and 255 for the Value argument but it will be ignored if the Type argument is not xlCellTypeFormulas or xlCellTypeConstants, or if it’s not a valid combination of the values in the table above.

 

So, back to point 1 as promised. According to the documentation, the valid Type values are those of the XlCellType enumeration, namely:

 

Name

Literal value

xlCellTypeAllFormatConditions

-4172

xlCellTypeAllValidation

-4174

xlCellTypeBlanks

4

xlCellTypeComments

-4144

xlCellTypeConstants

2

xlCellTypeFormulas

-4123

rxlCellTypeLastCell

11

xlCellTypeSameFormatConditions

-4173

xlCellTypeSameValidation

-4175

xlCellTypeVisible

12

 

However, this is not in fact a complete list of the possible values you can pass. In addition, you can pass values which correspond (roughly) to the order in which the items appear in the Goto – Special… dialog (below)

 

 

Type number

Equivalent

Same as XlCellTypeEnum value

Range.SpecialCells(1)

Range.SpecialCells(xlCellTypeComments)

No

Range.SpecialCells(2)

Range.SpecialCells(xlCellTypeConstants)

Yes

Range.SpecialCells(3)

Range.SpecialCells(xlCellTypeFormulas)

No

Range.SpecialCells(4)

Range.SpecialCells(xlCellTypeBlanks)

Yes

Range.SpecialCells(5)

Range.CurrentRegion

No enum value

Range.SpecialCells(6)

Range.CurrentArray

No enum value

Range.SpecialCells(7)

Range.RowDifferences

No enum value

Range.SpecialCells(8)

Range.ColumnDifferences

No enum value

Range.SpecialCells(9)

Range.Precedents

No enum value

Range.SpecialCells(10)

Range.Dependents

No enum value

Range.SpecialCells(11)

Range.SpecialCells(xlCellTypeLastCell)

Yes

Range.SpecialCells(12)

Range.SpecialCells(xlCellTypeVisible)

Yes

Range.SpecialCells(13)

Range.SpecialCells(xlCellTypeAllFormatConditions)

No

Range.SpecialCells(14)

Range.SpecialCells(xlCellTypeAllValidation)

No

 

Hopefully this will prove useful to someone. J

 

A few key things to note:

If you use the SpecialCells method applied to one cell, it typically works as if applied to the entire worksheet. However, this is not true if you use 5, 6, 9 or 10 as the Type argument.

Prior to Excel 2010, specialcells can return a maximum of 8192 areas (discrete blocks of cells). If more areas than that apply, the returned range will be the whole of the original range.

I can at present find no way using 7, 8, 13 or 14 to provide the additional options available in the Goto – Special dialog since the Values parameter seems to simply be ignored.

TechDays Online – A Microsoft Azure special


 

Please help spread the word to as much of the technical community as possible about the Tech Days Online Microsoft Azure Special event; a three day digital event happening on the 2nd,3rd,4th of June and is part of the hugely popular Tech Days Online series. 

 

IT Professionals and Developers joining will get:

 

  • Community keynote sessions with respected industry figures showcasing what others are doing with Microsoft Azure – including Scott Hanselman, Acclaimed Developer, Microsoft
  • Over 15 deep technical sessions spanning Azure 101, Apps & Architecture, Data & Machine Learning and Cloud Infrastructure & DevOps delivered by Microsoft UK experts and Gold Learning Partner QA Training to help IT Pros and Devs get started TODAY
  • The opportunity to ask questions to all of our experts though live Q&A
  • A live mystery Microsoft Azure challenge and a competition to win a drone
  • Information on the offers available across MSDN/BizSpark etc and how to take a free trial of Microsoft Azure

 

Registration Link:

https://msdn.microsoft.com/en-gb/dn973263?ls=Inbound+Phone&lsd=MVP-Site&WT.mc_id=WBNR+MVP-comms+MAY15 

 

 

Feel free to use any of the following sample tweets and posts to encourage your followers & friends to register:

IT Pro

https://twitter.com/TechNetUK/status/598813781859258368

https://twitter.com/TechNetUK/status/598813725739483136 

https://twitter.com/TechNetUK/status/598813669808480256

 

Dev

https://twitter.com/msdevUK/status/598810060685316096

https://twitter.com/msdevUK/status/598810151081021441 

https://twitter.com/msdevUK/status/598810243494117376

 

Facebook ads here:

https://facebook.com/271725486227028/posts/892574690808768

https://facebook.com/271725486227028/posts/892575537475350

https://facebook.com/271725486227028/posts/892586637474240

https://facebook.com/613969575296338/posts/1132057673487523

https://facebook.com/613969575296338/posts/1132058216820802

https://facebook.com/613969575296338/posts/1132076460152311

Office 2016 public preview now available

For those of you who aren’t already aware, there is now a public preview of the forthcoming Office 2016 available for testing – see the official blog post here.

The official Preview site is here.

 

As usual, remember it’s not an official release yet, so it’s probably best not to use it on a production system! (though to be honest, I do at home, and haven’t had any issues)

 

Enjoy. IMO it’s worth downloading just to get the new colour schemes! 🙂

 

 

Free online Virtual conference hosted by MVPs

Register to attend the Microsoft MVP Virtual Conference

 

 

Hi All – I wanted to let you know about a great free event that Microsoft and the MVPs are putting on, May 14th & 15th.  Join Microsoft MVPs from the Americas’ region as they share their knowledge and real-world expertise during a free event, the MVP Virtual Conference.

 

The MVP Virtual Conference will showcase 95 sessions of content for IT Pros, Developers and Consumer experts designed to help you navigate life in a mobile-first, cloud-first world.  Microsoft’s Corporate Vice President of Developer Platform, Steve Guggenheimer, will be on hand to deliver the opening Key Note Address.

 

Why attend MVP V-Conf? The conference will have 5 tracks, IT Pro English, Dev English, Consumer English, Portuguese mixed sessions & Spanish mixed sessions, there is something for everyone! Learn from the best and brightest MVPs in the tech world today and develop some great skills!

 

Be sure to register quickly to hold your spot and tell your friends & colleagues.

 

The conference will be widely covered on social media, you can join the conversation by following @MVPAward and using the hashtag #MVPvConf.

 

Register now and feel the power of community!


On Error WTF?

One of the more frequent questions I come across relates to the situation where an active and enabled error handler section handles the first error as expected but then fails to handle any subsequent errors. (An enabled error handler is one that is turned on by an On Error statement and an active error handler is an enabled handler that is in the process of handling an error.)

Here’s the explanation (it’s a little long, but bear with me!):

The On Error statement is the heart of VBA error-handling. Without an On Error statement, any run-time error that occurs will display an error message, and code execution will stop.

There are 4 distinct On Error options:

  1. On Error Resume Next
  2. On Error GoTo some_label/line_number
  3. On Error Goto 0
  4. On Error Goto -1

    On Error Resume Next

This is the simplest error handling option but also the most dangerous and most often misused. It ensures that when a run-time error occurs, control simply goes to the statement immediately following the statement where the error occurred, and execution continues from that point. There is no message to alert the user as to the fact that an error has occurred, or to what it might be. A typical good use of this structure is when there is a predictable error that you want to override – often assigning an object that may or may not exist to a variable. For example, when testing for the existence of a worksheet in a workbook, you can loop through all the worksheets checking the name of each one, or you can employ an On Error Resume Next statement like this:

Dim ws as Worksheet
On Error Resume Next
Set ws = activeworkbook.worksheets("some name")
If not ws is nothing then
' do stuff
End If

The danger of this is if you do not remember to reset error handling (by either simply disabling it with On Error Goto 0 or enabling an error handler – see below) all further errors in your code will be suppressed, which can make problems very hard to locate and debug – you may not even notice them until your code is already in real use, which is never a good thing!

I frequently see people simply put On Error Resume Next at the top of their procedures when they can’t figure out why an error is occurring – THIS IS NOT A GOOD IDEA!! It’s the code equivalent of hearing a strange noise coming from your car engine and simply turning the radio up. Sure, you can’t hear the noise anymore, but at some point something very bad is probably going to happen.

    On Error GoTo some_label/line_number


Enables the error-handling routine that starts at the specified line label or number. If a run-time error occurs, control passes to that specified line, making the error handler active. (The specified line must be in the same procedure as the On Error statement, or a compile-time error will occur).

    On Error GoTo 0


Disables any enabled error handler, including On Error Resume Next, in the current procedure. (It doesn’t specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0!) Without an On Error GoTo 0 statement, an error handler is automatically disabled when a procedure is exited normally.

    On Error GoTo -1


Resets the active error status (exception) to Nothing without disabling any currently enabled error handler. You should very rarely see or use this. If you find yourself using this, you should probably rethink the structure of your code. (Like Goto 0, it does not specify line -1 as the start of the error-handling code, even if the procedure contains a line numbered -1). Without an On Error GoTo -1 statement, the active error is automatically reset when a procedure is exited normally.

 

Now that we’ve covered that, why does the original problem arise? (I’ll wait while you go back and read the start to refresh your memory as to what the problem actually was)

Essentially there are two key concepts in error handling in VBA:

  • whether an error handler is enabled (we covered this above)
  • whether there is an active error condition – this can be a little surprising.

When an error occurs, an active error condition is set (what they call an exception in current VB). If there is no error handler, you see a message and code stops. That’s pretty simple.

Where it gets interesting is if there is an enabled error handler. If there is, it becomes active until the active error condition is reset. The only ways to reset an active error condition and deactivate an error handler are via a Resume, Exit Sub, Exit Function, or Exit Property statement, or via an On Error Goto -1 statement. Note: On Error Goto 0 will deactivate an error handler, but will not reset the active error condition so you cannot follow it with another On Error statement (other than an On Error Goto -1 to clear the error) and hope to handle further errors. Hence, the following approach will not work:

Sub err_foo()

On Error GoTo err_handle

Err.Raise 5

Exit Sub

 

err_handle:

On Error GoTo 0

On Error Resume Next

Err.Raise 4

MsgBox “You will never see this message”

End Sub

While the current procedure’s error handler is active, or there is an active error condition, no further errors can be handled by that procedure. If another error occurs during this period, control returns to the calling procedure, if any, or an error message is produced and processing stops.

Typically in the questions I see, there is no Resume statement – there’s either a GoTo statement or the error handling label/line number is just the start of another section of code, or precedes a looping statement (Next, Wend, Loop for example). None of these scenarios will work because the error condition is not reset, and so the error handler is still active, and cannot handle further errors.

Sometimes I see people try to use Err.Clear to reset the error condition but in actual fact this merely clears the properties of the Err object, which is always available and holds information about the last error to occur. It is not the same as the active error condition and cannot be used to reset it.

General comments:

An error-handling routine is not a Sub procedure or a Function procedure. It is simply a section of code marked by a line label or a line number.

To prevent error-handling code from running when no error has occurred, place an Exit Sub, Exit Function, or Exit Property statement immediately before the error-handling routine.

 

Examples:

I plan to add some code snippets here soon as a test of what you just read – your task will be to figure out what will happen in each of them before actually running the code! 😉

 

Final takeaway: 

If you find yourself using On Error Goto -1 a lot (or at all), you need to stop and rethink what you are doing! I have never, ever, seen well-written code that required it and have never used it myself in actual production code.