Hi all,

I had an Excel file with a few cells using some User defined functions that I had wrote. But I does not seems to recalculate when ever I open this Excel file. Can anyone please advise on what I can do so that these cells will auto refresh the calculation.

Thanks alot.
oks

Hi,

Check whether by chance in the options menu recalculation is set to manual instead of automatic.
Otherwise send a dummy parameter to the function with reference to the cell that is changed. You may ignore this parameter in your function, but while callin the function on the cell, the parameter should be passed. For example if you are expecting some change in some value in some cell when value in A10 changes, you can pass an argument to the function as A10.
i.e. your function for example may be like

Public Function myfunc() As Integer
'just double the value of A10 and place it wherever the function is called
myfunc = Sheet1.Cells(10, 1) * 2
End Function

You may have put in your some cell =myfunc()
This will not be refreshed automatically even if the value in A10 changes.

For this you may change the function to

Public Function myfunc(dummy As Range) As Integer
myfunc = Sheet1.Cells(10, 1) * 2
End Function

And wherever your are putting the function as =myfunc() put it as =myfunc(A10)

HTH

jp

Hi

Although I have not tried it, but, would putting 'Application.Calculate' in the Workbook objects Workbook_Open() event update the worksheet when it opens??

MTB