Excel 2002 SP2 VBA 6.3 'RefersTo' Method fails

[Robert L. Porter]

I have a macro in an Excel workbook which is intended to
automatically update some information from a master
template. This update may require resizing named ranges.
Based upon a Sams book (I don't have the book with me at
the moment so I can't give the reference) on VBA 5, I use
the following code:

With Range("RangeName")
strNewRge = "=" & .Parent.Name & "!"
strNewRge = strNewRge & Cells(.Row, .Column).Address _
& ":"
strNewRge = strNewRge & Cells(.Row, ColCount).Address
Names("RangeName").RefersTo = strNewRge
End With

When the last line is executed (the line
containing "RefersTo"), I get an error message.

"Run-time error '-214741848 (80010108)':
Method 'RefersTo' of object 'Name' failed"

Looking at the value of "strNewRge" shows it to contain
the correct new range limits, properly formatted for range
definition.

A search in the Microsoft KnowledgeBase for this error
number brings up a post-sp2 hotfix which is supposed to
fix this error; however, applying this hotfix does NOT fix
the error, and it still persists.

Environment: Windows 2000 SP2 (5.00.2195)
Excel 2002 (10.4302.4210) SP-2
Visual Basic 6.3, Version 9108, VBA: Retail 6.3.8863

Any suggestions?

Bob Porter

 

[Rob Bovey]

Hi Bob,

My guess would be a problem with the following line of code:

strNewRge = "=" & .Parent.Name & "!"

All manner of sheet tab names can require you to surround the name of the
worksheet in single quotes. If it's required and you don't do it then your
RefersTo string will be invalid. If it's not required and you include the
single quotes anyway, it won't hurt anything, so I always recommend
including them just to be safe. Try it like this instead:

strNewRge = "='" & .Parent.Name & "'!"

Note the single quote immediately after the equal sign and immediately
before the exclamation point.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Robert Porter]

Thanks for your suggestion. Unfortunately, it did not work. I really
don't think the problem is in that line of code, but rather in something
that has to do with the execution of the 'RefersTo' method.

This code runs on an installation in another facility. We have updated
the Visual Basic in the facility where it fails to match the Visual
Basic in the other facility, but the code still fails. Right now the
only difference we can see in the two installations is that the one that
works is running on Windows 2000 SP 4, whereas the one that fails is
running on Windows 2000 SP 2. A technician from the corporate support
organization is currently doing more research to see if we can find the
fix that will make the difference.

Bob Porter


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!