Can I add my own function to the customized status bar in Excel?

Question

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.

Answer 1

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.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If TypeName(Target) = "Range" Then
        If Target.Cells.Count > 1 Then
            Application.StatusBar = "My Function: " & _
                                    Application.Evaluate( _
                                                          "=COUNTA(" & _
                                                           Target.Address & _
                                                           ") / (COUNTA(" & _
                                                           Target.Address & _
                                                           ") + COUNTBLANK(" & _
                                                           Target.Address & _
                                                           "))" _
                                                           )
        End If
    End If
End Sub

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