Results 1 to 12 of 12
  1. #1
    cingil
    Guest

    Excel 97>2000 (Excel 2000)

    I had emailed woody directly on this and he suggested i provide a sample of the code I have a problem with -- and a helpful soul might try to assist me...I would very much appreciate it!!!
    We have a spreadsheet application in Excel 97, using a significant amount of VBA code that we use for our employee expense processing.
    We need to upgrade to Microsoft Office 2000 suite to satisfy a business need ASAP.
    However, the application fails in various points in the VBA code, which I am trying to decipher and fix.
    Are there commands/verbs/syntax that worked in Excel 97 that no longer work in Excel 2000 - and if so, is there somewhere that would list these?
    The process first fails on a line that included a comment at the end .... REM xxxxx
    Eliminating that line let the process flow a little further, but then if fails on some other logic that looks like it should work, unless some of the rules have changed. The error message gives no meaningful clue... error 91 object variable or with block variable not set..
    I would appreciate any help or direction to other resources you know about that could help me debug this processing.
    I am attaching the particular code section that is currently failing for your review.
    If this all seems unclear, I would be happy to provide you with additional information.
    thanks
    Cindy Gilbert

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97>2000 (Excel 2000)

    I don't see any attachment

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel 97>2000 (Excel 2000)

    Your attachment does not appear to have stuck. Could you try again ?

  4. #4
    cingil
    Guest

    Re: Excel 97>2000 (Excel 2000)

    Please forgive my earlier post with no attachment..I will do better in the future..I am including the code instream to make sure it makes it this time!
    *********************************************
    Sub GenerateSummaryButton()
    Dim rangeName, sortRangeName

    Range("DialogResponse").Value = "CANCEL"
    UserInfoForm.InputName.SetFocus
    UnLockSheet
    UserInfoForm.Show
    If Range("DialogResponse").Value = "OK" Then
    If Range("ExpenseFirstRow").Value <> Range("ExpenseCurrentRow") Or _
    Range("GuestFirstRow").Value <> Range("GuestCurrentRow") Or _
    Range("AgriFirstRow").Value <> Range("AgriCurrentRow") Then
    wsCollector.Range("A" + Trim(Str$(Range("CollectFirstRow").Value)) + _
    ":I" + Trim(Str$(Range("CollectFirstRow").Value + 1200))).ClearContents
    CollectExpenseDetail
    CollectGuestDetail
    CollectAgriDetail
    rangeName = "A" + Trim(Str$(Range("CollectFirstRow").Value)) + ":I" + Trim(Str$(Range("CollectCurrentRow").Value - 1))
    sortRangeName = "A" + Trim(Str$(Range("CollectFirstRow").Value))
    Call wsCollector.Range(rangeName).Sort(wsCollector.Rang e(sortRangeName))
    GenerateSummary
    Range("SummaryCreated").Value = "Yes"
    End If
    End If
    Range("SubTitleDate").Value = Now
    LockSheet
    End Sub

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97>2000 (Excel 2000)

    This calls a lot of other routines by the look. I suspect you really need to attach the spreadsheet.

    One common problem is that when a spreadsheet is attached, and you then preview the message, the attachment is lost.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97>2000 (Excel 2000)

    Yikes! This is a big problem and I feel your pain!!! There are many differences in VBA for xl97and xl2000 and none are documented. My application must work in both so I have both Excel versions installed and constantly shift back and forth between the two for testing.

    Sorry to say it's trial and error (and a lot of profanity helps too). One benefit though is that once you get code to work in both versions you're much more knowledge about the Excel object model!! My recommendation would be to use the de###### and step through the code very carefully. Also add lots of error handling so that you have a good idea of where in the code it failed.

    This will also be the case with xl95 and XP and 2002 so I've tested only for 97/2000 and if the user runs my application with another version, I warn them that it hasn't been tested (and don't call me crying about a problem at 2am). Really, it's impossible to test any application of a reasonable size on five versions of Excel to proclaim that it works.

    Deb <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  7. #7
    cingil
    Guest

    Re: Excel 97>2000 (Excel 2000)

    I tried to attach and send the entire spreadsheet as suggested.
    However, the file is apparently too large - I deleted a lot of the company specific table entries, but it is still around 500K.
    Is there another way I could get this to you?
    I very much appreciate you all for being there and willing to help me out on this!
    CIndy

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel 97>2000 (Excel 2000)

    Have you the facility to compress the file with WinZip, or some such utility.

    You can download an evaluation copy of WinZip from <A target="_blank" HREF=http://www.winzip.com/>Here</A>
    Hopefully that will get the size down to 100k or less.

    Andrew

  9. #9
    cingil
    Guest

    Re: Excel 97>2000 (Excel 2000)

    <P ID="edit" class=small>Edited by Eileen on 31-Aug-01 10:17.</P>Well, I have tried deleting some more superfluous verbiage and then zipping the file using the maximum compression and it is still 127K.
    Any thoughts??!!
    Cindy

    Attachment added by me. --Eileen

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel 97>2000 (Excel 2000)

    Cindy,

    The code for the Sub GenerateSummaryButton() procedure runs without any error in my version of Excel 2000. Could you specify exactly where you get an error, and the error is.

    Is there some procedure that should be run before that one that might have an impact ?

    Andrew C

  11. #11
    cingil
    Guest

    Re: Excel 97>2000 (Excel 2000)

    Sorry not to have given better idea of the what you need to do to get the problem...
    You need to enter several expenses (use the expense sheet tab and button add expenses) - it needs to be several different expenses (several add expenses).
    Then go to the summary tab and button enter personal information...after entering personal info, hit ok
    This is where it fails and it is the error 91 object variable or with block variable not set
    The code is in the generate summary button procedure and is about the 11th line down which starts with ws.collector.range....
    Note - I believe I eliminated the 1st error I was getting - which was on a comment/REM statement explaining that an EmplNum entered greater than 999826 was too large - this is in Private SubOKButtonClick... If not, eliminate this REM and you should get what I am gettting.
    I am getting this running MSOffice 2000 version 9.0.2720.
    I hope I am giving you better information.
    Cindy

  12. #12
    cingil
    Guest

    Re: Excel 97>2000 (Excel 2000)

    I forgot to mention a few things that might help -
    In our production environment, the expense sheets (expense, guest, agribusiness) and the summary sheet are protected, so you cannot just enter the information directly into the sheet as you would normally complete a spreadsheet. You have to use the buttons (add expense, enter personal info, etc) and have the VBA generate the code in the spreadsheet and summary pages.
    I hope I have not made a lot of extra work trying to diagnose this -- the error 91 probably doesn't happen if you directly enter the data.
    thanks again for all your help
    Cindy

Posting Permissions

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