I have created a UDF that works a treat. However, the values are not automatically calculating.

I don't use excel much and can't figure out a way to re-calculate this formula when the sheet has been updated.

I have found a manual method of ( ctrl, alt, shift + F9) but this needs to be automatic.

Can anyone help.

BTW: I have also checked the calculations tab which is set to automatic within Options.

Many thanks

Kempes

Howdy. UDFs are not automatic. So you need to write some VBA code (probably one line would do it) to be triggered with any change in the worksheet.

Great, thanks for your help.

Any idea what the code is for refreshing the functions?
I image I need to place this in the sheetchange part of the code.
There is also one called sheetcalculate. Could I use this at all?

I've done a bit of searching and found that by adding

application.volatile

into my function, it is supposed to recaluate with any changes. However, this now breaks my function, and the cell now shows #VALUE!.

Taking this line back out and selecting the cell again, it goes back to the correct value but still doesn't recalculate automatically.

I don't understand why this bit of code should be the answer yet it breaks my function.
This is my code so far which fails with volatile in it.

Function MaxColwithData(wsindex As Integer, col As String) As Variant

Dim x As Integer

Application.Volatile
x = Worksheets(wsindex).Range(col & 65336).End(xlUp).row
MaxColwithData = x

End Function

Try putting this under the sheetchange event.


thisworkbook.calculate


If your workbook starts running slow as a result of this you could limit this to only fire when a cell in a particular range is changed.

It didn't seem to like that bit of code for some reason.

I tried

application.calculate

and

worksheets(1).calculate

Nothing!

I'm running out of ideas

Scrub that idea lets go for some overkill.


application.calculatefull


This will recalculate all cells in all open workbooks.

aha, yes.

that seems to work.
Many thanks for you help.

I'm still a little puzzled as to why application.volatile failed, but this is just as good for me.

Thanks again