If I select a number of cells in Excel, I can see some functions on those in the Customized status bar. This information is my own selection out of the following:
- Average
- Count
- Numerical count
- Minimum
- Maximum
- Sum
I want to add to this list a function that calculates the ratio of non-blank cells. The function would be =COUNTA(range) / (COUNTA(range) + COUNTBLANK(range)), but how can I get that into the status bar?
I don't have much experience in VBA, so some explanation would be welcome.
Best Answer
Try something like this (Not the best way to do it though but solves your purpose)
Explanation: What this code does is checks if the user has selected a valid range (of minimum 2 cells) and then uses the
Application.Evaluate
to calculate your formula and then display it in the status bar. Also note that I have not done any error handling. I am sure you will take care of it :)Paste this in the relevant sheet code area.
This is a very basic way of doing it. In case you want it applicable for all sheets then you will have to amend it accordingly.
Also the problem with this method is that it will suppress the application level messages in the status bar.
Screenshot