# Thread: Force User Defined Functions to calculate

1. I have created a user defined function which extracts the first element in a string of dates. It works fine on my machine but on a friend's machine shows a Value error. If I insert and then delete a column the function calculates and produces the correct answer. Any suggestions as to how to get it to calculate? Automatic recalc is turned on and pressing F9 does nothing on my friend's machine.

The code is as follows:

Function NextDue(strDates As Variant) As String
'Returns the next date when work is programmed
Dim PresentDate As Variant
Dim a As Integer
Dim b As Integer
Dim maxyear As Integer

maxyear = 2110
PresentDate = Worksheets("Constants").Range("Start_Year")

For a = PresentDate To maxyear
If (InStr(1, strDates, a) > 0) Then Exit For
Next a

If a >= 2110 Then 'the date has not been found so find the nearest
NextDue = ""
On Error Resume Next
For b = 1 To Len(strDates)
If Val(Mid(strDates, b, 4)) > PresentDate Then
NextDue = Mid(strDates, b, 4) 'we found a proper date
Exit For
End If
Next b
If Val(NextDue) = 0 Then NextDue = ""
Else
NextDue = a
End If
End Function

2. You could insert a line

Application.Volatile

at the beginning of the function (below the declarations). This will force the function result to be recalculated whenever any a calculation occurs in any cell on the worksheet. See Volatile Method for more info.

3. [quote name='HansV' date='2009-12-07 09:28' timestamp='1260134889' post='808754']
You could insert a line: Application.Volatile

Thanks HansV it looks like this is the answer, will have to try it on my friend's machine. The spreadsheet handles a lot of data and takes several seconds to recalculate I have concerns that this may slow the recalc down but will try it. I will let you know how I get on.

Thanks again

Roger Currier

4. Slow calculation could be a problem, because Application.Volatile will cause the function to be recalculated whenever any cell on the sheet is calculated - there is no way to restrict this.

Perhaps the function could be written more efficiently - if you wish, you could attach a stripped down copy of the workbook (with all sensitive information removed) so that Loungers can have a look at it.

5. Slow calculation could be a problem, because Application.Volatile will cause the function to be recalculated whenever any cell on the sheet is calculated - there is no way to restrict this.

Perhaps the function could be written more efficiently - if you wish, you could attach a stripped down copy of the workbook (with all sensitive information removed) so that Loungers can have a look at it.

HansV

Have incorporated the Volatile line into the spreadsheets and it does not seem to make any difference to the processing time. Have now emailed the changed spreadsheets to my friend for checking on his machine.

I doubt if the function could be much improved, the loop is a max of 100 times and the code is pretty trivial.

Will let you know if Volatile fixes the problem

Thanks again

Roger Currier

6. Originally Posted by Roger Currier
Have incorporated the Volatile line into the spreadsheets and it does not seem to make any difference to the processing time.
OK, good to hear that. Thanks for the feedback!

7. Hi Roger,

Just curious, what does the function do? Seems to me, this worksheet function generates the same result:

=IF(YEAR(B1)>2110,"",YEAR(B1))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•