IFS is not the same as nested IF functions

According to the Office support site, “IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.”.

Although that is sort of true, you certainly can’t say that IFS works the same way as nested IF conditions. The real beauty of an IF formula, is that it only evaluates two parts – the first expression, and then either the value if true, or the value if false, but never both.

IFS, on the other hand, appears to evaluate everything you pass to it. It does not just evaluate each criterion until it finds a true one, and then calculate the related value and exit. It evaluates all the criteria, and all the related values, which is obviously not great if you are using complicated formulas for criteria, or results, or both.

In other words, this:

=IF(A1=””,””,IF(A1=1,complex_formula,IF(A1=2,another_complex_formula,third_complex_formula)))

is much more efficient than the ‘equivalent’ IFS version:

=IFS(A1=””,””,A1=1,complex_formula,A1=2,another_complex_formula,TRUE,third_complex_formula)

Sure, the latter might be a little easier to read (though I’m not even convinced about that personally), but it will always evaluate all of those formulas, even if A1 is empty. I’d suggest IFS should be employed sparingly, if at all.

Excel for Mac: are you freaking kidding me??

Still too bad, so sad.

So here we are. It’s 2019. We have Office 2019. And Excel for Mac is still a half-arsed, crippled piece of crap from a programming perspective. (from a normal user perspective, it may be just a slow, slightly embarrassing cousin of the Windows versions).

Userform designer? No. I mean seriously: it’s been three years at least; do you even care?

Properly functioning VB Editor? No. See above.

VBA performance on any sort of par with Windows? No. Maybe with 2007? Which was crap.

BI tools? No. This at least I can understand, sort of.

Anything that makes it great? Well, no. Oh wait – at least it’s not Numbers. I think that basically is the benchmark.

In all honesty, there might be some good points if you’ve never used Windows Excel, but if you have, you’ll be scrabbling for any sort of VM where you can run a full, proper version of Excel.

You are, of course, free to disagree with me, and if you do, I’d love to know why. I’m unlikely to agree with you, but at least I’ll have some more data for my clinical studies…

Power Pivot for all! Well, sort of.

Many of you will have seen the recent UserVoice update about Power Pivot, which is, of course, good news – if somewhat late, as some others have commented already.

However, that announcement seems a little misleading to me, based on what I’ve been told: it is only coming to the subscription versions of Office (i.e. Office 365) and the forthcoming Office 2019. So if you have a 2013, or non-subscription 2016, SKU which doesn’t already have Power Pivot, you’re not going to get it. It pretty much goes without saying that there will be no change for Mac Office users. Still, at least you’re no worse off!

RiP Chip Pearson

I’ve just learned that Chip Pearson sadly passed away on 19th April.

A true legend, and a really good guy in my interactions with him, he will be massively missed for his invaluable contributions to the world of Excel. He was my first introduction to the real power of VBA and I owe him a huge debt.

My deepest sympathies to his parents.

Update: thanks to Chip’s parents, and Bill and Greg at MrExcel, Chip’s website is now back up and running again. There is a thread at MrExcel here where you can add a message, which we hope to pass to Chip’s parents, to give them some idea of what a huge influence their son has been to so many people. Please do take the time.

Object model bug with msoElementPrimaryValueAxisTitleAdjacentToAxis

I recently came across an interesting bug in the object model for charts, that I have seen mentioned in several forums. The issue is that using

Chart.SetElement msoElementPrimaryValueAxisTitleAdjacentToAxis

doesn’t actually add a title to the chart axis and therefore any subsequent code that attempts to format the axis bugs out with a 424 error.

As it turns out, the problem is that there are two constants sharing the same value in the OM:

msoElementPrimaryValueAxisTitleAdjacentToAxis
msoElementPrimaryValueAxisTitleNone

both have the value 306.

The resolution is actually fairly simple – just declare your own constant like this:

Const msoElementPrimaryValueAxisTitleAdjacentToAxis As Long = 307

 

Enjoy.

Autofilter bug with Excel 2016/365 build 1706

It appears that there is a problem with Autofilters in the latest build (1706) where some values are not displayed in the filter dropdowns if you have a lot of data (say more than 20k rows). The only fix I am aware of currently is to revert back to the previous build, which didn’t have the same problem.

For instructions on reverting, see here.

Passing arguments ByRef using Run

I know what you’re thinking: you can’t do that – at least not without a convoluted workaround like public variables or passing memory locations and copying memory blocks. I thought the same too. As did everyone else, including Microsoft, as far as I can tell. However, the intrepid Jaafar Tribak at MrExcel discovered an insanely simple solution to this problem – just late bind the application object.

Yep, you heard. That’s all it takes.

So, as we know, code like this demonstrates that Run passes everything ByVal no matter what you tell it:

Sub runByVal()
    Dim sTemp As String
   
    sTemp = "Hello"
   
    Application.Run "PassByVal", sTemp
   
    ' unchanged as you would expect
    Debug.Print sTemp
   
    Application.Run "PassByRef", sTemp
   
    ' still unchanged, per documentation as the arg is passed ByVal regardless
    Debug.Print sTemp
   
End Sub

Sub PassByVal(ByVal s As String)
    s = "ByVal"
End Sub
Sub passByRef(ByRef s As String)
    s = "ByRef"
End Sub

The output of both Debug.Print statements will be “Hello”.

But with a simple change, we can pass the argument ByRef:

Sub runByRef()
    Dim sTemp As String
    Dim o ' just so we can late bind the Application: Variant or Object will work
   
    sTemp = "Hello"
   
    Set o = Application
    o.Run "PassByVal", sTemp
   
    ' unchanged as you would expect
    Debug.Print sTemp
   
    o.Run "PassByRef", sTemp
   
    ' this is now changed! Mind. Blown.
    Debug.Print sTemp
   
End Sub

Sub PassByVal(ByVal s As String)
    s = "ByVal"
End Sub
Sub passByRef(ByRef s As String)
    s = "ByRef"
End Sub

Now the output will be “Hello” followed by “ByRef”. As far as I can tell it works with all variable types including objects.

I don’t really know why it works here (late binding a class won’t make a property routine work ByRef) but it does and is really easy to implement!

Who’s With me?

I frequently see people get confused by, and misuse, the With ... End With construction. The most common misconception seems to be that anything put inside one of these clauses automatically refers back to the object used on the With line – so in other words, the belief is that this works:

With Sheets("Sheet1")
Range("A1").Value = "Hello"
Range("A2").Value = "Magical With block"
End With

and, indeed, while debugging it may seem to work – because usually Sheet1 is active so that you can see what is happening. But in fact the With block is doing absolutely nothing here – the code would do exactly the same thing if it weren’t there at all. (Feel free to remove it and see)

The simplest way I can think of to explain the way this clause should be constructed is like this:

Imagine your code written out in full, repeating the object you want to use in the With block wherever it is necessary. So, for the example above, we would start with this code:

Sheets("Sheet1").Range("A1").Value = "Hello"
Sheets("Sheet1").Range("A2").Value = "Magical With block"

Nothing too tricky there, I hope you’ll agree.

Now to convert this to a with block, we add back the With and End With lines we had before:

With Sheets("Sheet1")
Sheets("Sheet1").Range("A1").Value = "Hello"
Sheets("Sheet1").Range("A2").Value = "Magical With block"
End With

and then remove any occurrence of the object we have on the With line – but do not remove anything else:

With Sheets("Sheet1")
.Range("A1").Value = "Hello"
.Range("A2").Value = "Magical With block"
End With

Note the two differences between this version and the first version that didn’t work? Correct – the dots before the two Range calls. Those dots are what tell the compiler that the properties or methods relate back to the object referred to on the With line. Without them, the With block may as well not exist.

Now, where it can seem a little confusing is if you start to nest With blocks:

With Sheets("Sheet1")
    .Name = "New sheet"
    With .Range("A1")
        .Value = "Hello"
        .Font.Bold = True
    End With
.Range("A2").Value = "Magical With block"
End With

In truth though, all you need to remember is that any property or method that you prefix with a dot refers back to the nearest enclosing With block. So the second line refers back to Sheets(“Sheet1”), as does the third line. The fact that the third line is also a With block only affects the lines that follow, until the next End With line. After that, the next line refers back to Sheets(“Sheet1”) again.

So that’s the basics of writing these clauses, but there is one very important thing to remember about the way they work at run time:

The With line sets and holds a reference to the object in question, so any changes made to that object will be reflected in any code inside the with block. What that means is that this:

With Range("A1")
    .EntireRow.Insert
    MsgBox .Address
End With

is not the same as this:

    Range("A1").EntireRow.Insert
    MsgBox Range("A1").Address

The first version gets a reference to A1, then inserts a row, so the address of the object is now A2. In the second, the address is still A1. This is quite a trivial example purely to demonstrate the point.

The one that used to catch me out regularly was changing the location of a chart inside a With block because the Location method of a Chart actually returns a reference to a new Chart object making the old one (the one held by the With block) invalid.