Find Min value in range with VBA?

I know this is pretty basic, but it's been a long night...

How do I find the minimum value in a range?

If I have Q2:Q12, and want to find the minimum value - via automation from
Access, would it look something like this:

dblMin = xlapp.Evaluate("MIN(Q2:Q12)") ??? same for Max ??

Thanks in advance.

deko,

This works:

Dim dblMin As Double
Dim xlApp As Object
Dim xlBook As Excel.Workbook
Dim xlSht As Excel.Worksheet

Set xlApp = CreateObject("excel.application")
Set xlBook = xlApp.Workbooks.Open("C:\Path\Filename.xls")
Set xlSht = xlBook.ActiveSheet

dblMin = xlApp.WorksheetFunction.Min(xlSht.Range("Q2:Q12"))
MsgBox dblMin

This requires a reference to your Excel application through Tools /
References (in the VBE).

HTH,
Bernie
MS Excel MVP


"deko" <[email protected]> wrote in message
news:[email protected]...
> I know this is pretty basic, but it's been a long night...
>
> How do I find the minimum value in a range?
>
> If I have Q2:Q12, and want to find the minimum value - via automation from
> Access, would it look something like this:
>
> dblMin = xlapp.Evaluate("MIN(Q2:Q12)") ??? same for Max ??
>
> Thanks in advance.
>
>

> This works:
>
> Dim dblMin As Double
> Dim xlApp As Object
> Dim xlBook As Excel.Workbook
> Dim xlSht As Excel.Worksheet
>
> Set xlApp = CreateObject("excel.application")
> Set xlBook = xlApp.Workbooks.Open("C:\Path\Filename.xls")
> Set xlSht = xlBook.ActiveSheet
>
> dblMin = xlApp.WorksheetFunction.Min(xlSht.Range("Q2:Q12"))
> MsgBox dblMin
>
> This requires a reference to your Excel application through Tools /
> References (in the VBE).

Thanks! That really helped. Here's what seems to be working for me (still
testing)...

dblMin =
xlapp.WorksheetFunction.Min(xlapp.Workbooks(strXlsFile).Worksheets(i).Range(
strRange))

That should more than seem to work - I just didn't know how you were
referencing the workbook and worksheet objects.

HTH,
Bernie
MS Excel MVP

> Thanks! That really helped. Here's what seems to be working for me
(still
> testing)...
>
> dblMin =
>
xlapp.WorksheetFunction.Min(xlapp.Workbooks(strXlsFile).Worksheets(i).Range(
> strRange))