User defined Formula calculations (1 Viewer)

Kempes

Registered User.
Local time
Today, 09:03
Joined
Oct 7, 2004
Messages
327
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
 

Kempes

Registered User.
Local time
Today, 09:03
Joined
Oct 7, 2004
Messages
327
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?
 

Kempes

Registered User.
Local time
Today, 09:03
Joined
Oct 7, 2004
Messages
327
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
 

chergh

blah
Local time
Today, 09:03
Joined
Jun 15, 2004
Messages
1,414
Try putting this under the sheetchange event.

Code:
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.
 

Kempes

Registered User.
Local time
Today, 09:03
Joined
Oct 7, 2004
Messages
327
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
 

chergh

blah
Local time
Today, 09:03
Joined
Jun 15, 2004
Messages
1,414
Scrub that idea lets go for some overkill.

Code:
application.calculatefull

This will recalculate all cells in all open workbooks.
 

Kempes

Registered User.
Local time
Today, 09:03
Joined
Oct 7, 2004
Messages
327
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
 

Users who are viewing this thread

Top Bottom