Setting Excel Range.Value when sheet not selected?

[Johnboy]

I'm having a problem with XL2000 when setting the values of a Range object on a given worksheet to the contents of an array when that worksheet is not active (a different worksheet is selected/active). It works perfectly with this method:

Dim myArray(1 to 2, 1 to 5) as Double
'(Set myArray values here)
Worksheet("1").Range("A1:E2").Value = myArray

but I need to be able to use the "cells" method of defining a Range object. The following method does not work when the sheet containing the Range being set is not the active worksheet:

Dim myArray(1 to 2, 1 to 5) as Double
'(Set myArray values here)
Worksheet("1").Range(Cells(1 ,1), Cells(2,5)).Value = myArray

This method raised error #1004: "Application-defined or Object-defined error." I would greatly prefer to use the second method in my code because it suits my program much better. Does anyone know how to make it work without selecting the sheet? :confused:

[D-niss]

Humm is the code in a module or on the sheet object?
Try using Sheets("Sheet1") instead of worksheet.

[Johnboy]

I just tried that idea. It raises the same error. I hadn't tried that previously, only because I know that VBA uses the activeworkbook when the workbook is excluded from the statement. Thanks for the try. :)

[D-niss]

I just tried that idea. It raises the same error. I hadn't tried that previously, only because I know that VBA uses the activeworkbook when the workbook is excluded from the statement. Thanks for the try. :)

Yes I know, my bad. I misread your post. Is the code in a module?

[Johnboy]

Yes I know, my bad. I misread your post. Is the code in a module?

Yes, it's in a module. I have a really cool method for saving data from my application into a binary file. It runs faster when I then reload the data using the method of setting a range = an array, rather than looping through individual cells. This procedure refers to around 20 separate worksheets that are all identical. So I place it in a module to call it globally.

[D-niss]

Yes, it's in a module. I have a really cool method for saving data from my application into a binary file. It runs faster when I then reload the data using the method of setting a range = an array, rather than looping through individual cells. This procedure refers to around 20 separate worksheets that are all identical. So I place it in a module to call it globally.

It doesn't work for me either. I guess the sheet has to be activate.
You can use Worksheets(1).activate before your routine.
I've never ran into that prblm. I must be doing it some other way.
Perhaps if you show how you're doing this, I could give you some
suggestions.

Regards,