Runtime Error 1004: Method ‘Cells’ of Object ‘_Global’ failed

**NeoPa** · May 16 '08, 07:58 PM

As a full member now, you should know that we expect your code to be posted in [CODE] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

ADMIN.

**NeoPa** · May 16 '08, 08:06 PM

If you mean line #18 (it's so much easier working with code in tags) then I suspect you need the . at the start.

Code:

.Range("A1:L1").Select

I suspect (not absolutely sure) that Range() is a shortcut for Application.Ran ge(). As the application in this instance is Access rather than Excel, it doesn't work.

The . ensures the full and correct reference (via the sheet rather than the application but nevertheless).

**Stewart Ross** · May 17 '08, 03:53 PM

Hi. NeoPa is right - your code would work well directly in Excel, where the likes of the references to the current Selection and Range do not need further qualification, but not when used from Access (or any other Office application) as an automation server. You will need to explicitly refer to ranges and so on for your code to work reliably. There is an explanation why in the later posts of this thread, where the original poster was running into errors for similar reasons.

When Range is referred to without qualification in Excel code it is the ActiveSheet property which is normally implied, although a range can actually include multiple worksheets. As NeoPa has said, you need to use .Range (referring to the sheet object you set in your code) explicitly within a With statement to ensure that you are referring to the correct worksheet object.

Back on the implied properties, ActiveSheet itself implies that there is a current ActiveWorkbook, another Excel property. When running Excel as an automation server there can be multiple instances of workbooks and worksheets open simultaneously within that automation instance, so trying to imply which of these applies causes all sorts of head-scratching errors in what is apparently valid code that works in Excel itself. But in Excel it is running in a single workbook instance only.

From the repeated references to the current Selection in your code I would guess that you have copied code created by the macro recorder at some stage. This works fine in Excel, but for the reasons explained above and in the linked thread it is unreliable at best when using Excel as an automation server, and can introduce very trying errors apparently at random.

To give an instance of how misleading the use of Selection can be, I used the macro recorder to record the actions involved in setting the text size for a comment box in a cell to a larger size (the default was too small) and changing the size of the comment box itself. In Excel all of this worked perfectly. In Access the self-same code worked on the first object, but not for any others. Sometimes it would fail with an error message, sometimes it did not fail but regardless it would not format the comment box on the second and subsequent worksheets. It turned out that the implicit references to the Selection were hiding that the text of comments is stored in a text frame inside a resizeable box, and once the objects involved were referred to explicitly in the automation code using the appropriate methods to resize and so on I had reliable operation and no further failures. This simple problem took about ten hours of debugging to resolve, however...

-Stewart

**NeoPa** · May 19 '08, 01:06 PM

Nice answer Stewart :) Sounds like you have a thorough understanding of this.

Runtime Error 1004: Method ‘Cells’ of Object ‘_Global’ failed

Runtime Error 1004: Method ‘Cells’ of Object ‘_Global’ failed

Comment

Comment

Comment

Comment