Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fx to VBA (Excel )

    I started looking at this in <post:=627,602>post 627,602</post:>

    When do you switch from nested functions to a user-defined function in VBA?
    What prompts you to switch from nested functions to a user-defined function in VBA?

    Excel, tricky bit of formula. I go the route of writing on one row an atomic component of the result.
    In the next row I write another component.
    Row by small-step-row I build up to the desired result.
    Then I take a deep breath and coagulate the steps into one horribly long function statement like this:
    <font color=red>=IF(IF(NOT(ISERROR(MATCH(P12,$D$5:P$5)<= 12)),MATCH(P12,$D$5:P$5),"")>1,IF(IF(NOT(ISERROR(M ATCH(P12,$D$5:P$5)<=12)),MATCH(P12,$D$5:P$5),"")<= 12,SUM(OFFSET(D6,0,P16):P6),""),"")</font color=red>
    and I start thinking how neat this would look (in the user's worksheet) if it were encoded as a VBA function.

    I am thinking that there must be an execution (ReCalculation) cost associated with sort of function in a cell, and I'm worried that the overhead of interpreting a User-defined function might be excessive.

    I'd like to hear of any general ideas on this.

    For end-users who are confident in my VBA skills, a VBA rendition for a commonly-used function works well, aesthetically; they aren't too interested in the behind-the-scenes stuff.
    For end-users who retain my VBA skills for a mere two months on-site, I am hesitant about leaving a legacy of VBA in a non-VBA shop.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fx to VBA (Excel )

    I don't have hard-and-fast rules, but my reasoning follows roughly the same lines as yours.

    I first look at whether VBA would be possible at all.

    - Are end-users allowed to enable macros?
    - Is there support for handling VBA problems?

    If not, VBA is out, obviously.

    In general, built-in functions are more efficient than user-defined functions because their code is compiled instead of interpreted. So I'll try to solve a problem using built-in functions first.

    But:
    - In mega-formulas it's often unavoidable to repeat the same calculation (for example, MATCH(P12,$D$5:$P$5) occurs four times in your formula).
    - Array formulas carry a large overhead. A short VBA loop can be more efficient than an array formula that combines every possible pair of cells.
    - Even though I build mega-formulas the same way you do, they can become unwieldy and almost impossible to maintain.

    Some of these problems can be avoided by keeping the intermediate formulas in hidden rows or columns. But at some - rather fuzzy - point, it becomes more attractive to write a few lines of easy-to-understand and well-documented code than to expand the formula even further.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fx to VBA (Excel )

    Thanks, Hans.
    I agree with all your points.
    I'm especially leery of hidden cells/rows/columns. My end-users have a most annoying habit of deleting them .....
    (Once we get rid of all the humans, we'll be OK .....)

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fx to VBA (Excel )

    Then I found these posts, same thread:

    <post#=663,748>post 663,748</post#> <post#=663,751>post 663,751</post#> "IF" formulas cannot be nested more than 7 levels deep;

    I am running into some sort of limit, not 7-IF functions, but obviously something. That alone seems to be a practical reason for switching to a user-defined function.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fx to VBA (Excel )

    Mostly worksheet functions are faster, but there are proven situations where a VBA UDF is faster than a worksheet function. Charles Williams shows some more info on UDFs:
    http://www.decisionmodels.com/calcsecretsj.htm

    And I write someting about udfs too: http://www.jkp-ads.com/articles/fixlinks2udf.asp
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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