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.

Office Update breaks ActiveX controls

UPDATE: 10 March 2015: Microsoft have now released fixes for this!

For Excel 2007 click here.

For Excel 2010 click here.

For Office 2013 click here. (note there doesn’t appear to be a separate Excel patch)

 

Note: I have not actually been able to verify whether these patches work, but some early reports suggest they don’t work for everyone (perhaps for the same reason that the fixes below didn’t).

I am also unsure at this time as to whether Office 2003 was affected, but have not seen any mention of a patch if it was.


 

Update: 15 Dec 2014: There is now an MSKB article about this problem with suggested solutions and a FixIt tool here: KB3025036.

 

It seems that a recent Office update (Tuesday 9th Dec) has broken ActiveX controls on worksheets. The symptoms include (but are probably not limited to):

  • Being unable to use or change properties of any active controls on worksheets
  • Error messages saying “Can’t insert object”
  • Error 438 when trying to refer to an activex control as a member of a worksheet in code

To fix it, do this:

  1. Close all Office applications.
  2. Do a search in Windows Explorer – make sure to include hidden and system files and folders – for *.exd files (note: that’s not *.exe !!) and delete any you find.
    Make sure you get these:
    C:\users\username\AppData\Local\Temp\Excel8.0\MSForms.exd
    C:\users\username\AppData\Local\Temp\VBE\MSForms.exd
  3. Reboot the computer (this is not always necessary, but probably better safe than sorry)
  4. Restart your Office apps and test the controls again.

Please note that the .exd files will be recreated when you next use a workbook with an embedded active control – this is quite normal and should not cause you a problem!

Hopefully that will resolve the problem for you. 🙂

 

Note: if you are having problems locating the .exd files, you can press Win+R to open the Run box, enter %temp% and press Enter. That should open explorer to the Temp folder and you then need to look in the Excel8.0 and VBE folders for the .exd files. (if your issue is with a program other than Excel, look for the folder related to that application – e.g. Word8.0)

 

Update: 11-Dec: It seems that for a few cases so far, this solution doesn’t resolve the issue. I will post back if and when I discover a solution for those cases too, though simply uninstalling the update mentioned at the beginning of this post may be a solution until a fix is released.

Trust me, Microsoft is aware that this is an issue – see their blog post here.

 

Update: 12 Dec 2014: I have seen mentions that if none of the above fixes the issue in Office 2013, removing this update may work: http://support.microsoft.com/kb/2920734

 

UDFs and Conditional Formatting

I came across something in the forums (where else?) this week that I did know, but had completely forgotten. It’s kind of a weird one and not always that easy to track down so I thought I’d post it here as a sort of aide memoire.

If you use a UDF in conditional formatting, you must make sure that it has error handling in it because if an error occurs in the UDF, it can stop any running code dead with no warning whatsoever. For a very good write up on UDFs in general and this issue specifically, have a read of Charles Williams’ page here. In fact, if you do any sort of complex work in Excel and you aren’t familiar with Charles’ site, stop right now and go and read all of his Excel pages. I guarantee it will be worth your time. You should also subscribe to his blog here. And finally, if you can (or can persuade your company to), get his FastExcel V3 add-in. (note to my IT department: still waiting…)

 

Now, back to our story.

The case in the thread in question was a pretty basic UDF to determine whether a cell contained a formula – simple enough code along the lines of:

 

Function CellHasFormula(rngTarget as Range) As Boolean

CellHasFormula = rngTarget.HasFormula

End Function

 

Seems innocuous enough and you wouldn’t, in general use, think it would require error handling. However, it appears that when used in conditional formatting, about the only truly safe properties of a range that you can use are .Value, .Value2 and .Formula. Anything else could be unavailable and if that is the case, your code will just stop. The sample workbook I was provided with had code called in the Workbook_Open event to set various filters on a sheet that used a UDF similar to the above to conditionally format one column of a table, but the Open event didn’t run. No errors, it just didn’t fire.

If you opened the workbook and then manually ran the Workbook_Open code it would start OK, remove filters from the sheet and then simply terminate when trying to reapply an autofilter. Again, no errors, the code just stopped.

Understandably this was proving both very frustrating and difficult to debug. (Great credit goes to Tracy at MrExcel for figuring out what the problem with this workbook was)!

 

So the moral of the story is simply: always put error handling in your UDFs!

 

Until next time.

Excel AMA (Ask Me Anything) reddit session on April 4th

From the Office blog here:

Join us Friday, April 4 at 1:00pm PDT/4:00pm EDT for the first Excel-hosted AMA (what’s an AMA?) on reddit. We get great feedback and input about Excel through customer site visits, bringing customers to Microsoft, sharing on blogs and support forums, and more, but we haven’t interacted broadly in real-time–until now. We’ll start a thread on reddit.com/r/iAMA at 12:50pm PDT/3:50pm EDT and we’d love to hear from you, so ask us anything!

 

Office for iPad is here!

Official page on the Microsoft site here.

My first impressions are, sadly, non-existent as it, of course, requires iOS 7 and I still have the first generation iPad. 🙁

Comments I can make are:

  • It requires an Office 365 subscription to allow editing/creating files, otherwise it’s just a viewer (and hardly the first of those).
  • There is no VBA support. (but at least it doesn’t remove or mangle any existing VBA in a file)
  • You can’t update external data links – so that rules out a lot of dashboards.
  • It looks pretty good from the screenshots I’ve seen and has fairly good desktop compatibility. See, for example, teylyn’s blog posting here
  • Bill Jelen has a good review of what does and doesn’t work here.

If I can ever pry my wife’s iPad away from her, I will try and review it properly. 🙂 Or, of course, it may be the excuse I need for an iPad Air…

 

When is a Range not a Range

I’m too lazy to retype everything so here is a link to the original thread at VBA Express.

I had always been under the impression that the Range property returns a new object instance every time it is called, even if you are referring to the same range. So, unlike Sheets for instance, where you can use the Is operator (or ObjPtr if you prefer) to test if one variable refers to the same sheet as another, like this:

Sub bar()
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Sheets(1)
Set ws2 = Sheets(1)
' this returns True
MsgBox ws1 Is ws2
End Sub

you cannot do the same with Range:

Sub foo()
Dim r1 As Range
Dim r2 As Range

Set r1 = Range("A1")
Set r2 = Range("A1")
' this will always return False
MsgBox r1 Is r2
End Sub

 

However, it seems that it is not the case that Range always returns a new object. It appears that there is some sort of internal heap that can be reused if necessary:

Sub foobar()
Dim w1 As Object
Dim w2 As Object
Dim w3 As Object

Set w1 = Range("A1")
Set w2 = Range("A1")
Set w3 = Range("A1")

Debug.Print "w1=" & ObjPtr(w1)
Debug.Print "w2=" & ObjPtr(w2)
Debug.Print "w3=" & ObjPtr(w3)

' clear them all
Set w1 = Nothing
Set w2 = Nothing
Set w3 = Nothing

' these should all be (and are!) 0
Debug.Print "w1=" & ObjPtr(w1)
Debug.Print "w2=" & ObjPtr(w2)
Debug.Print "w3=" & ObjPtr(w3)

' now reinitialize the variables
Set w1 = Range("A1")
Set w2 = Range("A1")
Set w3 = Range("A1")

' note that the pointers are the same but in a different order
' it appears that they are reclaimed from the top of an internal heap
Debug.Print "w1=" & ObjPtr(w1)
Debug.Print "w2=" & ObjPtr(w2)
Debug.Print "w3=" & ObjPtr(w3)

' and finally, just for kicks
Debug.Print Range("A1") Is Range("A1")
End Sub

 

I realise that this is probably of use / relevance / interest to very few people, but then that is kind of the leitmotif of this site. 😉

Custom number formats converting to Special formats

Occasionally I use custom number formats in Excel that happen to match one of Excel’s built-in Special formats. For example, if I custom format a cell as:

00000

the next time I view the cell format, Excel has merrily decided that this is a US zip code. Most of the time, I don’t care because the format still does what I need (although it can be annoying if, for example, the special format uses a different locale – e.g. using 000 as a format changes to a Chinese locale, making it difficult to read the dialog! For this case, you can specify your own locale such as [$-809]000 – 809 being for UK).

 

However, this can occasionally be an issue. Consider the following situation (which, of course, occurred in a forum thread recently):

As the developer, your PC settings use dd/mm/yy as the default Short Date format. You specifically assign the custom format:

dd/mm/yy

to some cells because you have limited space on your dashboard and don’t need 4 digit years. Everything seems fine – to you anyway.

 

Unfortunately, behind the scenes, Excel has actually decided that what you meant to do was assign a Short Date format because the custom format you chose matches your Short Date format. So when you distribute this tool proudly to your users, whose short date formats are set to use dd/mm/yyyy by default, all they see is ##### because the dates are too wide for the column widths. And you look like a numpty who doesn’t know what they’re doing. 😉

 

Luckily, the solution is pretty simple (for most situations) – include a text part in the custom format. If you, our intrepid developer, apply a custom format of:

dd/mm/yy;@

to the cells instead, this does not match the Short Date format, so Excel leaves it alone.

Since most (all?) built-in special number formats do not include a text part, this should work for most situations (unless you need all 4 custom format sections for some purpose; then you’re SOL.)

 

I hope that saves someone some time/hair/patience/money.