+ Reply to Thread
Results 1 to 14 of 14

How to resolve array formula in VBA

  1. #1
    Registered User
    Join Date
    07-11-2014
    Location
    Buenos Aires
    MS-Off Ver
    2010
    Posts
    16

    Exclamation How to resolve array formula in VBA

    Hi...

    I have this PERCENTILE formula which I need to implement in VBA

    =PERCENTILE(IF('RAW C TIME'!$D$2:$D$51816=$J7,IF('RAW C TIME'!$J$2:$J$51816=$K$4,IF($K$3="(All)",'RAW C TIME'!$I$2:$I$51816,IF('RAW C TIME'!$G$2:$G$51816=$K$3,'RAW C TIME'!$I$2:$I$51816)))),N$6)

    The idea is to pass all the ranges and values as parameters for a custom Function.

    I already accomplished this and it is working as expected. The only problem I have is that in some cases there are too many parameters, and since I am using Evaluate to run the function and then return the result, if the function gets too long (more than 255 characters) then Evaluate fails...

    So... I am wondering if there is a way to resolve first all the "IF's" so I can just pass the range or array needed for the percentile... Is there a way in VBA to resolve the nested if as it is in the function?

    can anyone tell me where I can find documentation that will guide me to accomplish this?

    Thanks

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: How to resolve array formula in VBA

    Try making your range to pass to the percentile function:

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    07-11-2014
    Location
    Buenos Aires
    MS-Off Ver
    2010
    Posts
    16

    Re: How to resolve array formula in VBA

    Thank you Bernie! I will give it a try

  4. #4
    Registered User
    Join Date
    07-11-2014
    Location
    Buenos Aires
    MS-Off Ver
    2010
    Posts
    16

    Re: How to resolve array formula in VBA

    Thanks a lot Bernie... the code does exactly what I was looking for... Just one more question.
    Suppose that C3 is a multiple-select list... It's in fact a "report filter" field to filter by year (this is a pivot table). I know how to find out which values are selected (PivotItems set to "visible")... but then, how should I look for multiple values in that range? Maybe create another variable YEARS , concatenate all the selected values and then search see if R3.Cells(lngR) is contained in YEARS? Does that make sense?

    Thanks a lot...

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: How to resolve array formula in VBA

    I would use code to create a pivotitems array, and then loop through that as well:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    07-11-2014
    Location
    Buenos Aires
    MS-Off Ver
    2010
    Posts
    16

    Re: How to resolve array formula in VBA

    Thanks for taking time to help with this...

    Here is what I did, and seems to be working

    Please Login or Register  to view this content.
    You think this approach could cause any issues?

  7. #7
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: How to resolve array formula in VBA

    As long as you thoroughly test it and can control what values are added, no, I don't see an issue. Searching a longer string for a substring can be a problem if parts of two substrings, when run together, form an unintended third string. I would add in a delimiter that is not an legal part of any substring, if that were the case:

    For i = 1 To C3.PivotField.PivotItems.Count
    If C3.PivotField.PivotItems(i).Visible = True Then
    years = years & ";" & C3.PivotField.PivotItems(i).Value
    End If
    Next i

    Then the concatenation problem would not be an issue.

    Personally, I also prefer not to use + for concatenation, because if the value is a number, you may get addition and not concatenation if you are not careful with your declarations and use a variant and not a string, so I stick with &.

  8. #8
    Registered User
    Join Date
    07-11-2014
    Location
    Buenos Aires
    MS-Off Ver
    2010
    Posts
    16

    Re: How to resolve array formula in VBA

    Thinking about that I had added a pipe in between the values in a first attempt, but then removed it thinking it was not necessary... but I will take your considerations into account.
    One last question before I close this one and send you a bottle of wine

    I notice that filtering the values and creating the range for the percentile function this way causes the table to take about 15 to 20 seconds to update every time I change a filter...

    Being a total newbie, I was wondering if there is a way to solve the IF's block using something similiar to the Evaluate method... and then pass the resulting array to the percentile function... Is that possible to do?

    Thanks again

  9. #9
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: How to resolve array formula in VBA

    I'm not aware of a way to do that involving just arrays without stepping through.

    I think the best way to use worksheet functions can often be.... to use the worksheet. Write the function to a cell, calculate, and read the value, then clear the cell.

  10. #10
    Registered User
    Join Date
    07-11-2014
    Location
    Buenos Aires
    MS-Off Ver
    2010
    Posts
    16

    Re: How to resolve array formula in VBA

    Hi Bernie!

    Thanks for your comments... Just came back to show you how I managed to solve this

    I was able to run the IF's block first, and then work with the resulting array to create a second array to pass the values to the percentile function

    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: How to resolve array formula in VBA

    Is looking at the result

    If result(i, 1) <> False Then

    any faster than using the actual conditional?

  12. #12
    Registered User
    Join Date
    07-11-2014
    Location
    Buenos Aires
    MS-Off Ver
    2010
    Posts
    16

    Re: How to resolve array formula in VBA

    What do you mean? Like

    If result(i,1) Then.. ?

    if it's that.. the thing is that if result(i,1) is not false, it returns the value of the cell that matched the criteria, and not a TRUE...

  13. #13
    Registered User
    Join Date
    07-11-2014
    Location
    Buenos Aires
    MS-Off Ver
    2010
    Posts
    16

    Re: How to resolve array formula in VBA

    Sorry... it works even in if the value is not boolean... thanks for the advice

  14. #14
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: How to resolve array formula in VBA

    What I meant was - is this:
    Please Login or Register  to view this content.
    Faster than this

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Resolve errorin formula when nesting issue
    By Joanofark in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-01-2013, 11:48 AM
  2. [SOLVED] Resolve three logical conditions using an if formula...
    By sland01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2013, 04:48 PM
  3. Resolve a problem with a sumproduct formula
    By dc123 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-14-2008, 11:55 AM
  4. Replies: 0
    Last Post: 01-21-2008, 12:44 PM
  5. [SOLVED] resolve:formula problem
    By Andrew in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1