Application.Min - how to ignore zero value?

[deko]

How do I determine the lowest non-zero value in a range?

This works if the lowest value is NOT zero:

dblMin = xlapp.Min(xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Range(xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Cells(2, 4), xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Cells(lr, lc)))
[lr = last row, lc = last column]

Is there some way to ignore zero values?

 

[Bob Phillips]

Here is an example

Debug.Print Evaluate("MIN(IF(A1:A10<>0,A1:A10))")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[deko]

Debug.Print Evaluate("MIN(IF(A1:A10<>0,A1:A10))")

Correct me if I'm wrong, but I believe this is saying: "If there is no zero
value in range A1:A10, then return the minimum value in the range". But
what if there *is* a zero value? How then would I get the minimum non-zero
value in the range?

In Access VBA, I think it would look like this:

MinVal = DMin("fldName", "tblName", "fldName > 0")

But all I have is a range in an Excel worksheet.

Perhaps I could assign the min value in the range to a variable, then test
the variable and get the average:

If MinVal = 0 Then
dblMin = xlapp.Average(myRange)
End if

Then increment down until 0

Do While MinVal > 0
MinVal = MinVal - x
Loop

But this could be inefficient - especially if I don't know how much to
increment down with each loop. Perhaps I could guess somehow by getting the
max and/or the standard deviation? The reason I need the min value is to
set the Y-axis on a chart - so it does not have to be absolutely precise,
but it does need to be pretty close.

Other ideas?

Thanks for the help.

 

[Bob Phillips]

No it is saying take the minimum of all values in the rfange that are not
zero.

Why don't you try it?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[deko]

No it is saying take the minimum of all values in the rfange that are not

zero.

I see. I should know by now not to second guess you...

Debug.Print Evaluate("MIN(IF(xlapp. _
Workbooks(strXlsFile).Worksheets(sn(i)). _
Range(xlapp.Workbooks(strXlsFile). _
Worksheets(sn(i)).Cells(2, 4), xlapp. _
Workbooks(strXlsFile).Worksheets(sn(i)). _
Cells(lr, lc))) <> 0, xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Range(xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Cells(2, 4), xlapp. _
Workbooks(strXlsFile).Worksheets(sn(i)).Cells(lr, lc))))")

Result: Error 2015

Is this caused by the input string being too long?

 

[Tushar Mehta]

Yes, Evaluate does have limitations, the length of the string being one
of them. However, in this case, it is something else.

Evaluate argument must be an XL formula. XL doesn't understand any
Workbooks mumbo-jumbo, which is VBA-speak. You need to convert your
range into a XL-type range reference, i.e.,
MIN(IF('[Book1]Sheet 1'!$A$1:$A$16<>0,'[Book1]Sheet 1'!$A$1:$A$16))

Use something like the untested:

dim RngAddr as string
....
with xlapp.workbooks(strxlsfile).worksheets(sn(i))
rngaddr="'[" & .parent.name & "]" & .name & "'!"
RngAddr=rngaddr & .Range(.Cells(2, 4),.Cells(lr, lc))).address
end with
debug.print xlapp.evaluate( _
"MIN(IF(" & rngaddr & "<>0," & rngaddr & "))")
....

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[deko]

Yes, Evaluate does have limitations, the length of the string being one

of them. However, in this case, it is something else.

Evaluate argument must be an XL formula. XL doesn't understand any
Workbooks mumbo-jumbo, which is VBA-speak. You need to convert your
range into a XL-type range reference, i.e.,
MIN(IF('[Book1]Sheet 1'!$A$1:$A$16<>0,'[Book1]Sheet 1'!$A$1:$A$16))

Great! That did the trick. Here's the working code:

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
strRange = "'[" & .Parent.Name & "]" & .Name & "'!" & _
.Range(.Cells(2, 4), .Cells(lr, lc)).Address
End With
dblMin = xlapp.Evaluate("MIN(IF(" & strRange & " <> 0, " & _
strRange & "))")
Debug.Print dblMin

Out of curiosity, is there any advantage of referencing ranges one way over
another? When is it better to use a string rather than something like:

xlapp.Max(xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
(xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells(2, 4), _
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells(lr, lc))

Is it just a matter of what the code needs to do?

Thanks for the help.

 

[Bob Phillips]

Better to set it up in a string, makes debugging tons easier.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Yes, Evaluate does have limitations, the length of the string being one
of them. However, in this case, it is something else.

Evaluate argument must be an XL formula. XL doesn't understand any
Workbooks mumbo-jumbo, which is VBA-speak. You need to convert your
range into a XL-type range reference, i.e.,
MIN(IF('[Book1]Sheet 1'!$A$1:$A$16<>0,'[Book1]Sheet 1'!$A$1:$A$16))

Great! That did the trick. Here's the working code:

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
strRange = "'[" & .Parent.Name & "]" & .Name & "'!" & _
.Range(.Cells(2, 4), .Cells(lr, lc)).Address
End With
dblMin = xlapp.Evaluate("MIN(IF(" & strRange & " <> 0, " & _
strRange & "))")
Debug.Print dblMin

Out of curiosity, is there any advantage of referencing ranges one way over
another? When is it better to use a string rather than something like:

xlapp.Max(xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
(xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells(2, 4), _
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells(lr, lc))

Is it just a matter of what the code needs to do?

Thanks for the help.