Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to use standard excel functions in vb
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to use standard excel functions in vb
Mark,
Please try: Application.WorkbookFunction.Max(...) Bill "Mark" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to use standard excel functions in vb
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 :-). "Bill James" wrote: Mark, Please try: Application.WorkbookFunction.Max(...) Bill "Mark" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to use standard excel functions in vb
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 "Mark" wrote in message ... 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 :-). "Bill James" wrote: Mark, Please try: Application.WorkbookFunction.Max(...) Bill "Mark" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Standard Colours | Excel Discussion (Misc queries) | |||
Three new functions that should be part of your standard set | Excel Worksheet Functions | |||
Can you set up Excel to automatically add a standard footer? | Excel Discussion (Misc queries) | |||
Standard deviations in Excel | Charts and Charting in Excel | |||
excel functions and User defined functions | Excel Programming |