Want to use standard excel functions in vb

[Guest]

I have a worksheet containing a list of items that ore of interest to me.
Periodically , I want to add to the list or modify one of the items in the
list.
Each item has an item ID in Col 1 and 'many' other bits of info (text,
numeric, dates) in subsequent columns

I built a user form in VB to pull info into the form from the indicated row
of the db and put it back into the DB as an indicated row.

In order to set the item ID of a new item, I want to get the maxvalue for
all current items.

I wanted to use the following code:

InitID.text = max(Worksheets("OppDB").cells(a1:a65535))

Where InitID is the name of the textbox in my form containing the Initiative
ID, and OppDB is the worksheet within the workbook that holds all data on the
set of initiatives I'm tracking.

I suppose I could do this in a loop, but, since excel has a function that
returns the max value found in a range (a1:aN) I thought this would work.
There are many other excel functions that I might want to use within VB --
what am I missing about how to do this (I know - the answer is probably
obvious).

TIA
Mark

 

[Bill James]

Mark,

Please try:

Application.WorkbookFunction.Max(...)

Bill

 

[Guest]

Thanks, Bill.

Here's the statement I used:

InitID.Text =
Application.WorkbookFunction.Max(Worksheets("OppDB").Cells(a1:a200))

I'm (still) getting a compile error ("Expected list separator or )" )with
the ":" highlited. Probably a bone head error .

 

[Mike Fogleman]

Dim myRange As Range
Set myRange = Worksheets("OppDB").Range("A1:A200")
InitID.Text = Application.WorksheetFunction.Max(myRange)

Do the WorksheetFunction on a variable
Mike F