Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    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

  2. #2
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    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.
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    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.

  6. #6
    Platinum Lounger
    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, row-by-row. But a single array is returned, which is in column order, whereas a standard one-dimensional 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.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    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.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    3 Star Lounger
    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.
    Attached Files Attached Files

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

  11. #11
    3 Star Lounger
    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?

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •