Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do worksheet references get resolved? (2002)

    I've got a workbook that has two sheets. On sheet 2, I have workbook function references that reference cells on sheet 1.

    When I open the workbook, if sheet 2 is the active sheet, the cells containing worksheet functions on sheet 2 ALL are initially displayed as #NAME!, but Excel, without further action from me, updates all the cells.

    When I open the workbook, if sheet 1 is the active sheet, the cells containing worksheet functions on sheet 2 are not all updated, some are displayed as #VALUE!. Each cell updates correctly if I select the cell and force an update.

    The worksheet function used accepts certain args of the form (A2345, D712).
    Also some args are of the form (sheet1!C34:C45, sheet1!A98:A109)..

    The cells that result in a #VALUE! are only those that use the form (A2345, D712), referring to cells on the same worksheet. Since the cell updates correctly when I force calculation, I do not understand why Excel does not automatically update the cells.

    The cells that do update are referring to cells on the other worksheet, so I guess that somehow causes Excel to get the correct reference.

    So it seems that Excel cannot get proper values for args such as (A2345, D712), unless those cells are on the active sheet when the workbook is opened, or if Iforce calculation manually.

    How can I avoid this problem?

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

    Re: How do worksheet references get resolved? (2002)

    As with your other post, if these are User Defined Functions, they need to include:

    Application.Volatile

    and even then may take an instant to resolve after the WB is first opened. (Recalc and resolution on open can be forced using the Workbook_Open event containing Application.CalculateFull.) Does this help?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do worksheet references get resolved? (2002)

    I've tried application.calculatefull and application.volatile.

    Actually, I want application.volatile to be false.

    In any case, the problem may be simplified to the following.

    Part of the code of the worksheet function examines particular range args to detect whether they are of the proper form. In this case, the proper form may be any of the following:

    1. An M x 2 array
    2. A 2 x M array.
    3. Two vectors of length M, each of which may be either a row vector or a column vector.

    The worksheet has a bunch of cells that test that aspect of the function by providing calls that differ only in that single range arg. I wrote this code yesterday and naively thought that I had tested all cases, guess not.

    If when the workbook opens, the test sheet becomes the active sheet, I see a bunch of #NAME! warnings, but Excel quickly updates those, implying that the code works.

    However, if the test sheet does not become the active sheet, then certain of the test cells have the #VALUE! warning, but the others do get updated by Excel. I can then manually select each cell and cause the cell to update. Of course, when I manually update, the test sheet is the active sheet, so that might not prove anything.

    I have since figured out the characteristics of the troublesome case, so if there is an error in the code, I should be able to find the critter. However, I do not yet see how it is tied to whether the sheet is active.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: How do worksheet references get resolved? (2002)

    Hi Howard,
    This may/may not help:

    A recalc on opening (or any other time the spreadsheet recalcs) can be forced by including the NOW() function in the formula associated with a non-volatile UDF.

    For example, if the formula using the UDF returns a text string, you can append:
    &IF(NOW()=0,"","")
    Alternatively, if the formula using the UDF returns a numeric value and your system date is anything later than 12am on 1/1/1900 <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>, you can append:
    *(NOW()<>0)
    Note, though, that because NOW() is itself volatile, Excel will prompt you to save the workbook even if no (real) changes have been made. Even this can be got around: Since you've got a UDF, you may as well add an auto_open macro to clobber the unnecessary file-save prompt:

    Sub Auto_Open()
    ThisWorkBook.Saved = True
    End sub

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: How do worksheet references get resolved? (2002)

    You should know, that UDF's that somehow error out during their recalc cause the #Value! to be displayed in their calling cell.
    I strongly suspect that there is a bug in one of your UDF's that has to do with sheet referencing.

    Very likely there is one or more range references in one of the UDF's that doesn't have a worksheet qualifier with it and thus Excel assumes you want it to use this range *from the active sheet*. Thus changing sheets and doing a recalc will cause the errors you describe.

    A common cause of problems like these is when one uses fixed ranges inside the UDF as opposed to having those ranges included in the set of arguments to the function.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do worksheet references get resolved? (2002)

    IMHO, u r right, but I believe the problem may have been a mistyped arg to the function.
    This function has no hard coded references.

    However, the puzzle is why the cell updated correctly manually or automatically when the active sheet upon open.
    In all cases, the same arg was used.

    I'll have to postpone investigating this for a later day.

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do worksheet references get resolved? (2002)

    Here's a reproducible example of what's wrong.

    The workbook has two sheets, call them Sheet 1 and Sheet 2.

    Sheet 2 has a formula in C48 that includes the following as an arg to a variable of type Excel.Range in a UDF:

    (H46:M46, H47:M47)

    Cell D48 on Sheet 2, has the same formula but uses:

    ('Sheet 2'!H46:M46, 'Sheet 2'!H47:M47)

    So the explicit reference to Sheet 2 should be unnecessary.

    Now if I activate Sheet 1, save the workbook and exit from Excel, then open the workbook.

    Sheet 1 is the active sheet, as expected.
    C48 on Sheet 2 updates correctly.
    D48 on Sheet 2 produces #Value!.

    However, if I activate Sheet 2, save the workbook and exit from Excel, then open the workbook.

    Sheet 2 is the active sheet, as expected.
    C48 on Sheet 2 updates correctly.
    But now D48 on Sheet 2 updates correctly.

    So it would seem that Excel does not pass (H46:M46, H47:M47) and ('Sheet 2'!H46:M46, 'Sheet 2'!H47:M47) as the same values when the UDF vets Areas(1) and Areas(2). It sems to depend on what sheet is active.

    Of furher interest may be that until I added the formula in D48 on sheet 2, there were additional cells on Sheet 2 producing #Value!, so inserting the formula in D48 seems to have caused something to change.

    As far as I know, the UDF is setting all Range variables to Nothing upon completion.
    I'll check the code listing to see whether I missed one, but those variables are neither Static nor Public, so should they not go away between invocations of the UDF?

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do worksheet references get resolved? (2002)

    I figured out what is causing the problem. Next step is to find a workaround.

    I wrote a function to dump the relevant parameters.
    I inserted a call to the dump function in the worksheet function, but I only did output for the cells C48 and D48 that demonstrate the problem.

    Then I saved the workbook such that sheet 1 would be the active sheet when the workbook was opened.
    The output is below. I've changed names to protect the identity of the app.

    Sheet 1 is named "Pizza" and sheet 2 is named "Bagels and Lox".

    Remember that all these function references are on the Bagels and Lox sheet.
    So, you can see that for some reason Excel is using the wrong sheet for the function referenced in cell D48 on the Bagels and Lox shhet.

    But for the reference in the adjacent cell, C48, Excel is refering to the correct sheet.

    Surely, this is a known problem???

    <pre>Active Cell: $D$48
    Range: $H$46:$M$46,$H$47:$M$47
    Workbook: XXX.xls
    Worksheet: Pizza
    Areas: 2
    Area: $H$46:$M$46
    Workbook: XXX.xls
    Worksheet: Pizza
    Area: $H$47:$M$47
    Workbook: XXX.xls
    Worksheet: Pizza
    RunTheFunction(Function): 1151
    RunTheFunction(Function): 1172
    RunTheFunction(Function): 1162
    RunTheFunction(Function): 1131
    RunTheFunction(Function): 1172
    RunTheFunction(Function): 1132
    Active Cell: $C$48
    Range: $H$46:$M$46,$H$47:$M$47
    Workbook: XXX.xls
    Worksheet: Bagels and Lox
    Areas: 2
    Area: $H$46:$M$46
    Workbook: XXX.xls
    Worksheet: Bagels and Lox
    Area: $H$47:$M$47
    Workbook: XXX.xls
    Worksheet: Bagels and Lox
    RunTheFunction(Function): 1121
    </pre>


  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do worksheet references get resolved? (2002)

    I found out what is causing the problem.
    Definitely an Excel bug/"feature".

    First, I created a copy of cells C48 and D48 in cells E48 and F48, respectively.

    I then created a copy of the data in different cells on the Bagels and Lox sheet and changed E48 and F48 to point to the new data copy.
    Result, as expected, F48 had the same problem as D48.

    Now, to verify the earlier finding that the Areas collection was referring to the wrong worksheet, I copied the data to sheet Pizza in the same cell positions as on the Bagels and Lox sheet.
    Sure 'nuf, now cell F48 on the Bagels and Lox sheet was correct, even tho Areas was pointing to the wrong sheet.
    D48 was still incorrect, as expected.

    Now, I had an epiphany!
    There are 23 cells using the same UDF, so why the problem in ONLY D48 and F48.
    Examining all 23 cells, I noticed that D48 and F48 had a common characteristic. They are the only two cells invoking the UDF using an arg of the following form:

    ('Bagels and Lox'!H64:M64, 'Bagels and Lox'!H65:M65)
    Which looks perfectly valid.

    However, the Areas collection returns "Pizza", instead of "Bagels and Lox" as the worksheet Parent for the cell reference ONLY because the UDF is on sheet Bagels and Lox.

    So the problem seems to be that the Areas collection gets confused by self-referential worksheet references.

    When used on the Bagels and Lox worksheet, ('Bagels and Lox'!H64:M64, 'Bagels and Lox'!H65:M65) and (H64:M64, H65:M65) are equivalent, but the Areas collection doesn't think so.

    I also tried copying the "Bagels and Lox" sheet to "XXX".
    But removing the spaces did not matter, same problem since reference was still self-referential.

    Surely, MSFT already knows about this??

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

    Re: How do worksheet references get resolved? (2002)

    Without seeing the actual code of your UDF I can give no further advice on your problem, I'm sorry.

    After reading all your elaborate replies I still state that it probably has to do with not having explicitly referred to the proper sheet *within the UDF* and therefore XL treats the range/area as being on the active sheet.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do worksheet references get resolved? (2002)

    The problem occurs when Excel passes the arg to the UDF. Has nothing to do with the UDF's code.

    Before I do anything, I can determine that Excel is not vetting the Worksheet correctly
    for an Area if the reference is of the form
    'XYZ'!whatever, and the arg is passed to a UDF in a cell on worksheet XYZ.

    I could change the UDF code to use the worksheet specified for the area, but what good
    would that do since Excel is passing me the incorrect info.

    If I insert the following two lines

    Dim strTemp As String
    strTemp = RangeID(rngInvInput)

    into the UDF, right after the Function statement for the UDF, and I use the code below, I get the following results.
    The #Value! is a result of Excel returning incorrect info for the Areas.

    <pre>Active Cell: $D$48 Not A B XXX.xls Y
    rngArg: $H$46:$M$46,$H$47:$M$47 Y XXX.xls
    Range: $H$46:$M$46,$H$47:$M$47
    Workbook: XXX.xls
    Worksheet: Y
    Areas: 2
    Area: $H$46:$M$46
    Workbook: XXX.xls
    Worksheet: Y
    Area: $H$47:$M$47
    Workbook: XXX.xls
    Worksheet: Y
    </pre>


    The arg being passed is actually ('Not A B'!H46:M46, 'Not A B!H47:M47), so Excel itself, is losing the worksheet context,
    even before my code gets a chance to mess with it. This problem occurs if sheet Y is active upon opening the workbook.
    Since the worksheet is hardcoded into the reference, it should not matter which sheet is activated when the workbook opens. Indeed,
    C48 on worksheet Not A B uses (H46:M46, H47:M47) instead of ('Not A B'!H46:M46, 'Not A B!H47:M47), and C48 is updated correctly.
    The problem is only wit hcell D48 that uses the ('Not A B'!H46:M46, 'Not A B!H47:M47) as an arg.

    Indeed, I can reproduce the problem without using my UDF, just be inserting the following into a cell (C57)
    on sheet Not A B and making sure that another sheet in the workbook is activated when the workbook is opened.

    =rangeid(('Not A B'!H46:M46, 'Not A B'!H47:M47))

    This results in:

    <pre>Active Cell: $C$57 Not A B XXX.xls Y
    rngArg: $H$46:$M$46,$H$47:$M$47 Y XXX.xls
    Range: $H$46:$M$46,$H$47:$M$47
    Workbook: XXX.xls
    Worksheet: Y
    Areas: 2
    Area: $H$46:$M$46
    Workbook: XXX.xls
    Worksheet: Y
    Area: $H$47:$M$47
    Workbook: XXX.xls
    Worksheet: Y
    </pre>


    As with cell D48, the wrong worksheet is associated with the areas.

    So unless I am misinterpreting the output of RangeID, there is a bug in Excel, independent of my UDF.


    <pre>Public Function RangeID(rngArg As Excel.Range) As String
    Dim rngArea As Excel.Range
    Dim rngResult As Excel.Range
    Dim strActiveCell As String
    Dim strWorkbook As String
    Dim strWorkSheet As String

    With Application.Caller
    strActiveCell = .Address
    Debug.Print "Active Cell: " & strActiveCell, .Parent.Name, .Parent.Parent.Name, ActiveSheet.Name
    End With
    With rngArg
    Debug.Print "rngArg: " & .Address, .Parent.Name, .Parent.Parent.Name
    End With
    Select Case strActiveCell
    Case "$C$48", "$D$48", "$C$57"
    With rngArg
    With .Parent
    strWorkSheet = .Name
    strWorkbook = .Parent.Name
    End With
    Debug.Print vbTab & "Range: " & .Address
    Debug.Print vbTab & vbTab & "Workbook: " & strWorkbook
    Debug.Print vbTab & vbTab & "Worksheet: " & strWorkSheet
    Debug.Print vbTab & vbTab & "Areas: " & .Areas.Count
    For Each rngArea In .Areas
    With rngArea
    Debug.Print vbTab & vbTab & "Area: " & .Address
    Debug.Print vbTab & vbTab & vbTab & "Workbook: " & .Parent.Parent.Name
    Debug.Print vbTab & vbTab & vbTab & "Worksheet: " & .Parent.Name
    End With
    Next rngArea
    End With
    Case Else
    End Select
    RangeID = Format(Now, "d mmm yyyy h:mm:ss")
    End Function
    </pre>


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

    Re: How do worksheet references get resolved? (2002)

    Your RangeId code works fine for me (XL2000).
    Whatever sheet is active when I run it, it nicely prints the proper sheetnames I've included in the function call.

    Two things come to mind:

    - XL2002 indeed has a calc bug regarding UDF's (should be in the knowledge base by now though)
    - Your code needs a cleaning job. Download codecleaner.exe from "the excel MVP page" below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do worksheet references get resolved? (2002)

    I guess that I have not been clear.

    >Whatever sheet is active when I run it, it nicely prints the proper sheetnames I've included in the function call.

    That's not the problem.
    The problem occurs when the reference to RangeID is on the second sheet, but the first sheet is automatically made the Active sheet when the workbook is opened.



    >- XL2002 indeed has a calc bug regarding UDF's (should be in the knowledge base by now though)

    The bug is not in UDF's, rather it is in the Areas collection, when there is a self-reference to worksheet 2 and worksheet 1 is activated automatically upon open.

    >- Your code needs a cleaning job. Download codecleaner.exe from "the excel MVP page" below.
    It's got nothing to do with my code.
    Using RangeID, the problem occurs as soon as the parameter is passed, i.e., before the code does anything.

    The resulting #Vaule! in my UDF is a result of the incorrect Area information resulting in wrong values.

    Next time I got bored, instead of playing Freecell, Minesweeper, or Solataire, I might make an example and post it at my web site.

    In the meantime, I'm done with this thread until I'm ready to announce such a posting.

  14. #14
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do worksheet references get resolved? (2002)

    See the following for a workbook that shows how to reproduce the problem.
    http://www.standards.com/OhMyWord/VBABugsFeatures.html

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How do worksheet references get resolved? (2002)

    You got bored that soon?

    I tried your workbook in Excel 97 SR2. It seems that the argument passed to the function refers to the active worksheet, irrespective of what is in the formula.

    The following seems to work OK for me - I changed the argument from a Range to a ParamArray:<pre>Public Function RangeID(ParamArray rngArg()) As String
    Dim dblSum As Double
    Dim rngArea As Excel.Range
    Dim rngCell As Excel.Range
    Dim strSheets As String
    Dim i As Integer

    strSheets = ""
    dblSum = 0
    For i = LBound(rngArg) To UBound(rngArg)
    Set rngArea = Range(rngArg(i).Address(External:=True))
    With rngArea.Parent
    strSheets = strSheets & "/" & .Name & "/"
    End With
    For Each rngCell In rngArea
    dblSum = dblSum + CDbl(rngCell.Value)
    Next rngCell
    Next i
    Set rngArea = Nothing
    Set rngCell = Nothing
    RangeID = strSheets & " Sum = " & Format(dblSum)
    End Function</pre>

    You must use single parentheses in the formula (since you're not building a union now):

    =RangeID('Sheet 2'!H2:K2, 'Sheet 2'!H3:K3)

Page 1 of 2 12 LastLast

Posting Permissions

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