Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

I don't know if this is a change in the version, but in 2003 this used to work in Access VBA:

Dim xlSheet As Excel.Worksheet
xlSheet.Range("RangeName").Value = 100

Previously I did not need to identify the worksheet because "RangeName" was unique. Is Excel 2007 not considering this a unique value and requires that the worksheet be specified?

[EDIT] Here is the error in Access 2007 VBA: "Error 1004 (Method 'Range' of object '_Worksheet' failed." Trying to avoid naming the worksheet.

I used the Name Manager in Excel. The name is unique and the scope is workbook.

alt text

When I tried to Edit Name, it does not allow the scope to be changed. alt text

Note: This workbook is Template that allows macros and the names are a mess.

share|improve this question
Where to you assign the worksheets? Like Set xlSheet = ... – dkusleika Nov 4 '10 at 13:49
@dkusleika - Although for this line of code that would work, I prefer not to since I'm referencing a Workbook level name and I'm doing this more than once. – JeffO Nov 4 '10 at 14:18
Maybe I didn't say that right. You have an xlSheet variable but you don't set it to anything. Based on the code you show, xlSheet would be Nothing. You should get an error 91 for that, but I'm just trying to understand your code. – dkusleika Nov 4 '10 at 17:04
@dkusleika - My guess is prior to Office 2007, this object was defaulted to the worksheet of the Name Label. Now the scope can be set at the worksheet level which would allow duplicate Name Label values and limit Excel 2007's ability to apply a default worksheet. – JeffO Nov 4 '10 at 18:50
1  
Is the worksheet a member of a collection that can be addressed by index? – David W. Fenton Nov 7 '10 at 3:00

1 Answer

up vote 1 down vote

Is RangeName definitely a unique range and defined as a Global name, not Local to one or more worksheets?

Jan Karel Pietersen's Name Manager is a great free tool for viewing, managing and editing names which may help sort out if this is an issue with the name rather than the code.

http://www.jkp-ads.com/officemarketplacenm-en.asp

share|improve this answer
Thanks for listing Name Manager. I'm going to try the one in Excel 2007 first. – JeffO Nov 4 '10 at 13:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.