Code works EXCEPT when code is running??

[OxfordCurmudgeon]

How can code work when it's not running? When it's in the Immediate window.

I evaluate this code in the Immediate window

?Range("ShiftsOrderedDataArea").Cells(1,1).Value

and it retrieves the contents of the cell. I then put my cursor on the start of a subroutine and press F8 (Step Into). The Private Sub ... line gets highlighted in yellow. I go back to the Immediate window and execute the same line of code (no changes) and I get

Run-time error '1004': Application-defined or object-defined error

I click OK to clear the error message, then click Reset under the Run dropdown menu, then go back to the Immediate window and run that line of code again. It works as expected.

The named range is defined in the same workbook as my code:

I assume this is another newbie mistake. Can anyone point me in the right direction?

 

[Alex Blakenburg]

Show us the VBA code, that line would need to something to the left of it in VBA
eg Variable_name = Range("ShiftsOrderedDataArea").Cells(1,1).Value

 

[RoryA]

I suspect you are running a routine in a worksheet code module, and the range in your debug.print code is not on that worksheet.

 

[OxfordCurmudgeon]

Show us the VBA code, that line would need to something to the left of it in VBA
eg Variable_name = Range("ShiftsOrderedDataArea").Cells(1,1).Value

I was trying to strip this down to the bare minimum; being able to create (and recreate) the problem in the Immediate window seemed to be the absolute minimum.

You're correct, of course; the problem cropped up while running actual code. The code I used in the Immediate window isn't identical to the real code. The real code uses a variable passed as an argument to the sub. Unless the code is running (in debug mode), that variable doesn't exist.

Here's the code fragment

Private Sub GetNextShiftsRecord(SrcIndex As Integer, EmpName As String, Day As Integer, Tippable As Boolean, _
Clockin As Date, Clockout As Date)
If SrcIndex < Range([ShiftsOrderedSortRange]).Rows.Count Then
SrcIndex = SrcIndex + 1
EmpName = Range("ShiftsOrderedDataArea").Cells(SrcIndex, [soEmployeeName])

The error occurs on the last line. I set a breakpoint on the first line. When the procedure was called, SrcIndex had a value of 1 and .Count was 361. soEmployeeName is a constant defined in the workbook; it has a value of 1.

Thanks for your help!

 

[OxfordCurmudgeon]

I suspect you are running a routine in a worksheet code module, and the range in your debug.print code is not on that worksheet.

Thanks, Rory.

I'm not using debug.print; I'm entering the code directly into the Immediate window.

You are correct. The named range is not on the same worksheet as the code, but the named range specifies the worksheet. (I think you can see that in the image I posted in the first message.)

Any insights?

 

[RoryA]

? is short for Debug.print in the immediate window.

If you start running code in a worksheet, then the scope of any unqualified calls to Range or Cells is the sheet with the code in it. Since the named range is not on that sheet, the code will fail. If you were to use:

?Application.Range("ShiftsOrderedDataArea").Cells(1,1).Value

it should work.

 

[OxfordCurmudgeon]

Thank you, Rory!