Create Function using Excel VBA
This seemed to work ok:
Option Explicit
Function MinPrice(ParamArray arglist() As Variant) As Variant 'text or number
Dim arg As Variant
Dim FoundANumber As Boolean
Dim myMin As Double
myMin = 100 ^ 100 'some really big number
FoundANumber = False
For Each arg In arglist
If IsNumeric(arg) Then
FoundANumber = True
myMin = WorksheetFunction.Min(myMin, arg)
End If
Next arg
If FoundANumber = True Then
MinPrice = myMin
Else
MinPrice = "Item Not Bid"
End If
End Function
Sub aa()
MsgBox MinPrice("a", 3, CVErr(xlErrNA))
End Sub
But maybe you could use an array formula:
=IF(COUNT(A1:A10)=0,"Item Not Bid",MIN(IF(ISNUMBER(A1:A10),A1:A10)))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
Bob K wrote:
I am trying to create a function that looks at each argument and does
validation of the argument to determine whether or not to include the
argument in the min function. The validation process should exclude
arguments that:
Argument = 0
Argument is text
Argument is an error
If the all the arguments fail the validation, it should return a text
message ie €śItem not bid€ť otherwise it should return the result of the min
value of the arguments that pass the validation process. Any help would be
much appreciated. I also want to create a function the works similar using
€śSmall€ť.
Thanks,
bob
Function MinPrice(ParamArray arglist() As Variant) As Double
For Each arg In arglist
MinPrice = WorksheetFunction.Min(arglist)
Next arg
End Function
--
Dave Peterson
|