Why doesn't my custom function run in a cell

[Bezzie]

I created a custom function to retrieve an account balance. It is declared as Public Function.

I now need to enter this function in a cell on another worksheet where the balance of the account must be displayed. So I enter

Please Login or Register  to view this content.

into the cell where I want the balance displayed. When I first enter the formula in the cell the balance is displayed correctly. When I do other transactions and then do a Application.Calculate the formula is not recalculated. Pressing F9 also doesn't recalculate the new balance.

What am I missing or doing wrong? Why is the formula not recalculated?

[romperstomper]

What are cAccNumber and dDate?

[Bezzie]

In the cell it would be references to other cells. One with the account number and one with the date e.g. =Get_Account_Balance(B3, C3) where B3 has a text string account number e.g. 1001000100-CASH and C3 has a date in it e.g. 17-Nov-14.

What Get_Account_Balance will then do is find the 1001000100-CASH account in the Accounting worksheet and then find the last entry on or before 17-Nov-14 and return the account balance in that account on that date.

[romperstomper]

Unless the direct inputs to the function change, the formula won't recalculate. It seems you have other references to ranges in the code which isn't good practice but if you need the formula to update regardless you need to make the function volatile.

[Bezzie]

Thanks romperstomper. That solved my problem.

[Bezzie]

Wanted to add to your reputation but it won't let me. Sorry.