Excel guru Rob van Gelder asks a question which I will paraphrase: What Excel feature is at the top of your wish list? I’m sure everyone has something to say about this, so limit your comment to the one (or two) feature(s) that are at the top of the list.
At the top of Rob’s list is blinking cell’s (just kidding). As for me, my knee jerk reaction is more rows and columns. But when I have to pick just one, I’m not sure if that’s the most important. I definitely find myself needing more columns more often that more rows, but I can’t say that I hit this limit so often that it really bothers me. Most of the spreadsheets where more columns would have been handy are already written with appropriate workarounds. I don’t gnash my teeth every time I use them, just when I have to redesign them.
I would really like to see flexible rows and columns. Limit me to 17 million cells if you like, just let me determine how many rows and columns for each worksheet. One row with 17 million columns, one column with 17 million rows, or anything in between. That sounds pretty reasonable to me, but I can’t even spell C, much less program in it so I don’t know if it’s even feasible.
In VBA, there are two things that come to mind immediately: The ability to create all the commandbar control types that exist; and a real before close event. There’s only a handful of commandbar controls that are creatable in VBA, but I want them all. And anyone whose been bitten by the BeforeClose event on an unsaved workbook knows what I’m talking about there.
I should stick with my first instinct, so I’m going with more rows and columns. It sure would be nice to have, say, three columns for every day of the year plus a couple of extra. Now it’s your turn. What feature would you like to see?
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) & " | "
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.
