0

So I'm basically transferring code from one workbook to the other. I'm opening a file through file pickers in both cases the code is identical:

Private Sub ImportData_Click()
(... irrelevant stuff...)

With fileDialog
'The file stores the last path chosen by the user. Goes there first. 
'Otherwise, defaults to the workbook's location because we know it exists

    If file_exists(Range(ThisWorkbook.Names("cst_last_import_folder")).Value) Then

(... more irrelevent stuff...)

End Sub

The Range() method fails in the new workbook (runtime 1004 error), but not in the old one. It took me a while to figure out - but that's because in the old book, this part is actually done within a module. In the new workbook, this bit of code is hosted directly on the sheet.

So I know how it fails - e.g. the Range() methode doesn't work if the code resides on the worksheet. But why is it so? E.g. what causes the method to fail only if hosted on the worksheet? Or is there a more fundamental reason behind this that I am missing?

logicOnAbstractions
  • 2,178
  • 4
  • 25
  • 37
  • Do you have a `Named Range` in your new workbook named "cst_last_import_folder" ? – Shai Rado Dec 07 '16 at 18:13
  • Absolutely! I debugged it to print Thisworkbook.names("cst_....").value & I get the right value. If I move the very same code into a module, it now works... – logicOnAbstractions Dec 07 '16 at 18:16
  • you have it in one of your `Worksheets` as a code ? why ? – Shai Rado Dec 07 '16 at 18:20
  • Well because in the newer workbook, there's an import button which triggers this code and since it's only short script I figured I might as well but code it directly in the _click() event. Perhaps slightly lazy. I mean the fix for this isn't hard - just created a import() sub in a module & make it happen there. But still, I want to understand this wouldn't work outside a module? (Also to understand if other methods might fail in similar circumstances....) – logicOnAbstractions Dec 07 '16 at 18:22
  • 2
    **ALWAYS** qualify the sheet that the `Range`, `Cells`, `Rows.Count`, `Columns.Count`, etc, etc, etc refer to. – YowE3K Dec 07 '16 at 19:14
  • 1
    Anyone wondering if that's "actually" a duplicate, see @YowE3K's comment. The error is the same, the solution is the same. Read the answer there, first paragraph and two bullet points are exactly what the problem is here. – Mathieu Guindon Dec 07 '16 at 19:15
  • Along the same lines as the duplicate answer @Mat'sMug posted, you may also have a named range that is scoped to the worksheet only. In that case, your code would work in a separate module, the specific sheet module, but not code from a different sheet in which case the same error would be observed. Comes down to the same solution of fully qualifying your cell locations. – Automate This Dec 07 '16 at 19:21
  • In a sheet code module, an unqualified `Range` refers to the sheet that the code belongs to and your range name is probably not part of that sheet. (So it's like saying "select the orange [which is in a box of oranges] from this box of apples" - it doesn't work.) In a project level code module, an unqualified `Range` will usually refer to the `ActiveSheet`, but if used with a range name as the index it can usually work out where to go. – YowE3K Dec 07 '16 at 19:21
  • Yup you guys are right - broadly speaking it amounts to the same thing. I usually use named workbook references because it's easy to reference it from anywhere, but that code's not all mine so I wasn't careful and ended with the unqualified range. Thanks for pointing out! – logicOnAbstractions Dec 07 '16 at 19:26

0 Answers0