Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Excel array functions in VBA
Is it possible to call an Excel array function in VBA?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Excel array functions in VBA
varr = Evaluate("SMALL(A1:10,{1,2,3})")
-- Regards, Tom Ogilvy "JM" wrote in message ... Is it possible to call an Excel array function in VBA? 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Excel array functions in VBA
I thought this, but it just gives me an error 2015.
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tom Ogilvy" wrote in message ... varr = Evaluate("SMALL(A1:10,{1,2,3})") -- Regards, Tom Ogilvy "JM" wrote in message ... Is it possible to call an Excel array function in VBA? 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Excel array functions in VBA
"Bob Phillips" wrote...
I thought this, but it just gives me an error 2015. ... "Tom Ogilvy" wrote in message varr = Evaluate("SMALL(A1:10,{1,2,3})") ... 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:A10AVERAGE(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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Excel array functions in VBA
Ok, I guess it doesn't need to be treated as an array funtion: A1:A10 has
the integers 1 to 10. 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" wrote in message ... I thought this, but it just gives me an error 2015. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tom Ogilvy" wrote in message ... varr = Evaluate("SMALL(A1:10,{1,2,3})") -- Regards, Tom Ogilvy "JM" wrote in message ... Is it possible to call an Excel array function in VBA? 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Excel array functions in VBA
Just a slightly different way from Tom's using Excel XP.
Dim v As Variant v = [Transpose(Transpose(SMALL(A1:A10,{1,2,3})))] HTH -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Harlan Grove" wrote in message ... "Bob Phillips" wrote... I thought this, but it just gives me an error 2015. .. "Tom Ogilvy" wrote in message varr = Evaluate("SMALL(A1:10,{1,2,3})") .. 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:A10AVERAGE(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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Excel array functions in VBA
"Dana DeLouis" wrote...
Just a slightly different way from Tom's using Excel XP. Dim v As Variant v = [Transpose(Transpose(SMALL(A1:A10,{1,2,3})))] .... And the advantage of two TRANSPOSE calls vs one INDEX call is? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Excel array functions in VBA
Hi. No advantage really. I like your "Index" version myself.
A one Transpose version could be something like this: 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" wrote in message ... "Dana DeLouis" wrote... Just a slightly different way from Tom's using Excel XP. Dim v As Variant v = [Transpose(Transpose(SMALL(A1:A10,{1,2,3})))] ... And the advantage of two TRANSPOSE calls vs one INDEX call is? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling functions from Excel worksheets and the Find function in XL '97 | Excel Programming | |||
Calling functions from dll | Excel Programming | |||
Calling Add-in functions from VBA | Excel Programming | |||
Calling certain functions within VBA | Excel Programming | |||
Calling VBA functions from a xll | Excel Programming |