Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Curious 'Compile Error' (2003 SP1)

    I have a macro in my Personal.XLS worksheet that doesn't do anything outrageous or slick but, having worked sweetly, now doesn't, giving a "compile error" on the second line:

    <font face="Georgia">Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select</font face=georgia>

    The VBA editor highlights the "Selection" keyword on the second line when informing me of the error. Curiously, when I store the macro in a module attatched to the workbook I want to run it against, it works without complaint. (All it does is make a copy of the active sheet from the original workbook, applies some formatting along with a couple of filters and hides a few column ranges.

    When I first started at my current company, I had to use my predecessor's UserID and login details while my NetID was being created. The wheels ran exceedingly slowly and it was several weeks before my own UserID was ready. The macro in question was written when I was impersonating my predecessor and saved to a Personal.XLS associated with his Documents and Settings folder, (we use Windows XP, SP1). When my UserID became available, I moved the Personal.XLS file in it's entirety to the corresponding location in my Documents and Settings folder, (He hadn't created any macros so all of the contents were mine and I wanted to go on using them). While the macros were available to me in my new incarnation, I belieive it was at this point, (and without any amendment to the macro), that it began to misbehave.

    I moved it into the workbook I want to run the macro against, where it worked, and didn't think any more about it. Now the time has come when I want to run the macro against several workbooks and, so as not to keep multiple copies, (which will inevitably lead to mulitple versions), I want to use it again from Personal.XLS, where it still stubbornly refuses to play ball.

    Thinking the Personal.XLS file might be retaining some reference to the other User ID, I moved and renamed it, created a Personal.XLS of my own from scratch, and copied ther macros from the original into the new one, by cutting and pasting the VBA text from module to module). It still fails at the same point with the same error message.

    I must have missed something, but I can't think what. Can anyone give me any insights?
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

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

    Re: Curious 'Compile Error' (2003 SP1)

    Your code works for me, whether from a module in the active workbook, or from a module in Personal.xls. Does the following variation (needing only one line) work for you?

    Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Select

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Curious 'Compile Error' (2003 SP1)

    Thanks for your reply, Hans. The problem seems to be related to the Selection keyword. I added your line so the first few lines now look as follows:

    <font face="Georgia">'Range("A1").Select
    'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Select

    Selection.Copy
    Workbooks.Add</font face=georgia>

    (n.b. first two lines now commented out and replaced by your suggested alternative) Now, If I try to step through or run the procedure, your line is processed without comment, but I get another compile error, but this time focussing on the Selection keyword on the line after yours. I dare say there may be alternatives to using the "Selection" keyword, similar to your method of dispensing with it in the first line, but it would be tedious in the extreme trying to think of alternatives instead of using it as freely as I should be able to.

    I'll adopt your one line to replace my two, but I'm still puzzled...
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Curious 'Compile Error' (2003 SP1)

    Would it be possible to post the entire macro, sothat we can see what the actions are from the beginning? It may help to see under which context the macro is operating!
    Regards,
    Rudi

  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: Curious 'Compile Error' (2003 SP1)

    Why even select?
    I try to avoid selecting as much as possible:

    Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy

    Though if you are not pasting it right away, you have to be careful, excel will sometimes clear the clipboard. You can copy and paste in one step, by adding the destination range right after it (they can be on different sheets)

    You might want to add the workbook first, then copy the range from the other sheet to whatever the source...

    Steve

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

    Re: Curious 'Compile Error' (2003 SP1)

    This kind of bogus compile errors can be caused by two things:

    1. A corrupt VBA project
    2. Missing references.

    To start with 2: Open the VBE, activate the project and choose Tools, references. Are any noted as MISSING? if so, try to uncheck those.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Curious 'Compile Error' (2003 SP1)

    The macro in it's entirety is as follows:

    <font face="Georgia">Sub Bertify2()
    '
    ' Revised Bertify Macro
    '
    '
    ' First, create a copy of the active sheet of the Pan-EMEA.
    'Range("A1").Select
    'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Select

    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    ' then reapply formatting
    Cells.Select
    Cells.EntireRow.AutoFit
    ActiveWindow.FreezePanes = True
    Application.CutCopyMode = False

    ' apply filters to remove the data Bert isn't interested in
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="UK"
    Selection.AutoFilter Field:=15, Criteria1:="C"
    Range("E:F,J:K,M:M,O:O,Q:Q,U:X,Z:AE,AG:AG,AI:AL"). Select
    Range("M1").Activate
    Selection.EntireColumn.Hidden = True
    Range("F2").Select
    ActiveWorkbook.SaveAs Filename:= _
    "Documents and Settingswzm73kMy DocumentsBlahBlah UK Pan-Emea Extract (" & Format(Date, "dd-mmm-yy") & ").xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    End Sub</font face=georgia>

    As for the use of Selection, I don't know VBA well enough to a) know there's an alternative method and [img]/forums/images/smilies/cool.gif[/img] implement it in a timely fashion. The root of a lot of my macros tends to be a recording, which can be tweaked and amended to make it truly bespoke. Selections are readily included in macros that are recorded.

    As for Jan Karel's suggestions, the module is newly created and yet still behaves in the same manner as the old one, so I think coruption isn't an issue. There are only four, (of many) references checked are "Visual Basic for Applications", "Microsoft Excel 11.0 Object Library", OLE Automation" and "Microsoft Office 11.0 Object Library". All show valid file locations. There are no references to anything Missing. Also, if that was the case, wouldn't it affect the macro from wherever it was run?

    Thanks very much for the input.
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

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

    Re: Curious 'Compile Error' (2003 SP1)

    The PROJECT might still be corrupt though.

    I suggest exporting all modules to text files, creating a blank workbook and importing them into the new workbook. Then save the new workbook.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Curious 'Compile Error' (2003 SP1)

    Jan,

    No joy, I'm afraid. Exported the contents of the module and removed the project, (by deleting the Personal.XLS hidden workbook). Closed down Excel and re-booted the machine, (just to be cautious), and recorded a new macro to create a new Personal.XLS. Re-imported the module file and tried the procedure again. Same result - the procedure fails on the first "Selection" keyword and reports a compile error. The same macro works fine when copied into a module in the target worksheet.

    My head's beginning to get sore from the scratching...
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

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

    Re: Curious 'Compile Error' (2003 SP1)

    This is definitely NOT normal behavior. "Selection" is a standard object in Excel VBA, you should be able to use it in any module.

    See if <!post=Systematic Approach to Behavioral Problems in XL,290455>Systematic Approach to Behavioral Problems in XL<!/post> helps, and/or try Help | Detect and Repair.

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

    Re: Curious 'Compile Error' (2003 SP1)

    Sometimes an export/import isn't enough.

    Try this:

    Open notepad and copy/paste the code into that.
    Now open a fresh workbook and paste it back from notepad (make sure you select and copy from notepad deliberately, otherwise you;re pasting the same clipboard object back into your VBE again). Now it should work.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Curious 'Compile Error' (2003 SP1)

    In addition to Jan Karel's suggestion, one other thing to look for. Check to see if there is a DIM statement anywhere that DIMs a variable named Selection.
    Legare Coleman

  13. #13
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Curious 'Compile Error' (2003 SP1)

    Well, who'd have thought it?!?!? It sounded rather counter-intuitive, ( to me, at least! <img src=/S/baby.gif border=0 alt=baby width=15 height=15> ) but your solution seems to have worked perfectly.

    I copied and pasted the contents of the module from Personal .XLS into a notepad txt file and deleted Personal.XLS. Then I rebooted the PC, re-created Personal.XLS and pasted the listing back into the new module. Closed down Excel, (answering "Yes" to the prompt about saving changes to Personal.XLS) and restarted it.

    The macro now sails through the syntax check and processes the procedure lines as it should.

    Many thanks for your help. I guess this is one of those situations where, once you've been through it, the solution is so odd that you don't forget it.

    Another one for the armoury!
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

  14. #14
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Curious 'Compile Error' (2003 SP1)

    I spoke too soon, I'm afraid. The macro I was testing was the one embedded on the workbook itself, NOT the one from Personal.XLS. Testing this one produces the same error. I've now run the copy and paste to notepad, delete personal.XLS, recreate and re-paste scenario several times and the error is consistent. There were a few oddities, however.

    On several, but not all, occasions, after pasting the macro listing from the notepad file into the module window and saving it, I get an error message when exiting Excel, (appended below). This isn't accompanied by an entry in the Application folder of Event Viewer, nor is a Dr Watson log created. Does anyone know of a log file anywhere that I could find out more about the error.

    Secondly, (and this may be normal behaviour, I'm not sure), after recreating the Personal.XLS and pasting the listing back into the module, I save and close the VBA window and then Excel. If I then reopen Excel and record another macro, the new macro is saved to a newly created Module 2 in Personal.XLS. At first, I thought this was because I was recording the new module with the VBA editor window open and minimised in the background, but subsequent tests have shown this not to be the case.

    The next thing I'm going to try is to remove all other macros from the Personal.XLS module other than the one I'm interested in and see if that makes a difference. There aren't many others and they're mainly fragments, (though complete in themselves) testing functions and operations to be pasted into larger macros.

    One last thing: If I choose Tools|Macros, those in Personal.XLS are prefixed by the sheet name and exclamation mark, (i.e. PERSONAL.XLS!Bertify2), a situation that doesn't look correct but that I can't remember is normal or not.

    To recap, I've already removed all of my third-party add-ins, run Excel in repair mode and recreated Personal.XLS in the manner described above and yet the problem still occurs. As has been seen by my premature assumption of success, the macro runs to completion successfully when embedded in the worksheet I'm running it against.

    Can anyone shed any light??
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Curious 'Compile Error' (2003 SP1)

    >>>>(i.e. PERSONAL.XLS!Bertify2), a situation that doesn't look correct but that I can't remember is normal or not.

    That is normal, as it is a macro refered to in an external source! (Always prefixed with the WBook name!)
    Regards,
    Rudi

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
  •