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

 

[JLGWhiz]

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:



The code that fails is:

Value_ = Sheets([COLOR=#b22222]workingsheet[/COLOR]).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

Pretty obvious that it does not like something about the variable highlighted in red. Is it a variable or a string? Is it spelled correctly? 1004 errors are usually related to user error such as typos or oversight in punctuation, etc. Files for this forum can only be attached as a link to a share server.

 

[Kenneth Hobson]

Workingsheet and param 1 and 2 are not what you think I suspect. You may need to use CLng(param1) or use IsNumeric(param1)...

Use a Debug.Print to view the results after a Run in the Immediate Window (Ctrl+G) or set them to watch in the Debug Watch window. Or, press F8 to execute each line and hover mouse over variable to view values.

 

[PeterBunde]

Pretty obvious that it does not like something about the variable highlighted in red. Is it a variable or a string? Is it spelled correctly? 1004 errors are usually related to user error such as typos or oversight in punctuation, etc. Files for this forum can only be attached as a link to a share server.

It is a String (according to the Watch I set up) and it is spelled correctly.

The Watch on Sheets(workingsheet) fails with the Value <

Method 'Sheets' of object '_Global' failed>

 

[Kenneth Hobson]

You can test like this:

Sub Test()
  Dim p1$, p2$, param1 As Long, param2 As Integer, WorkingSheet$
  
  param1 = 1: param2 = 1
  p1 = 1: p2 = 1
  WorkingSheet = "defects_of_category_store"
  
  If Not WorkSheetExists(WorkingSheet) Then
    MsgBox WorkingSheet, vbCritical, "Worksheet Does Not Exist"
    Exit Sub
  End If
  
  MsgBox Sheets(WorkingSheet).Cells(param1, param2).Value
  'err.number=1004 because p1 is a string and p2 is a string but not a column string.  
  MsgBox Sheets(WorkingSheet).Cells(p1, p2).Value
End Sub

 

[PeterBunde]

In the debug, I

Application.ActiveWorkbook

results in Value

Nothing

. I suppose that is a hint to why the Sheets() is not working?

 

[PeterBunde]

In fact, all of the sheets in the file fail on the Sheets(..) method. To me that indicates that the Whole workbook is out of scope of the VBA code. Why?

 

[JLGWhiz]

In fact, all of the sheets in the file fail on the Sheets(..) method. To me that indicates that the Whole workbook is out of scope of the VBA code. Why?

Post your full code.

 

[Kenneth Hobson]

Maybe is in another instance?

For some reason my post #5 did not include the function.

 'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
    Dim ws As Worksheet, wb As Workbook
    On Error GoTo notExists
    If sWorkbook = "" Then
      Set wb = ActiveWorkbook
      Else
      Set wb = Workbooks(sWorkbook) 'sWorkbook must be open already.  e.g. ken.xlsm, not x:\ken.xlsm.
    End If
    Set ws = wb.Worksheets(sWorkSheet)
    WorkSheetExists = True
    Exit Function
notExists:
    WorkSheetExists = False
End Function

Of course the workbook can be checked as well.

Sub Test_IsWorkbookOPen()
  MsgBox IsWorkbookOpen("Personal.xls"), , "Personal.xls Open?"
  MsgBox IsWorkbookOpen("Personal.xlsb"), , "Personal.xlsb Open?"
  MsgBox IsWorkbookOpen("Personalx.xlsb"), , "Personalx.xlsb Open?"
End Sub

Function IsWorkbookOpen(stName As String) As Boolean
    Dim Wkb As Workbook
    On Error Resume Next ' In Case it isn't Open
    Set Wkb = Workbooks(stName)
    If Not Wkb Is Nothing Then IsWorkbookOpen = True
    'Boolean Function assumed To be False unless Set To True
End Function

 

[PeterBunde]

Post your full code.

My sheet has many modules so I'd have to

This is the function that fails:

Public Property Get Value_(Optional param1, Optional param2, Optional param3, Optional param4, Optional param5) As String
Dim s As String
Select Case ParamTypeCode(param1, param2, param3, param4, param5)
    Case "NulNulNulNulNul"
         Value_ = ActiveCell.Value
         
     Case "IntNulNulNulNul"
      Value_ = Sheets(workingsheet).Cells(currentRow, param1).Value
         
    Case "StrIntNulNulNul"
        Value_ = Sheets(workingsheet).Cells(param2, LookUpColumn(workingsheet, param1)).Value
    Case "StrNulNulNulNul"
            CulNum = LookUpColumn(workingsheet, param1)
        If CulNum <> 0 Then ' row that was set by Row_, and column by param1
            Value_ = Sheets(workingsheet).Cells(currentRow, CulNum).Value
        Else
            Value_ = Sheets(workingsheet).Name.Range(param1).Value
        End If
       
    Case "StrIntStrNulNul"
        Value_ = Sheets(param3).Cells(param2, LookUpColumn(param3, param1)).Value
    Case "StrStrNulNulNul"
        Value_ = Sheets(param2).Range(param1).Value
    Case "IntIntNulNulNul" 'col, row
        Value_ = Sheets(workingsheet).Cells(param2, param1).Value()
    Case "IntIntStrNulNul"
        If (param3) = "Offset" Then
            Value_ = Sheets(Sheets(workingsheet).Name).Cells(ActiveCell.Row + param2, ActiveCell.Row + param1).Value()
         Else
           MsgBox ("Unknown indicator: " & param3)
         End If
    Case "IntIntStrNulNul"
        Value_ = Sheets(param3).Cells(param2, param1).Value()
    Case "IntIntIntIntStr"
        MsgBox "It is not possible to retrieve value of a multi cell range"
End Select
End Property

How can I attach the whole Excel sheet?

BW Peter Bunde Hansen