Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function update on load (97/2000/xp)

    We have a spreadsheet that is used by people with 97/2000 and xp
    2 cells are populated by the following function call.
    =AddWeeks(D152,F151,G151)

    When you open the spreadsheet with 97 and 2000 the cells are populated correctly, but with xp the cells just show 0's (which causes a div by 0 error further down the line). My solution was to force a refresh of the data in the Workbook_Open event.
    First I tried
    sheet1.range("A1"). calculate
    , but that didn't work, so I tried
    sheet1.range("A1").formula = "=AddWeeks(D152,F151,G151)"
    which works a treat in xp, but broke 97 putting (you guessed it) 0's in the cell...
    Anyone encountered this and know a solution that fits all?

    TIA

    Alan

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function update on load (97/2000/xp)

    A bit hard to tell without the function code.

    You could of course test in the workbook_open event for the XL version and have only XP do the formula="addweeks..." stuff...:

    Select Case Left(Application.Version,2)

    Case "10"
    'Do XL XP stuff
    case "9."
    'Do XL2000 stuff
    case "8."
    'Do XL97 stuff
    Case Else
    'Office 2004 ? <g>
    End select
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function update on load (97/2000/xp)

    Jan,
    I've included the function below, I think it's too noddy to fail (not my code though). It was because it was so simple that I figured it has to be a difference between how 97/2000 and xp works. I've just tried the version that works on xp with 2000 and it doesn't work. I was toying with the version check but thought I'd check with the board first.

    Public Function AddWeeks(oActiveCell As Object, rStartMonth As Range, rEndMonth As Range) As String
    'Gets the total number of weeks between two months
    Dim intTotalWeeks As Integer
    Dim intMonthCount As Integer

    intMonthCount = rStartMonth

    Do Until intMonthCount > rEndMonth
    Select Case intMonthCount
    Case Is = "1"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("I3").Value
    Case Is = "2"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("J3").Value
    Case Is = "3"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("K3").Value
    Case Is = "4"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("L3").Value
    Case Is = "5"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("M3").Value
    Case Is = "6"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("N3").Value
    Case Is = "7"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("O3").Value
    Case Is = "8"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("P3").Value
    Case Is = "9"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("Q3").Value
    Case Is = "10"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("R3").Value
    Case Is = "11"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("S3").Value
    Case Is = "12"
    intTotalWeeks = intTotalWeeks + ActiveSheet.Range("T3").Value
    End Select
    intMonthCount = intMonthCount + 1
    Loop

    AddWeeks = intTotalWeeks

    End Function

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function update on load (97/2000/xp)

    Strange way of coding indeed <g>.

    I would say these
    Case Is = "1"
    statements are odd.

    intMonthCount being an integer I'm surprised it works at all.

    Maybe shortening those statements to
    Case 1
    Case 2
    ...

    helps?

    Otherwise I would not be too surprised if someone can cook up a normal worksheet function that computes the number of weeks between two months so you can get rid of the udf all together.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Function update on load (97/2000/xp)

    Without a sample workbook to see what populates those cells, it's hard to tell, but have you looked at the built-in DATEDIF() function, using parameter "m"?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function update on load (97/2000/xp)

    Unfortunately it would be difficult to sift out the confidential data. The powers that be say that this year is a 53 week year (I get the feeling that is v. badly thought out) so I can't use DateDiff either :-(
    The trouble is I can't get excel xp to be consistent, 4 machines, 4 different results so far...

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Function update on load (97/2000/xp)

    Any chance you could censor (blank out) everything except those ranges addressed by the macro, and provide a couple of different scenarios fro just those ranges? I can't imagine where week counts would be a confidentiality problem. (Also, you may want to blank out all Properties information for the WB.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function update on load (97/2000/xp)

    So do it the other way around, don't send us the entire project.

    We already have the UDF's code, so all we need is the numbers that are in the ranges used by the UDF (bad idea to use ranges to hold the UDF's constants btw) and a set of examples of arguments and their results.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Star Lounger
    Join Date
    Apr 2002
    Location
    Nottingham, Nottinghamshire, England
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function update on load (97/2000/xp)

    Jan, here's the data you asked for (I've tried but even with maximum zip compression I can't get the spreadsheet down below the 100k and it remain meaningful)

    rows 1, 2 and 3 of the sheet contains
    Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec <- Month Names
    1 2 3 4 5 6 7 8 9 10 11 12 <- Month Numbers
    4 4 4 5 4 4 5 4 4 5 4 5 <- No. of weeks in month

    this is used in the udf

    if the F151 contains 1 and G151 contains 12 then the result in D152 should be 52 as the udf adds all of the weeks within the range of months.
    if the F151 contains 1 and G151 contains 6 then the result in D152 should be 25.

    The trouble is that on opening in XP the result is often 0 which leads to Div/0 errors elsewhere.
    My solution to this was to force a refresh of D152 on opening, but this doesn't appear to work either.

    A lot of the spreadsheet is protected to prevent users over writing critical data, including row 1,2,3 and cell D152 that contains the formula.
    The nature of this system is to calculate our costs models, each customers cost model spreadsheet is linked to a spreadsheet that contains our average salaries(updated quarterly).

    This is one of the systems (I'm sure we've all seen them) where someone who had a requirement and developed it in excel when it should be a proper database. It's growing with virus like proportions (currently nearly 400 spreadsheets) as new customers and new requirements are added and no one wants to pay for a redesign.

Posting Permissions

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