I'm a bit confused as to how to use obtain, in VBA, the value which would be
returned by a worksheet function. On one of my sheets, in column 2, there is
a list of numbers. Suppose I wanted to know the highest value in this column
between rows 7 and 707 inclusive. I could obtain this by

Max (B7:B707)

Now suppose I ws in a VBA module and wanted this value. Can anyone tell me
what code statement(s) would allow me to put this value into variable. I
vaguely remember that this calls for the "application." syntax, but I have
tried to find the specifics and am stymied. Any help would be greatly
appreciated.


--
-regards

You are referring to Application.WorksheetFunction. Here is the example from
Excel Help:

Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox answer

Here is an example from a project I worked on:

Elem$ = vbNullString
Elem$ = Application.WorksheetFunction.VLookup(Me.cboCO_Exp Type.Value, _
ThisWorkbook.Sheets(CostLogicSht$).Columns("B:E"), 4, False)
If Elem$ < "" Then
Me.txtCO_CostElem.Value = Elem$
End If

Not all worksheet functions can be used with Application.WorksheetFunction.
See Excel Help for a list.

Hope this helps,

Hutch

"GeorgeJ" wrote:

I'm a bit confused as to how to use obtain, in VBA, the value which would be
returned by a worksheet function. On one of my sheets, in column 2, there is
a list of numbers. Suppose I wanted to know the highest value in this column
between rows 7 and 707 inclusive. I could obtain this by

Max (B7:B707)

Now suppose I ws in a VBA module and wanted this value. Can anyone tell me
what code statement(s) would allow me to put this value into variable. I
vaguely remember that this calls for the "application." syntax, but I have
tried to find the specifics and am stymied. Any help would be greatly
appreciated.


--
-regards

Tom, your post was just what the doctor ordered. I couldn't remember the
method "application.worksheetfunction", but as soon as I saw your post it
came back to me. Your example was helpful too. and your response was as
prompt as it was helpful. I don't know what I'd do without this forum.

-Regards

Why not just use [...] or Evaluate("...") ? What am I missing?

Thu, 2 Aug 2007 14:52:03 -0700 from Tom Hutchins
:
You are referring to Application.WorksheetFunction. Here is the example from
Excel Help:

Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox answer

Here is an example from a project I worked on:

Elem$ = vbNullString
Elem$ = Application.WorksheetFunction.VLookup(Me.cboCO_Exp Type.Value, _
ThisWorkbook.Sheets(CostLogicSht$).Columns("B:E"), 4, False)
If Elem$ < "" Then
Me.txtCO_CostElem.Value = Elem$
End If

Not all worksheet functions can be used with Application.WorksheetFunction.
See Excel Help for a list.

Hope this helps,

Hutch

"GeorgeJ" wrote:

I'm a bit confused as to how to use obtain, in VBA, the value which would be
returned by a worksheet function. On one of my sheets, in column 2, there is
a list of numbers. Suppose I wanted to know the highest value in this column
between rows 7 and 707 inclusive. I could obtain this by

Max (B7:B707)

Now suppose I ws in a VBA module and wanted this value. Can anyone tell me
what code statement(s) would allow me to put this value into variable. I
vaguely remember that this calls for the "application." syntax, but I have
tried to find the specifics and am stymied. Any help would be greatly
appreciated.


--
-regards


--
"The Internet is famously powered by the twin engines of
bitterness and contempt." -- Nathan Rabin, /The Onion/
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/