Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel97 Strange behaviour compilation error

    Happy New Year everyone!

    I wonder if you can help me? I'm a VB developer based in Darlington, UK developing and supporting for a world wide Retail organisation.

    I've produced a spreadsheet that has extensive data validation and error checking, created using a mix of Excel's Data Validation feature and VBA code.

    Of the 40 or so locations across the world where this is in use, about 30% have reported 'Compilation error' messages when they try and open the workbook.

    I got a 'tame user' in Gibraltar to play with a non password protected version of the same workbook and he reported that the code fails on the following lines taken from various different procedures within the application:

    Workbook Open:
    dDeployDate = Format(#1/30/02#, "dd/mm/yy")

    Worksheet Change:
    Target.Value = UCase(Target.Value)

    ErrorChecking Module:
    MsgBox prompt:="Please ensure you type in a Cash Brought Forward " & Chr(13) & Chr(10) & _
    "figure before proceeding further", _
    Title:="Cash Brought Forward"

    The problem also manifests itself on both Excel 97 SR1 and SR2 (as my man in Gibraltar has both these versions)

    I rather think the problem may be in my use of functions like Format, UCase and Chr but I'm not sure how to diagnose this properly and where to start fixing it?

    Any ideas would be gratefully received.

    Many thanks

    Nigel Bell

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

    Re: Excel97 Strange behaviour compilation error

    Straw clutching here, but this looks similar to problems in VB6 reported <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=vb&Number=104305&page=1 &view=expanded&sb=5&o=0&fpart=>here</A>.

    I'd have your domesticated user check from the VBE editor for references to any Tools, References or Tools, Additional Controls. Each one you click on will give you a file location- then check the time/date stamp of any of those controls (you may well find a missing reference if you're lucky. That will be a lot easier to fix that aa mismatching one).

    If you have access to Office Developer edition, you will have a "package and deployment wizard" which will enable you to send out updated dlls or ocxs. Perhaps.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  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: Excel97 Strange behaviour compilation error

    If I may join Geoff in clutching at straws, there may be regional language issues involved here. For example dd/mm/yy may not have any meaning in some regions. or "Prompt::=" etc may need translating.

    Maybe be worth exploring.

    Andrew C

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel97 Strange behaviour compilation error

    To avoid International settings issues, I would use
    dDeployDate = Format(DateSerial(2002,1,30), "dd/mm/yy")
    Francois

  5. #5
    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: Excel97 Strange behaviour compilation error

    Besides all the other great suggestions, I know from experience (and pain) that Excel97 requires WorkbookFunction.Format() and not just Format() like Excel2000 accepts. Ucase() by itself works fine in xl97 so I don't know what might be the problem there, however if some of the references are missing (as someone else pointed out), that would definitely generate that error. (I've seen it myself and now check for any missing references at workbook open. There was a recent poster who had this same error.)

    You might want to review all the WorksheetFunction.BLAH() and see if you you are using any of them. I tend to use the old style xl97 style since that still works in xl2000 and I don't have to write separate code (if xl97 then blah else blah).

    Also, if you happen to use FormatCurrency() in xl2000, there is no such thing in xl97 you need to use Format() and define the currency style yourself. These things and many others have been a constant pain for me as well in trying to keep one set of code that works in both xl97 and xl2000. (I don't want to know what might pop up in XP.)

    Deb <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

Posting Permissions

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