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…

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.

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.