Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Worksheet.Calculate Method Failed Err Msg (2000)

    User is getting the error msg:
    Method 'Calculate' of object '_Worksheet' failed

    The code in the workbook that triggers this error (the line that is highlighted whne clicking the 'Debug' button) is quite simple:

    Worksheets(TargetSheet).Calculate

    The value TargetSheet is the name of the active worksheet.

    Interestingly emough the client only get's this err msg when the code is executed while running Excel on a Citrix Server. When the code is executed on the users local computer it works without issue. When I execute the code myself on my workstation I too have no problems. In fact I can see no reason for any error in this workbook.

    I do have one thought that some of you may be able to comment on or add to, does the fact that the error msg does not list the name of a worksheet but shows just an underscore character indicate that the .Calculate method is being called on a balnk worksheet name? In otherwords if the variable TargetSheet was set to Sheet1 and the .Calculate method raised this error would the message still look like the one above or would it include the sheets name like this:

    Method 'Calculate' of object 'Sheet1_Worksheet' failed

    If the answer is yes, the sheets name would be included as shown above then the answer to this err msg is that TargetSheet has no sheet name. I would imagine that would raise an error. If I am correct then the next question is why does TargetSheet not have a name.

    Thoughts?

    Thanks
    Ed

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Worksheet.Calculate Method Failed Err Msg (2000)

    Ed - have you tried stepping through the code, with either a watch variable set on "TargetSheet" or the locals window open to see what value it has when the code encounters this line? What happens if you replace <font color=blue>Worksheets(TargetSheet).Calculate</font color=blue> with <font color=blue>ActiveSheet.Calculate</font color=blue>?

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Worksheet.Calculate Method Failed Err Msg (2000)

    Dean,
    I have not tried steping thru the code because I don't have access to the clients Citrix server. I also can't change the code because it would be in violation of our company's policy towards editing code used by clients. I could modify the code on my own machine for testing purposes but I'm noy suppose to place any altered code on a clients system. Any other thoughts?

    Thanks
    Ed

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet.Calculate Method Failed Err Msg (2000)

    Its kind of tough without seeing the rest of the code and the workbook. One guess might be that TargetSheet contains the name of a chartsheet not a worksheet.
    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Worksheet.Calculate Method Failed Err Msg (2000)

    If targetsheet were a chart sheet, I would expect a subscript out of range error, since the sheet name is not a worksheet. This should be triggered before the calculation method is even "called"

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet.Calculate Method Failed Err Msg (2000)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> Good point, and one which I should have caught since my first thought was that targetsheet did not contain a valid sheet name and gave that up for the same reason.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Worksheet.Calculate Method Failed Err Msg (2000)

    Other thoughts?

    Would your client be able to step through the routine themselves, perhaps with you on the phone?
    If so, would they be able to set up the VBE to show the locals or watch window?
    Would they be able to set a watch or breakpoint for you?

    Has this problem just come to light recently, or has it been happening since the code was put into production?

    It seems that the problem is either related to the Citrix environment, or that the file they are running has been changed from the one that you have, since you are not encountering the same error. Could they send you a copy of the file they are having trouble with? - If you get the same error then maybe something has changed there - they might have changed a worksheet name, a named range, or something else that the code is relying on. If they can run the same file without errors when NOT running on Citrix, then that may point to a solution, or at least identification of the area to look at.

    If your client can't do any of the things you need, can you have them run a modified file for testing purposes only, with changes in objects selected, etc or loop counters recorded in a new file? I am a bit confused about the policy to not modify the code that clients are running - I assumed that you (or your company) were the original source of the code: what is your normal procedure for moving code into a client environment?

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

    Re: Worksheet.Calculate Method Failed Err Msg (2000)

    One thing that comes to mind is that you haven't specified what workbook the code refers to.
    Try preceding the worksheets(...) with the workbooks qualifier.

    Question: what kind of module does the calculate line that fails live in?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Worksheet.Calculate Method Failed Err Msg (2000)

    First let me apolagize for taking so long to reply to everyone's replies to my query. I have been on the road, traveling for business, for many weeks and I am just now getting to where I can follow up with this post.

    Second I'd like to thank everyone for their input; it's clear that everyone that has posted has taken time to give this some thought and provide some great suggestions. I will try to address everyone's questions as well as provide comments on the comments so as to clarify some questions about this issue:

    1) The Excel Addin was created by my company and while our development dept can change it I am not suppose to. I haven't approached our dev people about making a special build for this because they frown on that and are highly unlikley to do it anyway because they are all so busy with normal day to day tasks.

    2) Asking the client to step thru the code or perform any other development like task is not a good idea in this case due to the lack of technical know how on the clients part. Whatever I have the client do must stay within the bounds of working in a spreadsheet.

    3) The error is reproduceable only when Excel is loaded and used on the clients Citrix server. This happens with any XLS file that the client uses for my companies product and that is done on the Citrix server.

    4) The workbook contains no charts or graphs or any other type of object like a pivot table. There are only worksheets that contain either literal values, cell refs or formulas. No other custom code is used or are there any external cell refs or refs to other code or objects.

    5) The code module that contains the line: WorkSheets(TargetSheet).Calculate is within a regular code modul and not a class module.

    I hope that's enough to answer evryone's questions who has been kind enough to offer up some suggestions. I look forward to your responses.

    Thanks Again to everyone!
    Ed

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

    Re: Worksheet.Calculate Method Failed Err Msg (2000)

    Could you show us the lines of code before the calculate command?
    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
  •