String concatenation is like the weather…

…everyone complains about it, but nobody does anything about it. Well, certainly not Microsoft, anyhows. But back in 2012 Nigel Heffernan at did: he put up some nifty code for joining and splitting two dimensional arrays that I just stumbled across. I thought I’d have a go at turning the join one into a function that can be called from the worksheet, and add a few more options while I’m at it. More butchering than tweaking, as you’re about to see.

My revision can be called from the worksheet, and has the following arguments:

Yes, more arguments than at my last social outing. Most are optional and have defaults. Take that how you will. The default delimiter is a comma. The Field Delimiter is a separate Delimiter that gets added if your input array is 2D, and the default is also a comma. EndDelimiter puts an extra Delimiter of your choice on the end if you want one. Aesthetics only, really. The rest are explained below.

First, the result:
JoinText 20141115

  • That orange block is my data.
  • Column D shows the result if you point the function at each respective row
  • Row 8 shows the result of pointing the function at each respective column
  • In rows 12 to 15 you see the result of pointing it at the entire 2D block of data, under different settings.

Those last two results are what happens if the data is laid out by row and then by column, and you’ve incorrectly told the UDF to transpose the input array. If your data happenned to be laid out like this, you wouldn’t need that Transpose argument:
JoinText 20141115 2

The DelimitEnd argument does something pretty minor, really. If we include it, the end of the string gets padded with it – in this case an Exclamation Mark . If we exclude it, the string doesn’t get padded with any extra delimiters:
JoinText 20141115 3

You might notice it skips blanks. It doesn’t have to, if you don’t want it to:
JoinText 20141115 4

And it doesn’t need your two arrays to be the same size:

JoinText 20141115 5

A real-world example where this might be useful is when concatenating lists of names, where some may have more parts than others:
JoinText 20141115 6

Both the last two screenshots show examples of using three different delimiters…a space between words, a comma between columns, and something different on the end.

Here’s the code and workbook:
Join Function_20141115

Public Function JoinText(target As Range, _
Optional Delimiter As String = “,”, _
Optional FieldDelimiter As String = “,”, _
Optional EndDelimiter As String = “”, _
Optional SkipBlanks As Boolean = False, _
Optional Transpose As Boolean = False) As String

‘Based on code from Nigel Heffernan at

‘ Join up a 2-dimensional array into a string.

‘ ####################
‘ # Revision history #
‘ ####################

‘ Date (YYYYMMDD) Revised by: Changes:
‘ 20141114 Jeff Weir Turned into worksheet function, added FinalDelimiter and Transpose options
‘ 20141115 Jeff Weir Changed FinalDelimiter to EndDelimiter that accepts string, with default of “”

Dim InputArray As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim lngNext As Long
Dim i_lBound As Long
Dim i_uBound As Long
Dim j_lBound As Long
Dim j_uBound As Long
Dim arrTemp1() As String
Dim arrTemp2() As String

If target.Rows.Count = 1 Then
If target.Columns.Count = 1 Then
GoTo errhandler ‘Target is a single cell
‘ Selection is a Row Vector
InputArray = Application.Transpose(target)
Transpose = True
End If
If target.Columns.Count = 1 Then
‘ Selection is a Column Vecton
InputArray = target
‘Selection is 2D range. Transpose it if that’s what the user has asked for
If Transpose Then
InputArray = Application.Transpose(target)
Transpose = True
Else: InputArray = target
End If
End If
End If

i_lBound = LBound(InputArray, 1)
i_uBound = UBound(InputArray, 1)
j_lBound = LBound(InputArray, 2)
j_uBound = UBound(InputArray, 2)

ReDim arrTemp1(j_lBound To j_uBound)
ReDim arrTemp2(i_lBound To i_uBound)

lngNext = 1
For i = j_lBound To j_uBound
On Error Resume Next
If SkipBlanks Then
If Transpose Then
ReDim arrTemp2(i_lBound To WorksheetFunction.CountA(target.Rows(i)))
ReDim arrTemp2(i_lBound To WorksheetFunction.CountA(target.Columns(i)))
End If
End If
If Err.Number = 0 Then
k = 1
For j = i_lBound To i_uBound
If SkipBlanks Then
If InputArray(j, i) <> “” Then
arrTemp2(k) = InputArray(j, i)
k = k + 1
End If
arrTemp2(j) = InputArray(j, i)
End If
Next j
arrTemp1(lngNext) = Join(arrTemp2, Delimiter)
lngNext = lngNext + 1
End If
Next i

If SkipBlanks Then ReDim Preserve arrTemp1(1 To lngNext – 1)
If lngNext > 2 Then
JoinText = Join(arrTemp1, FieldDelimiter)
Else: JoinText = arrTemp1(1)
End If
If JoinText <> “” Then JoinText = JoinText & EndDelimiter

End Function

I like this function. I’m sure I’ll like it even more when you’re all finished polishing it to a bright sheen.


No you CANNOT have more of the same

I like Doug Glancy’s UndoSelections code via his Selectracker utility. It allows you to deselect a cell from a selection that you made while holding the Ctrl Key…something that Excel doesn’t let you do out of the box. Nifty.

(Aside: If you’re NOT a Ctrl freak, then you can also put Excel into Add To Selection mode by pushing Shift + F8, which adds any further cells you click on to the current selection without the need to hold down Ctrl. When you’ve got the cells you want, just push Shift + F8 again.)

I thought I’d try a simpler approach…if the user tries to select something that’s already selected, simply dump it from the current selection. So I came up with this:

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Deselect Target
End Sub

Sub Deselect(Target As Range)
Dim lngCount As Long
Dim lngLast As Long
Dim strTarget As String
Dim strOld As String
Dim strNew As String

'This code allows you to deselect cells when CTRL + Clicking
strTarget = Target.Address
lngCount = UBound(Split(strTarget, ","))
If lngCount > 0 Then
strNew = "," & Split(strTarget, ",")(lngCount) & ","
'Need to add the "," as a delimiter so we don't incorrectly identify say $A$1 and $A$10 as the same
strOld = "," & Left(strTarget, Len(strTarget) - Len(strNew) + 1) & ","
If InStr(strOld, strNew) > 0 Then
If strOld <> strNew Then
strOld = Replace(strOld, strNew, ",")
End If
If Right(strOld, 1) = "," Then strOld = Left(strOld, Len(strOld) - 1)
If Left(strOld, 1) = "," Then strOld = Mid(strOld, 2, Len(strOld))
Application.EnableEvents = False
Range(Split(strOld, ",")(UBound(Split(strOld, ",")))).Activate
Application.EnableEvents = True
End If
End If
End Sub

Here’s an illustration: below is a screenshot where I was trying to select cells in a Checker-board pattern while holding Ctrl, but made a stuff-up a couple of clicks ago:



Without VBA, I’d need to start from scratch, because Excel doesn’t let you deselect particular blocks from your current selection. But with my trusty code, all I need to do is try to select the offending block again, and Excel will say Hey…you’ve already got that in your selection. Oh wait…I guess you’re trying to tell me that you want to dump that particular range from the selection, given it’s already selected.

And so it does just that:
…which frees me up to try again:
In fact, as long as I keep holding Ctrl down, I can deselect as many ranges as I want:

It works pretty well. See for yourself: Open the below sample file, hold Ctrl down and do some crazy clicking, and occasionally click something you’ve already selected. ZAP! It’s removed from the current selection.
Unselect_20141111 v3 (Note: I’ve updated this file with snb’s version of the code listed further below.)

Why this isn’t the native behavior right out of the box is beyond me.

There’s bound to be coding improvements, so let’s have ’em.


snb has a much smarter approach in the comments that lets users deselect individual cells within a particular subs-selection OR deselect a sub-selection in its entirety. I’ve amended the sample file accordingly.

His approach goes a little something like so:

Private WithEvents App As Application
Option Explicit

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Deselect Target
End Sub

Sub Deselect(Target As Range)

Dim rn As Range
Dim cl As Range
Dim sel As Range
On Error Resume Next
Set rn = Target.Areas(Target.Areas.Count)

If Target.Count > 1 And Target.Areas.Count > 1 Then
If Not Intersect(Range(Replace(Target.Address & "~", "," & rn.Address & "~", "")), rn) Is Nothing Then
For Each cl In Target
If Intersect(cl, rn) Is Nothing Then Set sel = Union(sel, cl)
If Err.Number <> 0 Then Set sel = cl
End If
End If
End Sub

And so with SNB’s code, if I were to select a block:
snb block
…and I wanted to ditch the cell in the middle, then I can simply select it while holding Ctrl, and it gets ditched:
snb block 2
Meaning that I can then say apply formatting, to create an in-cell donut:
snb block 3
Much better than my approach. Cheers, snb!


Well here’s something that I probably learned once, but have subsequently forgotten and then rediscovered: You can filter a PivotTable Page Field just by typing the PivotItem that you want to filter it on. So if I start with a PivotTable like this:
Pivot Unfiltered
And say I don’t like the look of the mysterious substance that the kids have left smeared all over the mouse (probably just jam, but who the hell knows). I desperately want to filter that PivotField, but I desperately want to avoid the mouse. Well, watch what happens if I ust overtype the (All) in the PageField with the thing I want to filter by:
 Pivot Overtype PageField
Pivot Filtered
What’s more, if I type the name of a field that’s not already in the PivotTable over the existing PageField name:
Pivot New PageField
…then Excel does something else intelligent: it says “Oh, you want me to bring that PageField into the Pivottable for you.”
Pivot New PageField Inserted
The same thing in terms of adding new Fields goes for RowFields:
Pivot New RowField
Pivot New RowField Inserted
If you think about it, the adding of the fields is the same behavior as simply overtyping fields already in the PivotTable to rearrange them.

Regardless, now that this secret’s out, I don’t have to chip the sticky crap from my Mouse. As Phyllis Diller once said…Housework can’t kill you, but why take a chance? No, I don’t know who she is, either.

Two Times Table

So if you’ve played around with Tables a fair bit, then you probably would have noticed that you can merrily insert a row in the worksheet that intersects one table:
Insert OK

…but if you try the same thing on a row that intersects more than one table, the Insert, Delete, and Clear Contents options are grayed out (or greyed out, if you live where I live):
Insert Not OK

You may have tried to get around Excel’s veto by cutting or copying a row from somewhere else, then trying to insert it via the ungrayed (or ungreyed) Insert Copied Cells option:
Insert Copied Cells
…at which point Excel wiped that smirk of your face with this:
Not allowed

So I know when this happens. But I don’t understand why. Anyone have any ideas?

Formula Auditing – woes and arrows

I’ve been playing around with the Formula Auditing tools a fair bit recently. These things:
Formula Auditing Tools

In the course of this, I noticed a few things I haven’t before. Firstly, here’s my setup:
Formula Auditing Example_No Arrows
When I have cell D6 selected and click Trace Precedents, Excel is kind enough to draw just one arrow from the precedent range, while putting a box around the entire Precedent range so I can see where it is:
Formula Auditing Example_TracePrecedents
If I were to click on Trace Dependents for that cell, I’d like to see pretty much the same thing:
Formula Auditing Example_DesiredTraceDependents
…but here’s what I actually see:
Formula Auditing Example_TraceDependents
…which looks like something that Hippies were hanging on the walls in the late sixties:
String Picture
…when they weren’t out protesting, that is:
String Protest
Doing a Trace Precedents when there’s a much longer array of dependent cells involved looks even worse:
Formula Auditing Example_TraceDependents_Many
…and Excel becomes very sluggish as you scroll around, so presumably Excel is constantly redrawing these. Scrolling down doesn’t tell you much…that’s for sure:
Formula Auditing Example_TraceDependents_Many2
Let’s take a look at another setup, to better illustrate a couple of things I didn’t know until now:
Formula Auditing Example2_blank
Here’s what Trace Dependents has to say about cell B2:
Formula Auditing Example2_TraceDependents_Level1
One thing I didn’t realise until today, is that if you keep clicking that Trace Dependent button, Excel keeps drawing in additional levels of downstream dependents:
Formula Auditing Example2_TraceDependents_Level2
Formula Auditing Example2_TraceDependents_Level3
Formula Auditing Example2_TraceDependents_OffSheet
In case you didn’t know, you can double-click on any of the blue arrows, and you’ll be taken to the Precedent/Dependent cell concerned…particularly handy if it points somewhere off-screen. And you can double-click the arrow once you’re there to be magically transported back again. The dotted arrow pointing to a little sheet icon in the above screenshot tells you that there’s an off-sheet dependent that points at cell C19, which you can jump to if you double click on that dotted arrow and then select the reference from the Go To box:
Formula Auditing Example2_Goto
…although as you see above, the native dialog box is so narrow that you’re unable to actually see the cell addresses, and can’t be resized. In that case, you might want to download Jan Karel’s excellent RefTreeAnalyser, that fixes this and does a good deal more besides:
Formula Auditing Example_JK_UserForm
It also has a much better way of displaying precedents, by overlaying in the current window some little pictures of any precendents that happen to be out of view or on another sheet. (Would be really handy to have the same functionality for dependents too.):
Formula Auditing Example_JK_Visualise
Colin Legg has some great code that will also help you to determine all on-sheet and off-sheet precedent cells, using the .NavigateArrow method to actually travel up those blue arrows and thus find any precedents on other sheets. I imagine Jan Karel uses pretty much the same approach. [Edit: No, he doesn’t ]. You’ve got to use the .NavigateArrow method, because the Range.Precedents property doesn’t return precedents on other sheets or other workbooks.

Now here’s something nasty I’ve just noticed about the native Formula Auditing tool: It doesn’t pick up on off-sheet references that involve any kind of Table Reference, although on-sheet references work just fine:
Formula Auditing Example_Tables offsheet
So it is well broken, in my opinion, because I often refer to Tables on other sheets. And both Colin’s code and Jan Karel’s addin won’t help you here, I’m afraid. [Edit: Jan Karel’s code still catches these.] Seems to me the only way to get around this would be to search the worksheet for instances of a Table’s name occurring within formulas. That’s assuming there’s no way to actually read Excel’s dependency tree from wherever Excel maintains it. I seem to recall seeing a post a few years back about how you can extract information from the tree by extracting XML from the workbook file, but that might just be a flight of fancy. Anyone know whether it ispossible to interrogate the dependency tree directly somehow?

How ’bout colours instead of Arrows?

Given all those arrows can get pretty confusing, I thought I’d have a crack at coding up something that lets you use Conditional Formatting instead and/or arrows to highlight Dependents (Green) and Precedents (Blue). Here’s my starter for ten, with both Dependents and Precedents highlighted. Direct Dependents/Precedents get a darker colour and white bolded font so you can easily tell them from indirect:
Formula Auditing Example_Jeff1
…and you can restrict it to just showing direct:
Formula Auditing Example_Jeff2
…and overlay arrows if you want:
Formula Auditing Example_Jeff3
It automatically updates if you change your selection, too:
Formula Auditing Example_Jeff4

It doesn’t solve the Table issue mentioned above, but I’ve been finding it quite handy to get a quick feel for what’s going on in those crappy spreadsheets I inherited…err…designed recently.

Here’s a sample file:

Sync Pivots from dropdown

Over at the Excel Guru forum, Yewee asks:

I have 3 sheets in my excel worksheet.

1. Org
2. DataSource
3. Pivots Table

My Pivot table will get the data from the DataSource sheet. I will like to have the filter of the Pivot Table from one of the cell in Org Sheet.

How can I do that?

Incredibly easily, if you have Excel 2010 or later…because:

  • a PivotTable with nothing but one field in the Filters pane looks and behaves pretty much exactly like a Data Validation dropdown does; and
  • that PivotTable can be hooked up to the other PivotTables via slicers, so that it controls them.

If you’re a long-time reader of this blog you probably already know that, and may want to skip to the end to find a bit of VBA that makes setting up Slicers slightly more easy. But if you came here via Google, then pull up a pew and read on.

So let’s say these are the two Pivots that you want to control via a dropdown, and you want to put the dropdown where the red rectangle is:

Two Pivots and target


First, create a new PivotTable from the datasource that the other pivots share (or make a copy of one of the existing Pivots) and in the PivotTable Fields pane add the field you want to filter the other Pivots by to the Filters pane. (If you created this Pivot by copying another, remove any other fields that might appear).

Faux DV and Fields List

Great: Now you have a PivotTable masquerading as a Data Validation Dropdown. From now on, I’ll call it the ‘Master Pivot’. So just drag that Master Pivot where you want it:

Faux DV and Pivots


From the ANALYZE tab of the PivotTable Tools contextual menu in the ribbon, click the Insert Slicer icon:

Insert Slicer


…and from the menu that comes up, choose the field name that matches the field you put in the Master Pivot:

Chosen field

…and your slicer will magically appear:

Slicer added


Now we connect that Slicer to the other PivotTables. To do that, right click on the Slicer that just appeared, and click the Report Connections option:

Right Click


You’ll see from the Report Connections box that comes up that currently it’s only connected to one PivotTable – which of course is the Master PivotTable that we used to insert the slicer in the first place:

Report Connections Master


What we want to do is connect it to the other PivotTables, by checking those other checkboxes:



(Optional) We might want to make it so that the user can only select one thing at a time by clicking on the Master Pivot filter dropdown, and unchecking Select Multiple Items, if that’s your intent:

Dont select multiple items


…and now all we need to do is move that Slicer somewhere out of sight (but don’t delete it):

Faux DV and Pivots


Now when we select a region from that Master Pivot dropdown…

Select Region

… all the other Pivots are filtered to match:



That’s it…job done. As simple as possible, and no simpler.

Actually that’s a lie…unless there’s a good reason not to, it’s much simpler just to use a Slicer in the first place, and not bother with setting up the Master Pivot dropdown at all:

Just Use Slicer


Of course, that Slicer takes up much more room than our Master Pivot dropdown. So maybe that’s a good reason to use the Master Pivot approach, and not a slicer. Especially if we might want more than one dropdown to control all the Pivots and space is at a premium:

Multiple Dropdowns

Or you can do away with the Master Pivot altogether, and just set the slicers up between the actual ‘output’ pivots themselves, so that as soon as they change a PivotFilter setting in one of the Pivots, the others get changed too. (Note that this also happens with the ‘Master Pivot’ approach…it’s just that we don’t actually need to have that Master Pivot sitting there taking up space at all).

Programatically add and connect Slicers

I’ve always found it annoying that there’s no right-click option to add a Slicer to the currently selected PivotField. Plus connecting Slicers to multiple PivotTables is a drag. And also, I hate it how it adds new Slicers over the top of old slicers. So here’s some code that remedies all that:

Sub AddSlicer()
Dim pt As PivotTable
Dim ptOther As PivotTable
Dim pf As PivotField
Dim pc As PivotCache
Dim rng As Range
Dim sc As SlicerCache
Dim varAnswer As Variant
Dim bFoundCache As Boolean
Dim rngDest As Range

Set rng = ActiveCell

On Error Resume Next 'in case user has not selected a PivotField
Set pt = rng.PivotTable
Set pc = pt.PivotCache
Set pf = rng.PivotField
On Error GoTo 0

If pt Is Nothing Then Exit Sub

If pf.Orientation <> xlDataField Then
Set rngDest = Intersect(ActiveCell.EntireRow, ActiveCell.Offset(, ActiveCell.CurrentRegion.Columns.Count + 1))
On Error Resume Next 'SlicerCache might already exist
With rng
If pt.PivotCache.OLAP Then
Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, .PivotField.CubeField.Name)
Else: Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, .PivotField.Name)
End If
sc.Slicers.Add SlicerDestination:=ActiveSheet, Top:=rngDest.Top, Left:=rngDest.Left
End With
If Err.Number > 0 Then 'SlicerCache already existed. Work out what it's index is
On Error GoTo 0
For Each sc In ActiveWorkbook.SlicerCaches
For Each ptOther In sc.PivotTables
If ptOther = pt Then
bFoundCache = True
Exit For
End If
Next ptOther
If bFoundCache Then Exit For
Next sc
End If

varAnswer = MsgBox(Prompt:="Make Slicer control the " & pf.Name & " field in all Pivots on the same sheet?", Buttons:=vbYesNo)
If varAnswer = vbYes Then
For Each ptOther In ActiveSheet.PivotTables
If ptOther.CacheIndex = pt.CacheIndex And ptOther.Parent.Name = pt.Parent.Name Then
sc.PivotTables.AddPivotTable ptOther
End If
End If

Else: MsgBox "You can't add a Slicer to a Values field."
End If
End Sub

In addition, the below code will add the Add Slicer icon to the right-click menu that comes up when you right click on a PivotField:

Option Explicit

Private Sub Workbook_Open()
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Sub AddShortcuts()
Dim cbr As CommandBar


Set cbr = Application.CommandBars("PivotTable Context Menu")

With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Add Slicer"
.Tag = "AddSlicer"
.OnAction = "AddSlicer"
.Style = msoButtonIconAndCaption
.Picture = Application.CommandBars.GetImageMso("SlicerInsert", 16, 16)
End With

End Sub

Sub DeleteShortcuts()

Dim cbr As CommandBar
Dim ctrl As CommandBarControl

Set cbr = Application.CommandBars("PivotTable Context Menu")

For Each ctrl In cbr.Controls
Select Case ctrl.Tag
Case "AddSlicer"
End Select
Next ctrl

End Sub

…meaning whenever I right click on a PivotField I get this:



Clicking on that adds a Slicer to the selected field automatically, plus asks you:

Control all pivots


Hell yes, I do!

Here’s a sample file:



A couple of quirky bugs…

Found a couple of funny bugs today that I thought I’d share.

First, this one:

Weird: Partially italicizing right-aligned text screws with the display of trailing spaces. But it doesn’t do the same to left-aligned text with leading spaces:

And then there’s this one, where you start with a file that you’ve suppressed gridlines on:
…and then you add a New Window…which allows you to look at a different parts of the same file on dual monitors – or even the same monitor if it’s suitably wide, by clicking on this:
New Window2

(Aside: I never rated this feature as being particularly useful until I got a second monitor and then recently rediscovered it. Now I’m starting to think is indispensable, as I no longer have to scroll around half as much as I do when getting to grips with how spreadsheets are laid out, or when putting in new formulas that point from one region of a workbook to a completely different region of the same workbook. I’m sure Dick has been using it for years to avoid rodenting.)

But when you compare the new window (which Excel temporarily renamed SomeFileName:2) to the old (which Excel temporarily renamed SomeFileName:1), there’s a couple of subtle differences:


So it respects my wishes to not show the Formula bar, but ignores my wish to not display gridlines and headings. And if I close the original window – the one called SomeFileName:1 – then those settings I didn’t want from SomeFileName:2 are now in the file SomeFileName. Bummer.

So make sure that’s the one you close when you’re done. Otherwise your file now has gridlines and/or headings when you didn’t want it to.

One of these things is not like the other…

Sometimes when I copy code from the web and paste it into VBA, I get something like this:

…and I’m damned if I know why. It’s not the usual culprit of incorrect quote marks. It’ something else, and I don’t know what.

Usually I just retype the offending line:
No error
…and after looking in vain for a difference, simply delete the bad one, and put it down to inexperience.

But not today. Because I’m tired of being compiled around. So today, I’m gonna find out why. And I’m going to use Excel to keep VBE honest. Conquer and #DIV/0, I say.

If I paste the two formulas into separate cells in Excel, then I can clearly see that something is amiss. I just can’t clearly see the actual something:
Excel 1

Okay, let’s get nasty, and atomise these suckers so I can compare their DNA:


Well take a look at that…there’s the culprit:
No wonder I couldn’t see it:

In this particular case, the culprit looks like Ken Pul’s Blog platform…I stole the code from a comment Jan Karel left there, and note that the code has no similar issue if I lift it directly from Jan Karel’s site.

There you go. Busted.

Makes me feel like singing: