Excel 365, Evaluate, and the Type Mismatch error

If you have a version of Excel that no longer requires you to array enter array formulas by using Ctrl+Shift+Enter, you may discover an unexpected side effect if you write code that uses Evaluate with a formula containing one or more ROW() or COLUMN() worksheet functions. Any such code will now always return an array, even if the result is just one value.

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!