2

So, sometimes when I try to execute this command, it gives me an error. The problem is that it is very inconsistent. In certain cases, it works and in others, it just doesn't.

This is the line from getCellVal function. cellName is a string and s is an integer.

getCellVal = Sheets(s).Range(cellName).Value

This time it is giving me: Run-time error '438': Object doesn't support this property or method

This line actually worked without problems moments ago. I added some other functions that use it and now it's not working anymore.

Any ideas about why?

Improve this question
6
  • If cellName is a Named Range with workbook scope, then remove the Sheets(s). from the right side. Commented Aug 9, 2017 at 16:44
  • @RonRosenfeld unqualified Range calls implicitly refer to the active worksheet (unless you're in a worksheet's code-behind) - that would actually make things worse ;-) Commented Aug 9, 2017 at 16:54
  • @Mat'sMug Not so if it is a Defined Name with Workbook Scope, which is what I wrote. Commented Aug 9, 2017 at 17:07
  • @RonRosenfeld Fine. But then that would be error 1004, not 438. Commented Aug 9, 2017 at 17:14
  • FWIW accessing defined names should be done via the Workbook.Names collection for workbook-scoped names, and via the Worksheet.Names collection for worksheet-scoped names. Range's behavior is broken and unintuitive. Commented Aug 9, 2017 at 17:19

1 Answer 1

Reset to default
3

Unqualified calls into the Sheets collection implicitly refer to whatever workbook is currently active, and it's important to know that the collection contains Worksheet objects, ...but also Chart objects.

Since you mean to work with a Worksheet, use the Worksheets collection instead.

If you're not getting an "index out of bounds" error, then the sheet you asked for does exist. But error 438 points to that sheet not being a Worksheet (and thus not having a Range member).

I bet the active workbook has a chart sheet at index s.

The solution is simply to be explicit.

If you mean to work with the workbook that contains the code that's running, qualify Workbook member calls with ThisWorkbook:

getCellVal = ThisWorkbook.Worksheets(s).Range(cellName).Value

If you mean to work with a workbook, then you need to get ahold of the Workbook object when you open it:

Dim wb As Workbook
Set wb = Application.Workbooks.Open(path)
'...
getCellVal = wb.Worksheets(s).Range(cellName).Value
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.