Why is it that you can do this:
Dim lo as ListObject
Set lo = ActiveCell.ListObject
If lo Is Nothing Then 'Do something
…but you can’t do this:
Dim pt as PivotTable
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then 'Do something
…and instead you have to do this:
Dim pt as PivotTable
On Error Resume Next
Set pt = ActiveCell.PivotTable
If Err.Number > 0 then 'Do something
Err.clear
Huh? Huh?
Jeff –
This is when I rub my chin thoughtfully and say, “Excel is like that sometimes.”
See, I’m never sure whether it’s because I don’t understand the object model well enough, or because the object model has a lot of inconsistencies in it. Or both of course.
There are other examples that throw an error like the pivot table code, but offhand I can’t think of one. When it happens, though, it takes ten seconds to shrug and insert On Error Resume Next. I still use If pt Is Nothing instead of If Err.Number > 0.
I hadn’t thought of testing via If pt Is Nothing. That’s neater.
It’s also the same structure as before you added the On Error.
Wouldn’t it be awesome if Microsoft created “.Exists” for objects? I just ran into the same thing with Worksheets. If I could just do:
If Worksheet(“HereIam”).Exists = False Then Worksheets.Add.Name = “HereIam”
or even
If isError(Worksheet”HereIam”).Activate) Then Worksheets.Add.Name = “HereIam”
Instead we get to jump through error handling hoops. And by the way, you really need On Error Goto 0 and your last example. Oh how life gets fun when you forget that!
I can think of things I’d rather have (i.e., that there are no workarounds for). I have plenty of boilerplate code with functions like SheetExists(sSheetName) or PivotFieldExists(PT, sPivotFieldName) and the like.
On the other hand, for example, I have no way to make Excel treat a cell containing a formula as if it’s blank. I can use “” to make it look blank, I can use NA() to make a chart not plot a marker at that point, but I can’t use BLANK() or NULL() to make a chart leave a gap in a line at that point, because they don’t exist.
I have a more generic function called “IsIn” for doing tests like that:
Function IsIn(ByRef colCollection As Variant, ByVal sName As String) As Boolean
'-------------------------------------------------------------------------
' Procedure : IsIn Function Created by Jan Karel Pieterse
' Company : JKP Application Development Services (c) 2006
' Author : Jan Karel Pieterse
' Created : 11-03-2013
' Purpose : Determines if object/item is in collection
'-------------------------------------------------------------------------
Dim vMember As Object
On Error Resume Next
For Each vMember In colCollection
Err.Clear
If vMember = sName Then
If Err.Number = 0 Then
IsIn = True
Exit Function
End If
End If
Err.Clear
If vMember.Name = sName Then
If Err.Number = 0 Then
IsIn = True
Exit Function
End If
End If
Next vMember
End Function
There’s still a workaround for that, but it’s just way uglier than boilerplate code.
You have to graph a separate series for each line segment. I’d have one line with the data (using #N/A where I want gaps). Then in the rows below it use a formula like this for as many line segments as I needed to handle: =IF(COUNTIF($B$3:B$3,NA())=0,B$3,NA()), and changing the “=0” part to =1, =2, etc. for each line segment in a separate row (or put the value in another column and reference it like $A4).
I hate having to do helper rows to make charts look good, but with Excel it seems like you almost always need helper rows for a good chart. I agree that these type of charting fixes would be much more helpful.
Mike –
Yeah, but that’s way beyond a “workaround”.
Jan – Well done! That is beautiful code, but you realize you are encouraging me to whine in the comments more… Thanks for sharing!
Mike –
I just remembered how I approached the gaps in a line problem, over a decade ago. I wrote a procedure that stepped through the values in a chart series, and if there was #N/A (and I think text, to cover the “” problem too), it changed the format to “No Line”. You had to run it whenever the data changed, though.
An alternative was using a separate data range that linked to the data to be plotted. If any cells in the original data were unplottable, the corresponding cells in the shadow range were cleared, leaving a gap in the chart. This would also need to be rerun every time the data changed.
I can think of things I’d rather have, too. Lots around ListObjects and PivotTables.
What prompted this little whinge, is that when I see something like Set lo = ActiveCell.ListObject working and then see something like Set pt = ActiveCell.PivotTable not working, I wonder if there’s some subtle dichotomy that I’m not aware of.
The subtle dichotomy is mostly because the different features were developed in different eras by different subsets of a large dynamic group.
Ah, good to know it’s not me. Because I neither ‘get’ nor ‘do’ subtle.
Jon – It’s always a debate for me whether to fix these kind of issues with “helper cells” or VBA. If it’s my sheet and I’m responsible for updating the data, I prefer VBA. It’s usually quicker for me to write, and easier to understand later what I did. If I’m going to set up the file and turn it over to someone else, the “helper cells” method is the only way to go. The chances of the average user figuring out the VBA when they want to add data or make changes is slim to none. I’m not sure if the average user does much better with helper cells (they usually get the job done but totally destroy the setup), but my power users tend to do ok.
Mike –
In general I like the helper cell approach. It’s all right in front of people, and if they diligently make backups (doesn’t everyone?) they can fearlessly hack away at the formulas. Also it means sending out xlsx files, so no macro warnings. And the updates happen in Excel’s usual recalc process, so the user doesn’t have to remember to run some code, and I don’t have to build in some kind of event code to run the code.
But if it’s a more general purpose thing, I’ll distribute an add-in and put the procedures behind a ribbon button.
Jeff, I have nothing helpful to add, but great question. I got bit by this a few weeks back in some code where I was using both. I don’t think I’d ever realized it before. Now the trick is remembering which works. Maybe the title of this post will help. But I’ll have to remember that it’s sincere :).