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:
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.
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.
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.
I also have a variation on that theme in my PUP add-in. The info is displayed in a toolbar. Click here.
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.
What modifications would be necessary to show only two options, say sum and count?
Stacie
Stacie: The line that starts
has everything that’s shown, one per line. Just remove whichever lines you don’t want.
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
Hi,
Can you show me an example to call this class?
Thanks
Emily
Hi Dick,
Possibly a bit more error handling needed – it seems to crash when it encouters #N/A.
Charlie
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.
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
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
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
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.
Sorry, ignore my last comment. Its working !
Thanks
Scott
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
Julie: You could wrap all the results in a
function. For example, you could format the average like
Hey guys do you know i i can add a custom function to this, is i called my function “CountErrors”???
Thanks!
Mike
Hi
I tried this eaxtly but could not toggle status bar with different outputs? Am i doing something stupid?
Sorry guys.. Its working now. I did a mistake earlier in copy pasting the code.
Thanks to the code writer/s.
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
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