Using a worksheet function from VBA

[Guest]

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.

 

[Guest]

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_ExpType.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

 

[Guest]

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

 

[Stan Brown]

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

Thu, 2 Aug 2007 14:52:03 -0700 from Tom Hutchins