Minimum and Maximum value of a range

[UHsoccer]

I have dates in column C and interactively I can display the minimum, maximun, average ets by right clicking in the status bar and it displays the information in the Status bar

How can I duplicate those functions with a VB macro for use in my program

 

[hiker95]

UHsoccer,


See:

2007 Status Bar in Excel 2003: Showing multiple summary functions in Status Bar for Excel 2003
2007 Status Bar in Excel 2003
http://www.archivum.info/microsoft.public.excel.programming/2008-02/msg00782.html

 

[UHsoccer]

That link uses a formula.

I have seen reference to code like

Application.Maximum ......

 

[UHsoccer]

Re: Minimum and Maximum value of a range - Solved

Here is the code

dim maxDate as Date
maxDate = Application.Max(Sheets(sheetName).Range("A2:A100"))

 

[hiker95]

UHsoccer,

Sorry, I mis-understood your question.



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. And, paste the copied code into VBAProject, Microsoft Excel Objects, ThisWorkbook (on the right pane) by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.StatusBar = False
    If TypeName(Selection) = "Range" Then
        Workbook_SheetSelectionChange Sh, Selection
    End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim s As String
Dim wfn As WorksheetFunction
    Set wfn = Application.WorksheetFunction
    On Error GoTo errH:
    If wfn.Count(Target) < 2 Then
        s = ""
    Else
        s = "Sum=" & wfn.Sum(Target) & " " & _
            "Avg=" & wfn.Average(Target) & " " & _
            "Min=" & wfn.Min(Target) & " " & _
            "Max=" & wfn.Max(Target) ' etc
    End If
errH:
    Application.StatusBar = s
End Sub

Add several worksheets containing a series of numbers in a range. Then save the workbook.

Click on one of the sheets, and highlite a range of cells with numbers, and look on the left side of the Status Bar.