+ Reply to Thread
Results 1 to 16 of 16

Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

Hybrid View

  1. #1
    Registered User
    Join Date
    05-12-2010
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    5

    Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

    I know that there Excel has a built in macro calle EVALUATE() which can treat string as formula so I created a User Defined Function evalu() to call the macro.
    Function evalu(mystr As String) As Variant
    
    evalu = Application.Evaluate(mystr)
    
    End Function
    To demonstrate how it works, please see figure1.
    I named two columns, "Price" and "t". At E3 and F3 I enter the formula I want as text.
    Supposing an array formula like "{=evalu(E3)} would work like calculating Price*t on every row.

    Figure 1
    HTML Code: 
    Indeed the evalu() function works, plx see figure2. However for formula with reference functions like INDEX, OFFSET, VLOOKUP (column F). The UDF evalu return only the first item (2) . It would have worked if I enter "=index(price, t)" for each row on Column F so I am sure the formula is just fine.

    Figure2
    HTML Code: 
    It seems that when the formula string (F3) contains INDEX, the evaluate() macro/ UDF evalu doesn't know to return an array of result.

    I once thought of modifying the UDF so that it first calculate the result somewhere else and then return back o the function but EXCEL UDF cannot alter any cells other than the cell that called it (caller).

    Doesn't anyone know how to modified the UDf so that it would return the array of result?

    It is essential to use the UDF here because I am building a generic pricing worksheets and there are like 100 columns of variables which formulae differs by products. The last solution I can think of is writng a macro then copy the formula string down everytime a formula is changed. However this is not cool
    Attached Files Attached Files
    Last edited by cryrus; 05-12-2010 at 11:41 PM. Reason: code tags

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

    Please add CODE tags around your three line UDF.

    Irrespective of whether or not this is the best approach to use one way of achieving your output for Column F might be:

    INDEX(LOOKUP(t,ROW(t)-MIN(ROW(t))+1,price),0)

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

    Actually, that's not correct... the sample is misleading given consecutive nature of t ... it can I think be simplified (and corrected!) to:

    INDEX(LOOKUP(t,t,price),0)

    (though above assume t is both unique and sorted in asc order)

    Depending on nature of Eval call it might also be worth qualifying the Parent object by virtue of the range, eg:

    Function evalu(mystr As String) As Variant
    evalu = Application.Caller.Evaluate(mystr)
    End Function
    (not really necessary in this specific context given use of named ranges with workbook scope)

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

    Did you mean:
    Application.Caller.Parent.Evaluate(mystr)
    ?
    Everyone who confuses correlation and causation ends up dead.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

    Quote Originally Posted by romperstomper View Post
    Did you mean:
    Application.Caller.Parent.Evaluate(mystr)
    ?
    Thanks for the spot R....

  6. #6
    Registered User
    Join Date
    05-12-2010
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

    Quote Originally Posted by romperstomper View Post
    Did you mean:
    Application.Caller.Parent.Evaluate(mystr)
    ?
    Sorry I just registered today so I didn't know that I can use "#" for CODE, will certainly do so next time.

    However adding the "Caller.Parent" doesn't resolve the problem.

    Like I said this is just an example of what I am trying to do. The pricing worksheets will have over 100 named columns (and thus over 100 of these formulae, which differs by products). To create a generic template it is essential to make the UDF works.

    In fact I think MS should really incorporate Evaluate() as a standard excel function as it is so powerful and would make excel modelling a lot easier generic enough to cater different products, assumption, models.

    Cryrus
    Last edited by cryrus; 05-12-2010 at 08:06 AM.

  7. #7
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

    You can post the actual workbook - click Go Advanced, then use the Manage Attachments button.

  8. #8
    Registered User
    Join Date
    05-12-2010
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

    Quote Originally Posted by romperstomper View Post
    You can post the actual workbook - click Go Advanced, then use the Manage Attachments button.
    Thanks I just attached the workbook in the first post. Please take a look at it.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

    Have you tried the formula suggestion in post #3 ?

    INDEX(LOOKUP(t,t,price),0)

    The point re: using Application.Caller.Parent was separate and more to do with ensuring that Evaluate is always processed against the appropriate object.

  10. #10
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,375

    Re: Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP

    cryrus, as DonkeyOte already stated:

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    I'll add the code tags this time, but please use them from now on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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