Search code examples
vbaexcel-2007ms-access

Specifying worksheet name required when identifying a cell by name in Excel 2007

Initially asked in SuperUser: https://superuser.com/questions/206822/specifying-worksheet-name-required-when-identifying-a-cell-by-name-in-excel-2007.

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.

Solution

  • This is what I found.

    This uses the name of the sheet after a fashion:

    Dim xlSheet As Excel.Worksheet
Set xlSheet = ActiveSheet
xlSheet.Range("rngOneCell") = 300

    With this syntax, I believe you can avoid naming the sheet:

    Range("rngOneCell") = 100