+ Reply to Thread
Results 1 to 3 of 3

"Use arguments, not cell references" (Walkenbach)

  1. #1
    Registered User
    Join Date
    07-05-2012
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    12

    Question "Use arguments, not cell references" (Walkenbach)

    Hello, guys!
    As we can read in "Excel 2010. Power Programming with VBA" by John Walkenbach:
    Use arguments, not cell references
    All ranges that are used in a custom function should be passed as arguments. Consider the following function, which returns the value in A1, multiplied by 2:
    Function DoubleCell()
    DoubleCell = Range(“A1”) * 2
    End Function
    Although this function works, at times, it may return an incorrect result. Excel’s calculation engine can’t account for ranges in your code that aren’t passed as arguments. Therefore, in some cases, all precedents may not be calculated before the function’s value is returned. The
    DoubleCell function should be written as follows, with A1 passed as the argument:
    Function DoubleCell(cell)
    DoubleCell = cell * 2
    End Function
    It is quite unclear to me - in what cases exactly we may face the problem - "at times, it may return an incorrect result. Excel’s calculation engine can’t account for ranges"?? We can see that the example in the book does the job - it works well...
    The point is that I really need to compose my function with references to cells (having to use around 100 constants, it seems that it's not a good idea to include all of them as arguments). I am staring at the book and feeling some doubts what I should do, why has Walkenbach used that indefinite statement without exact specification...
    Many thanks in advance for your advice.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: "Use arguments, not cell references" (Walkenbach)

    Suppose you had a function called Sin that didn't accept any arguments, but returned the sine of the angle in the cell to the left of where the formula appears. Excel would see no dependency, and would not recalculate if you change the value in that cell.

    You can put all the constants you want in a function (pi, e, the eccentricity of the earth), just make sure to pass all the variables the function needs to arrive at a result.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    07-05-2012
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: "Use arguments, not cell references" (Walkenbach)

    Thank you. I know that changes in values that are not passed as arguments do not lead to recalculation of a function result.
    But don't you think that in the book it is written in a little misleading way? Let me explaine, the author uses vague language - "at times, it may return an incorrect result.". It may look like description of a floating bug or something like that. While he could have clearly specified cases where result is always incorrect. Lets compare:
    1. "Excel’s calculation engine can’t account for ranges in your code that aren’t passed as arguments."
    2. "Excel’s calculation engine can’t account for changes in ranges in your code that aren’t passed as arguments."
    Do not you think that 2-nd sentence is correct while the 1-st is not?
    What is your opinion? To be honest I am not completely sure that I am right in my logics and proposals, my English is not that perfect to understand all subtle meanings.
    Thank you for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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