I have created a workbook with two different User Defined Functions (one to count the number of sheets in my workbook, the other one to force a formula (which is created by combining several text fragments).
I need the formulae to recalculate every time I change data on any of the sheets in the workbook, so I have set them to be Application.Volatile = True. However, this gives me two problems:
(1) The formula also attempts to recalculate if I change something in a different workbook, and then gives me an error message. I would thus need to recalculate the field only if i change something in the containing workbook, but not in any other workbook that might be open at the same time.
(2) Application.Volatile = True seems to put the fileds containing its formula at the top of the recalculation tree, and thus does not update certain changes. For example:
My UDF refers to cells A1 on Sheet1 and Sheet2.
Cell A1 on Sheet1 is SUM(A2:A5)
If i change A3 on Sheet1, Excel recalculates first my UDF which sums up A1 from the two worksheets, and afterwards recalculates the fields A1, which leads to a wrong total in my UDF.
I hope this is written somewhat understandably. I am a pure autodidact on VBA, so I might miss something all too obvious.