Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Right' won't compile (2000)

    "Left" won't either!

    For some reason I keep getting "Wrong number of arguments or invalid property assignment" on the following simple code:

    Sub Test()
    Dim strTitle As String
    Dim strYear As String
    strTitle = InputBox("Enter Month and Year [in MM-YY format].")
    strYear = Right(strTitle, 2) <font color=red>'error message here</font color=red>
    MsgBox (strYear)
    End Sub

    For that matter, samples from the Lounge (and from Excel Help) give rise to the same message. References seem to be in order and more complex code runs fine. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> Any ideas?
    Gre

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

    Re: 'Right' won't compile (2000)

    Your code run fine on my XL. Seems like you have a missing reference. Check Tools | References in VBE
    Francois

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Right' won't compile (2000)

    Thank you Fran
    Gre

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

    Re: 'Right' won't compile (2000)

    I have only :
    Visual Basic for Applications
    Microsoft Excel 9.0 Object Library
    OLE Automation
    Microsoft Office 9.0 Object Library

    and the code run. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Have you try the code in a new empty workbook ?
    Maybe some Excel Guru have other suggestions.
    Francois

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Right' won't compile (2000)

    Merci tant bien Fran
    Gre

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Right' won't compile (2000)

    Thanks Jan Karel! I actually manually went through all of the steps that Rob automates - for the modules in question - before I posted.
    Gre

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: 'Right' won't compile (2000)

    Not that this will help, but I had exactly the same symptom caused by editing Macros in a workbook in both Excel 97 and Excel 2000. I never did get to the root cause, had to restore a backup and make sure we never edited the Macros with Excel 2000. It looks like the first time it was opened in Excel 2000 it must have updated a library reference that affected all the string functions.

    StuartR

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Right' won't compile (2000)

    Good thing to know. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    FWIW I'm now pretty sure that it was simply a case of "personal.xls overload". <UL><LI>An initial symptom was reporting bogus "ambiguous procedure" names after I'd split off all the utility procedures & loops into a separate module. This continued even after a "code clean" (see earlier in this thread). Adding references to the other module has ceased being (strictly) necessary now that a clean file is set up. <LI>Another symptom was, on pressing Alt+F8, seeing the module name prefixed to some of the newer module procedures in the dialog box.[/list]The rest of the code ran fairly quickly and - for maintenance purposes - I'd stripped out as much dead wood as I could find.

    I'm sorry I can't share details regarding the size of the file. While I still use most/all of the resource saving tricks necessary back when "white-outs" were a daily issue, bottlenecks only arise occasionally. So I seldom check file size!

    edited by unkamunka 27-02-02 21:26 UTC to add P.S.
    P.S. I suspect that the "file size" issue is really an issue of number of lines of code. As I write VBA inside the VBE, it doesn't seem worthwhile providing an estimate unless someone thinks it's worth knowing for the record! <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>
    Gre

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

    Re: 'Right' won't compile (2000)

    Yes, you should keep your modules below approximately 1600 lines of code.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Right' won't compile (2000)

    Jan Karel, you're a persuasive man! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    The issue appeared to be the amount of code in the file - an all modules. When it started "choking" on the RIght function, total code lines were about the 6,500 mark (plus three forms). For the record, the amount of code I moved to a new file - curing the problem - was about half.
    Gre

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

    Re: 'Right' won't compile (2000)

    Sorry, I misremembered, lines should have read about 6400, not 1600.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: 'Right' won't compile (2000)

    Hi,

    You'd benefit from Rob Bovey's code cleaner, find it at
    http://www.bmsltd.ie/mvp

    Edited Mar 13th 2004 to update link
    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
  •