Selection Stats

Robin sent an idea for using the status bar to keep track of some characteristics of the selection. It’s a good idea, so I started fiddling with it. To the right of the statusbar, Excel will show you some particular stat involving the selection, like the sum:

StatusBar1

Robin’s idea was to see all those stats at once instead of having to change which one Excel is showing. I’ve had some spreadsheets where that would have been useful.

I created a class module to hold an application level event that would update the statusbar whenever the selection changed and show some stats. It could be easily modified to show any worksheet function or even a user-defined function based on the selection. Here’s the meat of the code:

Private Sub mApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   
    Dim vAvg As Variant ‘to account for errors
   Dim lCells As Long
    Dim lCnt As Long
    ‘Dim dMax As Double
   Dim vMax As Variant
    ‘Dim dMin As Double
   Dim vMin As Variant
    ‘Dim dSum As Double
   Dim vSum As Variant
    Dim dCnta As Double
   
    ‘Make sure selection is a range
   If TypeName(Target) = “Range” Then
        ‘Only when more than one cell is selected
       If Target.Cells.Count > 1 Then
            ‘Caclulate stats
           vAvg = Application.Average(Target)
            lCells = Target.Cells.Count
            lCnt = Application.Count(Target)
            ‘dMax = Application.Max(Target)
           vMax = Application.Max(Target)
            ‘dMin = Application.Min(Target)
           vMin = Application.Min(Target)
            ‘dSum = Application.Sum(Target)
           vSum = Application.Sum(Target)
            dCnta = Application.CountA(Target)
           
            ‘Concatenate statusbar message
           ‘Application.StatusBar = “Average: ” & CStr(vAvg) & ” | ” & _
                “Cell Count: ” & lCells & ” | ” & _
                “Count Nums: ” & lCnt & ” | ” & _
                “CountA: ” & dCnta & ” | ” & _
                “Max: ” & dMax & ” | ” & _
                “Min: ” & dMin & ” | ” & _
                “Sum: ” & dSum & ” | “
           Application.StatusBar = “Average: “ & CStr(vAvg) & ” | “ & _
                “Cell Count: “ & lCells & ” | “ & _
                “Count Nums: “ & lCnt & ” | “ & _
                “CountA: “ & dCnta & ” | “ & _
                “Max: “ & CStr(vMax) & ” | “ & _
                “Min: “ & CStr(vMin) & ” | “ & _
                “Sum: “ & CStr(vSum) & ” | “
        Else
            ‘Return control of statusbar
           Application.StatusBar = False
        End If
    Else
        Application.StatusBar = False
    End If
       
End Sub

I might not use this all the time because it probably hides some important statusbar messages. But if you find yourself changing Excel’s offering to show different stats for the selection, it might save some time.

SelectionStat1

Update: The old code failed when a cell contained an error (Thanks, Charlie). The above code has been revised so that Sum, Min, and Max are Variants that display the error if there is one. Old code is commented out and the new code appears below it.

21 Comments

  1. Andy Pope says:

    Hi Dick,

    Neat idea.

    I created something similar for a friend only I used a modaless useform to display the values.

    I have just modified it to work with a custom commandbar that can be docked. The only distracting thing is the length of the bar keeps changing depending upon the content. You can keep this to a minimum by formatting the output but the proportional font doesn’t help.

  2. J-Walk says:

    I also have a variation on that theme in my PUP add-in. The info is displayed in a toolbar. Click here.

  3. That popup is a great feature of Excel.

    One item I think is missing is a “Difference” mode.

    So if you highlight 2 cells it will report the difference.

    Highlighting two ranges (two areas) would report sum differences.

  4. Stacie says:

    What modifications would be necessary to show only two options, say sum and count?

    Stacie

  5. Dick says:

    Stacie: The line that starts

    Application.StatusBar =

    has everything that’s shown, one per line. Just remove whichever lines you don’t want.

  6. Jon Peltier says:

    I’ve used a technique similar to Andy’s and John’s, with commandbars, on several occasions. Here’s an example that interested parties can download:

    http://PeltierTech.com/Excel/Charts/chartvba.html#ChtSizeMenu

    - Jon

  7. Emily says:

    Hi,

    Can you show me an example to call this class?
    Thanks
    Emily

  8. Charlie says:

    Hi Dick,

    Possibly a bit more error handling needed – it seems to crash when it encouters #N/A.

    Charlie

  9. Michael says:

    I discovered your web site about a month ago and read it daily.

    I’m just learning VBA and find this script potentially very useful. Please excuse my ignorance, but how would I copy this script into my file to use it? A step-by-step procedure for a newbie would be greatly appreciated.

  10. Matt H says:

    Michael,

    In your project (say Personal.xls or wherever you’re putting your code), in the VB Editor, choose Insert > Class Module. This will probably go in as “Class1? and you should see it in the directory tree of the Project Explorer at left. Paste Dick’s code in there. Above it, type:

    Public WithEvents mApp As Application<

    Now, in any *regular* module, type:

    Public x As New Class1

    Sub InitializeApp()
    Set x.mApp = Application
    End Sub

    You have to run this sub to turn on event trapping for the Application Object. Of course, you might want to name your variables, and class module something more useful…

    Also, check out “Using Events with the Application Object” in the VBA help. Or pick up one of John Walkenbach’s “Power Programming” books, which are a pretty non-intimidating way to really get up to speed.

    Cheers,
    Matt H

  11. Matt H says:

    Ha… Dick, I guess that the comments are limited in length. (?) Where’d the rest of my comment go?

    Continued:

    In a regular module, add the following code:

    Public x As New Class1

    Sub InitializeApp()
    Set x.mApp = Application
    End Sub

    You need to run this sub in order to turn on event handling for the application object (e.g. the SelectionChange event.) Of course, you may want to name your modules and variables something more meaningful.

    Also, check out “Using Events with the Application Object” in the VBA help. Or pick up a copy of one of John Walkenbach’s “Power Programming” books, which are a non-intimidating way to get up to speed.

    Cheers,
    Matt

  12. Greg says:

    Good idea – I’ve wished for this ever since seeing it in a version (don’t remember which one) of Quattro Pro.

    I’m new to the site – I just came across it searching for help on another problem. I can tell it’s going to be a good resource.

    –Greg

  13. scott says:

    I have followed the instructions, and added this code into a class module within my worksheet.
    I can’t get the status-bar at the bottom to change to show each of the values (sum, average, min, max etc).

    Am I doing something stupid ? or not doing something which I should ?

    Sorry, but I am new to advanced VBA and any help gratefully received.

    Thanks

    Scott.

  14. scott says:

    Sorry, ignore my last comment. Its working !

    Thanks

    Scott

  15. Julie says:

    This is great! The only thing that I would like to change (but I’m not sure how) is the formatting of the results that appear on the taskbar.

    Is there a way to round the average to 2 decimal places and add commas to the larger numers (i.e. 1,000,000 instead of 1000000)?

    I’m definitely a beginner at writing Macros, so I have no idea how I would accomplish this. Any help would be greatly appreciated!

    Thanks!

    Julie

  16. Julie: You could wrap all the results in a

    Format

    function. For example, you could format the average like

    Application.StatusBar = “Average: “ &amp; Format(CStr(vAvg), “#,##0.00″) &amp; ” | “ &amp; _
  17. mike cave says:

    Hey guys do you know i i can add a custom function to this, is i called my function “CountErrors”???

    Thanks!

    Mike

  18. pushpender says:

    Hi
    I tried this eaxtly but could not toggle status bar with different outputs? Am i doing something stupid?

  19. Pushpender says:

    Sorry guys.. Its working now. I did a mistake earlier in copy pasting the code.
    Thanks to the code writer/s.

  20. Bcole says:

    This is great! I have been attempting a version for myself, but was unable to get other excel Status Bar messages to come through. Well done.
    I loaded a simple Run macro in the ‘ThisWorkbook’ Object of my personal file and it starts the calc on open:
    Private Sub Workbook_Open()
    Run “InitializeApp()”
    End Sub
    Thanks again,
    bcole

  21. Bcole says:

    One more thing.
    I was having issues with Autofilter and correct calculation.
    I added a line redefining Target as only Visible cells and test great so far.
    Insert before Calculations (^ = existing row to show code line reference):

    ^ ‘Caclulate stats
    ‘ Target redefined to select only visible cells
    Set Target = Target.SpecialCells(xlVisible)

    ^ vAvg = Application.Average(Target)
    ^…

    Please post back if this doesn’t work for you.

    bcole

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

You must be logged in to post a comment.

Here's how to update your reports of company and nearly any web data: