Error 1004 : function returning a Worksheet
 

Hello !

I'm experiencing an error after a function call returning a Worksheet
reference.

The function reads like this :

Private Function TheSheet(lSheet AS enmSheet) AS Worksheet

Select Case lSheet
Case enmEmpTable
Set TheSheet = ThisWorkbook.Sheets("Employees")

Case enmGlobal
Set TheSheet = Workbooks(sGlobal).Sheets("Parms")

....


End Function


Now, in another procedure, I use the following statement to get a
specific cell value :

vCellVal = TheSheet(lSheet).Cells(lRow, iCol).Value

This lines gives an error 1004 : Error defined by application or
object.

Tracing the code shows that the "TheSheet" function is executing
correctly and returns a valid reference to an existing worksheet.

iRow and iCol values are valid and the referred cell contains a text
value.

Any idea ?
Thanks

Error 1004 : function returning a Worksheet
 

Michel,

In your explanation you refer to both "iRow" and "lRow". Could that be the
problem?

hth,

Doug

"Michel S." wrote in message
...
Hello !

I'm experiencing an error after a function call returning a Worksheet
reference.

The function reads like this :

Private Function TheSheet(lSheet AS enmSheet) AS Worksheet

Select Case lSheet
Case enmEmpTable
Set TheSheet = ThisWorkbook.Sheets("Employees")

Case enmGlobal
Set TheSheet = Workbooks(sGlobal).Sheets("Parms")

....


End Function


Now, in another procedure, I use the following statement to get a specific
cell value :

vCellVal = TheSheet(lSheet).Cells(lRow, iCol).Value

This lines gives an error 1004 : Error defined by application or object.

Tracing the code shows that the "TheSheet" function is executing correctly
and returns a valid reference to an existing worksheet.

iRow and iCol values are valid and the referred cell contains a text
value.

Any idea ?
Thanks

Error 1004 : function returning a Worksheet
 

Thanks Doug

Unfortunately, it's only a typo. Both should read iRow

====

BTW : I forgot to specify I'm using Excel 2003



Doug Glancy a écrit :
Michel,

In your explanation you refer to both "iRow" and "lRow". Could that be the
problem?

hth,

Doug

"Michel S." wrote in message
...
Hello !

I'm experiencing an error after a function call returning a Worksheet
reference.

The function reads like this :

Private Function TheSheet(lSheet AS enmSheet) AS Worksheet

Select Case lSheet
Case enmEmpTable
Set TheSheet = ThisWorkbook.Sheets("Employees")

Case enmGlobal
Set TheSheet = Workbooks(sGlobal).Sheets("Parms")

....

End Function

Now, in another procedure, I use the following statement to get a specific
cell value :

vCellVal = TheSheet(lSheet).Cells(lRow, iCol).Value

This lines gives an error 1004 : Error defined by application or object.

Tracing the code shows that the "TheSheet" function is executing correctly
and returns a valid reference to an existing worksheet.

iRow and iCol values are valid and the referred cell contains a text value.

Any idea ?
Thanks