excel function within a UDF

[caerdydd]

Hello all,
Grateful for any advice on the following...I have a UDF 'GetHighPoint' which goes to an internal database and pulls back a list of data (results stored in high = Qry1.Data) and from this list the excel function MAX will find the highest value, thus the spreadsheet only sees one value(i.e the highest number)
My code is below but always brings back #Value in excel.
Any ideas as to why this keeps happening?
I think my references to the UDF variables is incorrect but not sure how they should be formatted.
Thanks for the replies.


Function GetHighPoint(id As Variant, StartDate As Date, EndDate As Date) As Variant

Dim high()
Dim i As Integer
Set Qry1 = New Qry
With Qry1
.ErrorMode = EXCEPTION
.InstrumentIDList = [id].Value
.FieldList = "HIGH"
.Mode = "START:[STARTDATE].value END:[ENDDATE].value"
.RequestAll

high = Qry1.Data
End With
ActiveCell.FormulaR1C1 = "=MAX([high].value)

 

[DaleWatson123321]

I'm not sure if this will work with your function, but it's worth a try...

instead of: ActiveCell.FormulaR1C1 = "=MAX([high].value)", try...

high = Application.WorksheetFunction.Max(high)

If the above works, it should be WITHIN your UDF - i.e. i believe the following needs to be placed OUTSIDE your UDF...

ActiveCell.Value = high

I hope this helps.

Regards, Dale Watson

 

[caerdydd]

Dale,
Thanks very much for the reply,
I incorporated your suggestion but gives me an error now on the variable 'High' saying... 'Compile Error, Cannot assign to an array'
Anyone have any ideas how to troubleshoot this one.
Thanks in advance

 

[TonyJollans]

Hi caerdydd,

I think you need another variable, try ..

[blue][tt] highmax = Application.WorksheetFunction.Max(high)[/tt][/blue]

and

[blue][tt] ActiveCell.Value = highmax[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[caerdydd]

Eventually got this working.........

Function GetHighPoint(id As Variant, StartDate As Date, EndDate As Date) As Variant

For i = LBound(thehigh) To UBound(thehigh)
Next i

GetHighPoint = Application.WorksheetFunction.Max(thehigh)

End Function