Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Wellington NZ
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Wellington NZ
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Wellington NZ
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Roger Currier View Post
    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. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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