MrExcel.com - Your One Stop for Excel Tips & Solutions

Copy the Quick Stats Values to the Clipboard

July 10, 2014 - by

The question came during an Excel seminar in Tampa: Wouldn't it be cool if you could copy the statistics from the status bar to the clipboard for later pasting to a range?

I pressed the person who asked the question on exactly how the paste should work. Of course, you can not paste the statistics immediately, because you have a bunch of important cells selected. You would have to wait, select another blank range of the spreadsheet, the paste (as in Ctrl+V) and the statistics would appear in a 6-row by 2-column range. The person who asked the question suggested they would be static values.

I did not try to answer the question during the seminar, because I knew it might be a bit tricky to pull this off.

But, I recently started a macro to see if this could be done. My idea was to build a long text string that could be pasted. In order to force the items to appear in two columns, the text string would have to have the label for column 1 (Sum) and then a Tab, and the value for column 2. You would then need a carriage return, the label for row 2, column 1, then another tab, the value, and so on.

I knew that Application.WorksheetFunction is a great way to return the results of Excel functions to VBA, but that it does not support all 400+ Excel functions. Sometimes, if VBA has a similar function already (LEFT, RIGHT, MID), then Application.WorksheetFunction will not support that function. I fired up VBA with Alt+F11, displayed the Immediate Pane with Ctrl+G, and then typed some commands to make sure all six status bar functions were supported. Luckily, all six returned values that matched what was appearing in the status bar.

To make the macro shorter, you can assign Application.WorksheetFunction to a variable: 

Set WF = Application.WorksheetFunction

Then, later in the macro, you can simply refer to WF.Sum(Selection) instead of typing out Application.WorksheetFunction over and over.

What is the ASCII code for a Tab?

I started building the text string. I chose a variable of MS for MyString. 

MS = "Sum:" &

This is the point where I needed a tab character. I am geeky enough to know a few ASCII characters (10=LineFeed, 13=Carriage Return, 32=Space, 65=A, 90=Z), but I could not remember the Tab. As I was about to head to Bing to look it up, I remembered that you could use vblf in your code for a linefeed or vbcr in your code for a carriage return, so I typed vbtab in lower case. I then moved to a new line to allow Excel VBA to capitalize the words that it understood. I was hoping to see the vbtab pick up a capital, and sure enough, the line became capitalized, indicating that VBA was going to give me a tab character.

If you type your VBA in lower case, when you go to a new line, you will see all the correctly spelled words pick up a capital letter somewhere in the word. In the image below, vblf, vbcr, vbtab are known to vba and get capitalized after moving to a new line. However, the thing that I made up, vbampersand is not a known thing to VBA, so it does not get capitalized.

At this point, it was a matter of joining 6 labels and 6 values into one long string. Remember in the code below that the _ at the end of each line means that the line of code is continued on the next line. 

Sub CopyQuickStatsToClipboard1()
Set WF = Application.WorksheetFunction
MS = "Average: " & vbTab & WF.Average(Selection) & vbCr _
& "Count: " & vbTab & WF.CountA(Selection) & vbCr _
& "Numerical Count: " & vbTab & WF.Count(Selection) & vbCr _
& "Min: " & vbTab & WF.Min(Selection) & vbCr _
& "Max: " & vbTab & WF.Max(Selection) & vbCr _
& "Sum: " & vbTab & WF.Sum(Selection) & vbCr

MsgBox MS
End Sub

After joining all of the labels and values together, I wanted to admire my work, so I displayed the result in a MsgBox. I ran the code, and it worked beautifully:

I thought that I was home free. If I could just get MS on to the clipboard, I could start recording Podcast 1894.  Maybe MS.Copy would do the trick?

Unfortunately, it was not that easy. MS.Copy was not a valid line of code.

So, I went to Google and searched for "Excel VBA Copy Variable to Clipboard". One of the top results was this post at the MrExcel Message Board. In that post, my old friends Juan Pablo and NateO were trying to help the OP. The actual tip, though, was where Juan Pablo suggested to use some code from the site of Excel MVP Chip Pearson. I found this page which explained how to get the variable on to the clipboard.

In order to add something to the clipboard, you need to first go to the VBA window's Tools menu and choose References. You will initially see a few references checked by default. Microsoft Forms 2.0 Library will not be checked. You need to find it in the very long list and add it. Luckily, for me, it was on the first page of choices, about where the green arrow shows it. Once you add the checkmark next to the reference, it moves to the top.

Chip's code will not work if you don't add the reference, so do not skip the above step!

Once you add the reference, finish the macro using Chip's code: 

Sub CopyQuickStatsToClipboard()
Set WF = Application.WorksheetFunction
MS = "Average: " & vbTab & WF.Average(Selection) & vbCr _
& "Count: " & vbTab & WF.CountA(Selection) & vbCr _
& "Numerical Count: " & vbTab & WF.Count(Selection) & vbCr _
& "Min: " & vbTab & WF.Min(Selection) & vbCr _
& "Max: " & vbTab & WF.Max(Selection) & vbCr _
& "Sum: " & vbTab & WF.Sum(Selection) & vbCr

' Thanks to Chip Pearson http://www.cpearson.com/excel/Clipboard.aspx
Dim DataObj As New MSForms.DataObject
DataObj.SetText MS
DataObj.PutInClipboard
End Sub

Before recording the podcast, I did a test to make sure it was working. Sure enough, when I ran the macro, then selected a new range and hit Ctrl+V to paste, the clipboard was emptied into a 6 row x 2 column range.

Whoo-hoo! I prepared the PowerPoint title card for the episode, turn on Camtasia Recorder, and recorded everything above. But... as I was about to show the closing credits, a nagging feeling came over me. This macro was pasting the statistics as static values. What if the underlying data changed? Wouldn't you want the pasted block to update? There was a long pause in the podcast where I considered what to do. Finally, I clicked the Camtasia Pause Recording icon and went to see if I could put a formula inside the MS string and if it would get pasted correctly. Sure enough, it did. I did not even completely finish the macro or do more than one test when I turned the recorder back on and talked about this macro. In the podcast, I theorized that this would never work for non-contiguous selections, but in later testing, it does work. Here is the macro to paste as formulas: 

Sub CopyQuickStatsAsFormulas()
Set WF = Application.WorksheetFunction
MA = Selection.Address
MS = "Average: " & vbTab & "=AVERAGE(" & MA & ")" & vbCr _
& "Count: " & vbTab & "=CountA(" & MA & ")" & vbCr _
& "Numerical Count: " & vbTab & "=Count(" & MA & ")" & vbCr _
& "Min: " & vbTab & "=Min(" & MA & ")" & vbCr _
& "Max: " & vbTab & "=Max(" & MA & ")" & vbCr _
& "Sum: " & vbTab & "=Sum(" & MA & ")" & vbCr _

' Thanks to Chip Pearson http://www.cpearson.com/excel/Clipboard.aspx
Dim DataObj As New MSForms.DataObject
DataObj.SetText MS
DataObj.PutInClipboard
End Sub

After posting the video, regular viewer Mike Fliss asked is there is a way to build the formulas that would constantly update to show the statistics for whatever range is selected. This would require a Worksheet_SelectionChange macro that would constantly update a named range to match the selection. While this is a cool bit of trickery, it forces a macro to run every time you move the cell pointer, and that is going to constantly clear the UnDo stack. So, if you use this macro, it has to be added to every worksheet code pane where you want it to work, and you will have to live without Undo on those worksheets.

First, from Excel, Right-Click on a sheet tab and choose View Code. Then, paste this code in. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Name = "SelectedData"
End Sub

Switch back to Excel. Select a new cell and type the formula =SUM(SelectedData). You will initially get a circular reference. But, then select another range of numeric cells and the total of the formula that you just created will update.

Select a new range, and the formula updates:

For me, the great discovery here was how to copy a variable in VBA to the clipboard.

In case you want to experiment with the workbook, you can download a zipped version from here.