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:


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) & ” | “
            ‘Return control of statusbar
           Application.StatusBar = False
        End If
        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.


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.

Posted in Uncategorized

21 thoughts on “Selection Stats

  1. 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. 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.

  3. Stacie: The line that starts

    Application.StatusBar =

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

  4. Hi Dick,

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


  5. 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.

  6. 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.

    Matt H

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


    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.


  8. 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.


  9. 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.



  10. 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!



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


    function. For example, you could format the average like

    Application.StatusBar = “Average: “ &amp; Format(CStr(vAvg), “#,##0.00”) &amp; ” | “ &amp; _
  12. Hey guys do you know i i can add a custom function to this, is i called my function “CountErrors”???



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

  14. 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,

  15. 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.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.