Run-time error '1004'

[PeterBunde]

Folks!

My .xls file is stored on a Confluence page. When I download it, I have to press the activate editing button - which results in an error message:

Run-time error '1004' Method 'Sheets' of object '_Global' failed

The code that fails is:

Value_ = Sheets(workingsheet).Cells(param2, param1).Value()

I substituted the variable names with the real values and put it in a function then ran it, and the code runs fine:

MsgBox(Sheets("defects_of_category_store").Cells(1, 1).Value())

I should tell you that the sheet "defects_of_category_store" is hidden (I use it to store an array which I then re-load into memory when the sheet is opened).

(If you could tell me how, I would attach the file)

BW Peter B Hansen

 

[Kenneth Hobson]

No attachments are allowed on this form. Use a free shared site like dropbox.com to post links to a short simple file that duplicates the problem.

Now that we know that it is a class, what is the code that uses it that causes the error?

To post all of a sheet's code, right click the tab, View Code, select and then copy and paste here.

 

[JLGWhiz]

Value_ = Sheets(workingsheet).Cells(currentRow, param1).Value

This sheds a little light on it. The statement is relatively simple. You have three variables, 'workingsheet' should be a string value for the sheet name. 'currentRow' and 'param1' should both be integer values representing row and column. If the string value is not a valid sheet name you would get error 9, subscript out of range and if the compiler has a problem deciding what data type 'workingsheet' is other than string, you might get error 1004. If the either or both of the other two variable values are not integer values, they you could get either error 13, type mismatch when searching for a range, or error 1004 when trying to return a value from it as a range. In this case it seems to be the latter. You can check the values of the variables at run time by using the F8 function key with the vb editor open and step through the code, or use the watch window to monitor the variable values.

 

[JLGWhiz]

Sorry, I was late for a doctor appointment and had to cut short. Adding to post #12 , if you find that the integer values are not being returned for the Cells() range, then you need to review the prior code to see where the wrong value was originated. You can use the mouse pointer as you step through the code to view the variable values in the intelisense (tool tip) display. This is a debug method which I use, but I cannot do it for you from here.

 

[PeterBunde]

Post your full code.

ActiveWorkbook results in 'Nothing'. Mind you, that is when I download it from Confluence. If I stop the debugger, then rerun the

Private Sub Workbook_Open()

, there is no error.

 

[JLGWhiz]

ActiveWorkbook alone will show Nothing becaus it is a constant object, but it should return something for

ActiveWorkbook.Name

 

[PeterBunde]

I found a workaround.

In the event handler for opening the workbook, I set a flag if the workbook isn't recognised. Using that flag, I can then run the code I want at a later stage - because once the workbook has opened, it does recognise the workbook a in

Set wb = ThisWorkbook

.

Private Sub Workbook_Open()
there_is_a_problem = False
On Error GoTo problem_handler
Dim wb As Excel.Workbook
Set wb = ThisWorkbook
<My code that crashes if the sheet doesn't know>
Exit Sub
problem_handler:
    there_is_a_problem = True 'Will load stats when envoked, see next function
End Sub

 

[JLGWhiz]

I found a workaround.

In the event handler for opening the workbook, I set a flag if the workbook isn't recognised. Using that flag, I can then run the code I want at a later stage - because once the workbook has opened, it does recognise the workbook a in

Set wb = ThisWorkbook

.

Private Sub Workbook_Open()

there_is_a_problem = False
On Error GoTo problem_handler
Dim wb As Excel.Workbook
Set wb = ThisWorkbook
<my know="" doesn?t="" sheet="" the="" if="" crashes="" that="" code="">
Exit Sub
problem_handler:
    there_is_a_problem = True 'Will load stats when envoked, see next function
End Sub

glad you worked it out and thanks for the feedback.
regards, JLG</my>