Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 97
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 690
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 66
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 690
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calling functions from Excel worksheets and the Find function in XL '97 Mike-hime Excel Programming 1 January 8th 04 04:17 AM
Calling functions from dll T.K Kullervo Excel Programming 0 September 29th 03 09:55 AM
Calling Add-in functions from VBA Mark[_21_] Excel Programming 5 September 12th 03 08:49 PM
Calling certain functions within VBA james Excel Programming 0 August 11th 03 10:27 PM
Calling VBA functions from a xll Kim[_8_] Excel Programming 2 August 3rd 03 11:48 AM


All times are GMT +1. The time now is 07:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"