Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    1004 runtime error (2000 sp-3/ 98SE)

    I can't determine the source of this error:
    Run-time error '1004':
    Method '~' of object '~' failed


    The same code works fine from XL VBA, but the problem arises when it runs from an activeX:
    <code>
    <font color=black><font color=blue>Sub</font color=blue> CreateNameScopes(nsNames <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>)

    <font color=blue>Dim</font color=blue> nme <font color=blue>As</font color=blue> Range
    Dim ns As NameScope
    MsgBox "Running CreateNameScopes" '<font color=red>OK here</font color=red>
    MsgBox Names("Quote").RefersTo '<font color=red>Error 1004 here</font color=red>
    <font color=blue>For</font color=blue> <font color=blue>Each</font color=blue> nme <font color=blue>In</font color=blue> ThisWorkbook.Names(nsNames).RefersToRange
    MsgBox "In loop"
    <font color=blue>Set</font color=blue> ns = New_NameScope(nme)
    m_ColName.Add ns
    <font color=blue>Next</font color=blue> nme

    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue></font color=black>
    </code>
    The line where the error occurs is just a test on a "known" range name.

    Any thoughts on how to tackle the source of this, please?

    Alan

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

    Re: 1004 runtime error (2000 sp-3/ 98SE)

    What happens if you use

    MsgBox ThisWorkbook.Names("Quote").RefersTo

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1004 runtime error (2000 sp-3/ 98SE)

    Same result Hans. Sorry, I made a bit of a dog's breakfast out of that, trying to format then edit it. The essential lines should read:

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black> MsgBox "Running CreateNameScopes" <font color=448800>'<font color=red>OK here</font color=red></font color=448800>
    MsgBox ThisWorkbook.Names("Quote").RefersTo '<font color=red>Error 1004 here</font color=red>
    <font color=blue>For</font color=blue> <font color=blue>Each</font color=blue> nme <font color=blue>In</font color=blue> ThisWorkbook.Names(nsNames).RefersTo
    </font color=black></code></div hiblock>
    Alan

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1004 runtime error (2000 sp-3/ 98SE)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Well, if you're stumped it can't be a trivial -> moderate problem... now I'm worried!
    All the names are just "garden varieties", like Insurer=Contacts!$A$2:$C$20
    I had a look at all of them with Name Manager, but can't see anything "odd".
    Some hits on Google tend to suggest it's associated with referring to ranges, but I even get the same result with something very "vanilla" like:
    MsgBox ThisWorkbook.Names("Insurer").Name

    I hope you're right about more forthcoming suggestions. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    Alan

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: 1004 runtime error (2000 sp-3/ 98SE)

    Shot in the dark: <!mskb=177527>Microsoft Knowledge Base Article 177527<!/mskb> appears to be for Excel 97, but considering your OS, you might try Method 2.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: 1004 runtime error (2000 sp-3/ 98SE)

    I'm afraid I'm stumped. I hope someone else has a suggestion.

    Added: you could try Pieterse's Name Manager utility to see if the offending name has special characteristics.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: 1004 runtime error (2000 sp-3/ 98SE)

    Have you tried using ..
    ActiveWorkbook
    instead of..
    ThisWorkbook

    It seems to me that if you are running it from an activex the problem could be that you aren't in the workbook that you think you are.
    Hence the 1004 subscript out of range message - it's telling you the name aint there.

    zeddy

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1004 runtime error (2000 sp-3/ 98SE)

    I just finished (finally) solving it, as a matter of fact. As you and Hans pointed out, and I also felt, the code was trying to access something that wasn't available. Having had no success with ThisWorkbook and ActiveWorkbook, I restructured some procedures to use an explicit reference to the "calling" workbook, then Set wbk = .... and the problem statement ran as planned:

    For Each nme In Wbk.Names

    Beats me why it didn't work using the more direct referencing. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Still, I'm happier that it is actually working.

    Alan

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1004 runtime error (2000 sp-3/ 98SE)

    Thanks John, but none of the causes in that article seemed applicable. It's obviously a very general-purpose error message, and I did finally hit on a "fix".

    cheers
    Alan

Posting Permissions

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