For instance, in a worksheet, one can execute
{=SMALL(A1:10,{1,2,3})}
Can a similar call be made in a VBA module? If so, what
is the syntax and how is the result returned?
--
Regards,
Tom Ogilvy
"JM" <anon...@discussions.microsoft.com> wrote in message
news:05ab01c3fd3a$edc691d0$a601...@phx.gbl...
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Tom Ogilvy" <twog...@msn.com> wrote in message
news:%23Tl%23pDU$DHA....@TK2MSFTNGP11.phx.gbl...
This doesn't seem to work in Excel 97 either. But the following does.
varr = Evaluate("INDEX(SMALL(A1:A10,{1,2,3}),0)")
Just another case of the wonders and mysteries of Excel's array semantics, which
Microsoft has never bothered to document in detail, perhaps because no one at
Microsoft knows what those semantics are either.
To be more precise, if I enter
=SMALL(A1:A10,{1,2,3})
as a nonarray formula in a cell, I get the same result as if I had entered the
array formula
=INDEX(SMALL(A1:A10),{1,2,3}),1)
in a cell. However, if I enter the nonarray formula
=IF(A1:A10>AVERAGE(A1:A10),A1:A10)
in any cell in B11:IV65536 (so implicit indexing doesn't kick in), this formula
returns #VALUE!. However, entering the same formula as an array formula in a
single cell gives the first entry from the array result.
Functions that invariably return #VALUE! when not entered as array formulas
return arrays when called from Evaluate. Functions that return nonerror values
when not entered as array formulas return only the first value when called from
Evaluate. AIN'T EXCEL SWELL?! Wanna make a bet blinking text makes it into Excel
as a cell format option before Microsoft adds any orthogonality to its array
semantics?
--
To top-post is human, to bottom-post and snip is sublime.
varr = Application.Small(Range("A1:A10"),Array(1,2,3))
? varr(1)
1
? varr(2)
2
? varr(3)
3
To the OP, in General, to handle most array functions, you use evaluate
which is like a virtual cell on the activesheet.
Regards,
Tom Ogilvy
"Bob Phillips" <bob.ph...@notheretiscali.co.uk> wrote in message
news:uBerHTV$DHA...@TK2MSFTNGP11.phx.gbl...
Dim v As Variant
v = [Transpose(Transpose(SMALL(A1:A10,{1,2,3})))]
HTH
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Harlan Grove" <hrl...@aol.com> wrote in message
news:_OL%b.4730$_4....@www.newsranger.com...
And the advantage of two TRANSPOSE calls vs one INDEX call is?
v = [Transpose(SMALL(A1:A10,ROW(1:3)))]
No advantage of course. :>) Just sharing an idea.
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Harlan Grove" <hrl...@aol.com> wrote in message
news:_db0c.4864$_4....@www.newsranger.com...