Visual Studio Tools for Office - CountIf, Ctrl-Shift-Enter, Array

[Bob Sullentrup]

Folks,

I want to have a function return the number of distinct entries in a column
in Excel using VSTO.

Turns out, 'CountIf' is a marvelously clever function for doing this with
one caveat -- that you have to signal to the 'Sum' function to mark it as an
array function.

Suppose your list is 'Apples', 'Apples', 'Pears', 'Grapes', 'Grapes',
'Grapes' in the range A1:A6.

=CountIf(A1:A6, "Grapes") returns 3 since 'Grapes' occurs three times.

Consider this though:

=Sum(1/CountIf(A1:A6, A1:A6))

CountIf does the same thing but operates on each cell in the list A1:A6
since the second parameter is a range, not the single value 'Grapes'. So it
returns an array {2,2,1,3,3,3} indicating the list has two 'Apples', then one
'Pear' then three 'Grapes'.

Taking reciprocals, we get {1/2, 1/2, 1, 1/3, 1/3, 1/3}.

When we sum the array, we get 3, the number of distinct entries in the list.

THE TRICK IS TO ENTER THE FUNCTION INTO A CELL

=Sum(1/CountIf(A1:A5, A1:A5))

not with 'Enter', but with Ctrl-Shift-Enter to mark it as an array function.

In VSTO, we can readily execute certain Excel functions using
'ThisApplication.WorksheetFunction'. It looks to me as if
'ThisApplication.WorksheetFunction.CountIf' does not know how to process
its second parameter as a range and thus an array. Equivalently, there is
no counterpart of the Ctrl-Shift-Enter functionality in the code behind that
exists in native Excel.

Any clues?

Thanks, Bob Sullentrup
--
Bob Sullentrup

[Simon Murphy]

Bob

If you want to use countif as if it were array entered into a cell, but dont have it in a cell then use application.evaluate("countif formula as a string") as in

Application.Evaluate("=SUM(1/COUNTIF(A1:A6, A1:A6))")


Cheers
Simon

[Bob Sullentrup]

Simon,

This may turn the trick, but it may not.

It's not just a matter of evaluating a formula, but to evaluate an array
formula.

I believe there are two properties, Formula and FormulaArray, that are
associated with a cell. When you hit '(Enter)', the expression gets assigned
to the Formula property. When Ctrl-Shift-Enter, to the latter.

I'll experiment with this and let the list know.


--
Bob Sullentrup


"Simon Murphy" wrote:

>
> Bob
>
> If you want to use countif as if it were array entered into a cell, but
> dont have it in a cell then use application.evaluate("countif formula as
> a string") as in
>
> Application.Evaluate("=SUM(1/COUNTIF(A1:A6, A1:A6))")
>
>
> Cheers
> Simon
>
>
> --
> Simon Murphy
> ------------------------------------------------------------------------
> Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
> View this thread: http://www.excelforum.com/showthread...hreadid=471231
>
>

[Simon Murphy]

Bob
App.evaluate uses array evaluation rules thats why I suggested it.

Are you saying you want to enter this in a cell? If so why are you bothering with VSTO?
If you are writing a user defined function you can code it how you want so you don't need array evaluation, to do that you need to create an automation add-in not a VSTO project.

Not totally clear what you are after, if this doesn't help you post back more info on what you are doing.
Cheers
Simon

[Bob Sullentrup]

Simon,

<App.evaluate uses array evaluation rules>

I did not know that.

That's sufficient, then, for my purposes. Many thanks!


--
Bob Sullentrup


"Simon Murphy" wrote:

>
> Bob
> App.evaluate uses array evaluation rules thats why I suggested it.
>
> Are you saying you want to enter this in a cell? If so why are you
> bothering with VSTO?
> If you are writing a user defined function you can code it how you want
> so you don't need array evaluation, to do that you need to create an
> automation add-in not a VSTO project.
>
> Not totally clear what you are after, if this doesn't help you post
> back more info on what you are doing.
> Cheers
> Simon
>
>
> --
> Simon Murphy
> ------------------------------------------------------------------------
> Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
> View this thread: http://www.excelforum.com/showthread...hreadid=471231
>
>

[Bob Sullentrup]

Simon,

Yup, your suggestion works slicker than snot on a doorknob!


--
Bob Sullentrup


"Bob Sullentrup" wrote:

> Simon,
>
> <App.evaluate uses array evaluation rules>
>
> I did not know that.
>
> That's sufficient, then, for my purposes. Many thanks!
>
>
> --
> Bob Sullentrup
>
>
> "Simon Murphy" wrote:
>
> >
> > Bob
> > App.evaluate uses array evaluation rules thats why I suggested it.
> >
> > Are you saying you want to enter this in a cell? If so why are you
> > bothering with VSTO?
> > If you are writing a user defined function you can code it how you want
> > so you don't need array evaluation, to do that you need to create an
> > automation add-in not a VSTO project.
> >
> > Not totally clear what you are after, if this doesn't help you post
> > back more info on what you are doing.
> > Cheers
> > Simon
> >
> >
> > --
> > Simon Murphy
> > ------------------------------------------------------------------------
> > Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
> > View this thread: http://www.excelforum.com/showthread...hreadid=471231
> >
> >

[Simon Murphy]

Bob
Post back if it doesn't do what you need
cheers
Simon