Results 1 to 12 of 12

20030214, 06:25 #1
 Join Date
 Feb 2001
 Posts
 369
 Thanks
 2
 Thanked 1 Time in 1 Post
Custom VBA funtions in array formulae (2000SP3)
After struggling with an array formula to test the truth of a condition in one column, and of one of two conditions in two other columns, I eventually got this to work:
=SUM(('Full PL list'!H2:H10="HEP1CE")*(('Full PL list'!AD2:AD10="HEP3CE")+('Full PL list'!AJ2:AJ10="HEP2CE")))
Checking identity is not appropriate, however, and with even more struggling (and thanks to an old post by a Mr Bernie Deitrick in the ms public excel misc ng), I eventually converted my simple VBA function:
Public Function WLike(strComparator as String, strComparee as String)
If strComparator Like strComparee then WLike=True else Wlike=False
End Function
into a function that can cope with an array input:
Function WLike2(strComparee As Variant, strComparator As Variant) As Variant
Dim i As Integer
Dim blResult() As Byte
Dim bdRows As Integer
bdRows = Application.Caller.Rows.Count
ReDim blResult(1 To strComparee.Count)
For i = 1 To strComparee.Count
If strComparee(i) Like strComparator Then blResult(i) = 1 Else blResult(i) = 0
Next i
If bdRows = 1 Then
WLike2 = blResult
Else
WLike2 = Application.Transpose(blResult)
End If
End Function
However, whilst this works as expected:
=SUM((WLike2('Full PL list'!H2:H10,"*P#?*"))*(WLike2('Full PL list'!AJ2:AJ10,"*P#?*")))
this doesn't (returning #N/A):
=SUM(((WLike2('Full PL list'!H2:H10,"*P#?*"))*((WLike2('Full PL list'!A2:AD10,"*P#?*"))+(WLike2('Full PL list'!AJ2:AJ10,"*P#?*")))))
Can anyone help me understand why? (I've tried playing around with brackets and between boolean and integer values being returned from WLike2).
Thanks
Mark

20030214, 07:01 #2
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Custom VBA funtions in array formulae (2000SP3)
Could you upload a workbook with a working example?
My hunch is that the dimensions of the returned array are not what XL expects in the array formula.
BTW: the SEARCH worksheet function also accepts wildcards. Not the same as LIKE, but it might just fit your purpose?Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030214, 07:26 #3
 Join Date
 Feb 2001
 Posts
 369
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Custom VBA funtions in array formulae (2000SP3)
Sorry, a bit rough and ready  I've cut and anonymised the first 10 rows. Cell B6 contains the formula that won't work, B7 the insufficient formula that does work (although not in the right place on the grid), and B4 the two condition only example that does work.

20030214, 09:18 #4
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Custom VBA funtions in array formulae (2000SP3)
Your formula contains a simple typo. This should be it:
=SUM(((WLike2('Full PL list'!H2:H10,"*P#?*"))*((WLike2('Full PL list'!AD2:AD10,"*P#?*"))+(WLike2('Full PL list'!AJ2:AJ10,"*P#?*")))))
Up to you to find the typo <bg>Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030216, 18:07 #5
 Join Date
 Feb 2001
 Posts
 369
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Custom VBA funtions in array formulae (2000SP3)
Doh! Now I feel stupid. Excel didn't moan at me about the formula, so I thought it would be OK  although you've no idea how many times I checked the brackets. As it was an array formula and I don't really understand them too well I wasn't all that surprised when there was a problem!! I guess I've just demonstrated the Excel equivalent of the "but spell check didn't find any errors" phenomenon of which I'm usually so scornful. Maybe I should be more tolerant.
Thanks anyway, and sorry for wasting your time.
Just for my interest, I didn't really understand the application.transpose bit of the function, and it's relationship with application.caller.rows.count. I'm also unclear as to exactly how the variant arrays are handled. If I pass a range of 3rows by 4 cols as strComparee for instance, would strComparee.count be 12? None of these things seem to be particularly well documented in online help, and as I do most of my work in Access, I've not got round to getting a good Excel book.

20030217, 06:41 #6
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Custom VBA funtions in array formulae (2000SP3)
As far as I can see with some testing, The result will be 12. The cells passed to the function are stepped through from left to right, rowbyrow. But a single array is returned, which is in column order, whereas a standard onedimensional array in a worksheet array formula is a column of cells, hence the transpose.
For more informatiion, check out this site.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030222, 11:07 #7
 Join Date
 Feb 2001
 Posts
 369
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Custom VBA funtions in array formulae (2000SP3)
Sorry, I'm feeling really stupid, but I'm having more problems with this. I've appended it to this thread to keep it in context.
This formula correctly returns 2 (where AM and AV don't contain a suitable string):
=((wlike2('Full PL list'!AM4,"*P#?*")=0)+(wlike2('Full PL list'!AV4,"*P#?*")=0))
Developing this formula to this correctly returns true
=((((wlike2('Full PL list'!AM4,"*P#?*")=0)+(wlike2('Full PL list'!AV4,"*P#?*")=0))('Full PL list'!BT4))=0)
Developing again to an array formula:
=SUM(((((wlike2('Full PL list'!AM2:AM4,"*P#?*")=0)+(wlike2('Full PL list'!AV2:AV4,"*P#?*")=0))('Full PL list'!BT2:BT4))=0))
covering 3 rows with values in the relevant columns of:
AM AV BT
2 blank blank 2
3 HEP1CE blank 0
4 blank blank 2
returns 0, when I would expect it to return 2. I've been through this one with a fine toothcomb and rebuilt it several times to make sure it's not a typo this time.
I don't understand.

20030222, 13:19 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Custom VBA funtions in array formulae (2000SP3)
Why not the array formula:
=SUM(IF(WLike2('Full PL list'!AM2:AM4,"*P#?*")+WLike2('Full PL list'!AV2:AV4,"*P#?*"),0,1))
This will count the entries that do NOT have the matching entries of "*P#?*" in AV or AM. I didn't understand where col BT was coming from, it seemed to be just a number you had to put in to subtract to get the right sum, but wasn't really needed for the formula to work and would have to be an extra column.
If I'm wrong could you clarify what this is and how it works?
Steve

20030223, 04:05 #9
 Join Date
 Feb 2001
 Posts
 369
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Custom VBA funtions in array formulae (2000SP3)
Sorry, I should have attached an example to be clearer. BT is a count of names (in either of 2 columns per sample type) without identified samples. As these values are used a lot, and including them in every formula becomes cumbersome, I've made them into a column on the datasheet. So my formula literally reads "sum of rows where empty sample boxes equals number of names without samples", which equates to number of rows where all samples in that category are missing, without the need to check again in both names columns. Hope the attachment makes it clear.
BTW, I tried this:
=SUM(IF((((wlike2('Full PL list'!AM2:AM120,"*P#?*")=0)+(wlike2('Full PL list'!AV2:AV120,"*P#?*")=0))=('Full PL list'!BT2:BT120)),1,0))
and it gave me the answer 5702. The anticipated answer is 12, and the number of rows with any nonblank entries in any of the columns is 120.

20030223, 12:55 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Custom VBA funtions in array formulae (2000SP3)
I am not completely clear on what you are doing exactly. The problem I see is that your formulas do NOT give you the arrays you anticipate and that gives you the wrong values:
Does this work?
=sum(IF((((wlike2('Full PL list'!AM2:AM120,"*P#?*")=0)+(wlike2('Full PL list'!AV2:AV120,"*P#?*")=0))=transpose('Full PL list'!BT2:BT120)),1,0))
Steve

20030226, 06:57 #11
 Join Date
 Feb 2001
 Posts
 369
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Custom VBA funtions in array formulae (2000SP3)
Ah! After a number of false starts and your post I now realise that the main problem was with the transposition, which I'll admit I'd been struggling with before, as it doesn't appear in VB help (but the worksheet function transpose does appear in excel help, and I assume they do the same thing). The function I copied did have some allowance for this, but every time I call it, application.caller.rows.count always returns 1, no matter what the range specified in the array formula is. Is that right?

20030226, 11:27 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Custom VBA funtions in array formulae (2000SP3)
Don't completely understand your question on application.caller.rows.count.
In a function the Application.caller should yield the range that called the function. This range is the one cell that has the formula in it so this range has a rowcount of 1.
Steve