Dynamic DMax criteria in VBA

[James Neumann]

All,

I am having some difficulty dynamically constructing criteria for the
where clause of the DMax function. I can make it return the correct
value with a range as the criteria argument, as below:

'******************************************
Sub TestDMax_1()

Dim vntResult As Variant

Dim rngSource As Range
Dim rngCriteria As Range

Set rngSource = ThisWorkbook.Worksheets(1).Range("A5:B11")
Set rngCriteria = ThisWorkbook.Worksheets(1).Range("A1:A3")

vntResult = WorksheetFunction.DMax(rngSource, 2, rngCriteria)

Set rngSource = Nothing
Set rngCriteria = Nothing

End Sub
'******************************************

But, when I try to assign the criteria dynamically, as below, I get
the error message "'1004' - unable to get the DMax property of
worksheet function."

'******************************************
Sub TestDMax_3()

Dim vntResult As Variant

Dim rngSource As Range

Set rngSource = ThisWorkbook.Worksheets(1).Range("A5:B11")

vntResult = WorksheetFunction.DMax(rngSource, 2, "[Tree] =
'Apple'")

Set rngSource = Nothing

End Sub
'******************************************

I supect that I'm doing something wrong when I construct the string,
but I have played around with it quite a bit, using parentheses, hash
marks, etc. to no avail. I couldn't find any references for the where
syntax, other than using a cell range. I've done this in Access
before - what am I missing/botching here?

For reference, the data sheet is modified from the Excel 2000 help
example:

A1: Tree
A2: Apple

A5: Tree
B5: Profit
A6: Apple
B6: 105
A7: Pear
B7: 96
A8: Cherry
B8: 105
A9: Apple
B9: 75
A10: Pear
B10: 76.8
A11: Apple
B11: 45

Thanks in Advance