User-defined function Excel 2010

[cwunderlich]

I am trying to create a custom function that will be used on multiple different sheets. Depending on the data on each sheet, the custom function needs to calculate a few different ways. One of the inputs needed for the function to know what to do is "NumNewDiags". I currently have another macro enter this number into cell A1 of this first sheet (with cell A1 of the other sheets referencing this first sheet's cell A1). Then on each sheet, cell A1 is the final input for the custom function so it knows how many new diagonals there are.

Like I said, depending on the circumstances of each sheet, the calculation could be slightly different (this is handled within the custom function with if statements). However, here is my problem: I get the function to do what I want when I am viewing one sheet. But then when I go to look at these same functions on a different sheet, I am either not getting correct values or nothing is recalculating. But then on this different sheet, when hit F2 within these formulas they update correctly and the previously correct functions get messed up.

I have played around with syntax like Application.Volatile and With Application.Caller.Parent but I just can't seem to figure out what is happening.

 

[Andrew Poulsom]

Please post your function. Your problem is most like the result of using ActiveSheet.

 

[cwunderlich]

I think I figured it out. I was not using ActiveSheet anywhere. However, with my With Application.Caller.Parent, when referring to different cells, one of the lines of code did not have .Cells(), it just had Cells(). Could this sort of thing explain this behavior?

Thank you

 

[Andrew Poulsom]

Yes, if you don't qualify the Cells property the ActiveSheet will be used.