none
VBA Function called twice if the input is a range RRS feed

  • Question

  • I have a column which is a named range in Sheet1. The name is Age.

    In sheet2 I have a cell which calls function one(I) and another which calls function two(R). 

    =one(22)
    =two(Age)
    

    The following is the code of my VBA functions:

    Function one(I As Integer)
       Debug.Print ("one enter")
       one = WorksheetFunction.Count(I)
    End Function
    
    Function two(R As Range)
        Debug.Print ("two enter")
        two = WorksheetFunction.Count(R)
    End Function
    function one is called once and function two is called twice.  Why?

    Friday, August 11, 2017 11:20 PM

Answers

  • Hi DiDoDe,

    I suggest you not use a function which will get different result according to running time as UDF in cells. Since the UDF could be re-calculate multiple times, its running time is uncontrollable, so the result is uncontrollable. For your example, I think they are both OK since their result just accord to value in B1:B5. For your original issue, I suggest you post when you could reproduce your issue. Thanks for understanding.

    Best Regards,

    Terry



    • Edited by Terry Xu - MSFT Tuesday, August 29, 2017 1:17 AM
    • Marked as answer by DiDoDe Tuesday, August 29, 2017 1:23 AM
    Tuesday, August 29, 2017 1:16 AM

All replies

  • What do you want to aquire by WorkseetFunction.Count in your function "one"?

    A function requires a range, e.g. A1:A10.


    Ashidacchi


    • Edited by Ashidacchi Saturday, August 12, 2017 4:22 AM
    Saturday, August 12, 2017 4:20 AM
  • If I didn't use the WorkseetFunction.Count then both functions were only called once.  With WorkseetFunction.Count  one was called once and two was called twice.  My real function is far more complex but I was trying to whittle it down to a simple example.

    My range, Age, is a column with over 1500 rows.

    Saturday, August 12, 2017 6:52 AM
  • I think you should provide code related to range "Age".
    As far as I don't know what "Age" is, I cannot say anything. 

    Ashidacchi

    Saturday, August 12, 2017 6:57 AM
  • If I look at Age in the Name Manager it is defined as "=Members!$F$2:$F$1564"
    Saturday, August 12, 2017 7:02 AM
  • Thank you for information.
    As for what you provided, I cannot know why your function "two" is called twice.
    Please check if you don't call "two" over once in every your VBA code.

    Ashidacchi

    Saturday, August 12, 2017 7:17 AM
  • Thanks for trying.  :-)

    Saturday, August 12, 2017 12:01 PM
  • D,
    re: multiple calls to function

    Try removing the code line:  Application.Volatile
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    Saturday, August 12, 2017 10:35 PM
  • I don't see Application.Volatile, is it something automatic?
    Sunday, August 13, 2017 3:33 AM
  • D,
    Re:  application.volatile

    It is a line of code that causes the function/sub to run whenever the worksheet is calculated.
    I believe I added that line to my original code in an edit.
    '---
    Jim Cone
    Sunday, August 13, 2017 3:45 AM
  • It's not something that I added to my VBA module, that's why I thought it might be automatic or hidden.

    Maybe I should have it.

    Sunday, August 13, 2017 3:49 AM
  • Maybe I should have it.

    No, you should not.

    If you are using UDF you can not control how many times the UDF is called, the calculation engine of Excel knows when and there can be circumstances that your UDF is called twice.

    But who cares? If you have programmed the UDF correctly, you can be sure that the result correlates with the other Excel formulas.

    Andreas.

    Sunday, August 13, 2017 9:43 AM
  • Hi DiDoDe,

    I failed to reproduce your issue. Here is my testing demonstrate.


    How do you do the test? 

    You could also clear output in VBA and set Formula->Calculation->Calculation Options->Manual,

    and then you could call the two sub in a cell and check the output in VBA if the sub is called twice.

    Best Regards,

    Terry
    Monday, August 14, 2017 10:12 AM
  • Thanks Andreas

    Monday, August 14, 2017 12:23 PM
  • Hi -

    I test it in a workbook with 1000+ rows in my Age column.  Age is on Sheet1.  On Sheet2 in two random cells I call the two function.

    hmm, would it make a difference that the cells in Age are themselves calculated off of birthdate?

    Monday, August 14, 2017 12:28 PM
  • If your issue has been resolved by Andreas advice, please mark as answer on Andreas.

    Closing your thread will be helpful for followers having similar issue.


    Ashidacchi

    Monday, August 14, 2017 12:30 PM
  • No, still working on the question

    Monday, August 14, 2017 3:11 PM
  • Hi DiDoDe,

    Have you checked my demonstrate? As you could see in the demonstrate, the two function is called only once. How do you test the function? If you do the same test, will the two function still be called twice?

    Best Regards,

    Terry 

    Tuesday, August 15, 2017 7:20 AM
  • Yes I did it as you demonstrated. However if after making Age a calculate value then

    • Changed the function (added a word to the debug.print)
    • change the calculated value in the Age cell

    the two() was called twice one time.  Another call to two() would be called once.

    The calculate in Age was based on SUM().

    So at this point I am guessing the second call has to do with the changes to the Age cell and/or the function code.

    Tuesday, August 15, 2017 3:06 PM
  • Hi DiDoDe,

    I'm still confused about how to reproduce your issue. What do you mean another two()? Do you use two() in two cells?

    I suggest you select Formulas->Formula Auditing->Show Formulas and then make a screenshot to tell us where is the age range and what formulas are used in Age range and where are you calling two() function.

    Just like this (C1:C10 is Age range)

    Besides, I also you use LICEcap tool to record a gif to show your demonstrate like I did in previous reply. 

    Thanks for your understanding. 

    Best Regards,

    Terry

    Thursday, August 17, 2017 9:37 AM
  • Hmm, I had deleted my debug code and I can't reproduce it myself!  I've gone on working with the workbook so maybe something I'm not remembering has also changed.

    Thursday, August 17, 2017 12:54 PM
  • Hi DiDoDe,

    Could you reproduce your issue now? Has your original issue been solved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry

    Monday, August 21, 2017 12:20 AM
  • No, I still cannot reproduce.  And it wasn't really an issue it was just a question, a behavior I was trying to understand. Maybe this string should just be closed.
    Tuesday, August 22, 2017 2:35 PM
  • Hi DiDoDe,

    I'm wondering why your Function will get different results after running multiple times. I think such function is not suitable to use as UDF in cells because it could be easily to be re-calculate multiple times. You must use it in case of unexpected re-calculation. I suggest you not use such function in cell or remove/lock the variable which may be different according to calling times.

    Best Regards,

    Terry

    Wednesday, August 23, 2017 6:23 AM
  • So, if an argument to my function is the result of a formula then I should repeat the formula in the function?  Given the following:

        Sheet1!A1    =SUM(B1:B5)

        Sheet2!A1    =MyFunc(Sheet1!A1)

        VBA Function MyFunc(I As Integer) As Integer
               MyFunc = I * 5
        End Function

    The preferred approach would be:

        Sheet2!A1    =MyFunc(Sheet1!B1:B5)

        Function MyFunc(R As Range) As Integer
            MyFunc = WorksheetFunction.Sum(R) * 5
        End Function

    Wednesday, August 23, 2017 3:48 PM
  • Hi DiDoDe,

    I suggest you not use a function which will get different result according to running time as UDF in cells. Since the UDF could be re-calculate multiple times, its running time is uncontrollable, so the result is uncontrollable. For your example, I think they are both OK since their result just accord to value in B1:B5. For your original issue, I suggest you post when you could reproduce your issue. Thanks for understanding.

    Best Regards,

    Terry



    • Edited by Terry Xu - MSFT Tuesday, August 29, 2017 1:17 AM
    • Marked as answer by DiDoDe Tuesday, August 29, 2017 1:23 AM
    Tuesday, August 29, 2017 1:16 AM