The code is the following:

Private Sub UnlockSheet()
    Range(Selection.Offset(640, 1), Selection.Offset(0, 0)).Select
    ' I've tried this

    Sheet1.Range(Selection.Offset(640, 1), Selection.Offset(0, 0)).Select
    ' I've tried this too

    Dim ws as Worksheet: Set ws = ThisWorkbook.Sheets(1)
    ws.Range(Selection.Offset(640, 1), Selection.Offset(0, 0)).Select
    ' and of course this, too
End Sub

This code - with any of the specified declaration - gives me error runtime error 1004. Could someone explain me why? I can't get it. Docs is unclear to me.

CodePudding user response：

First note that

1. Sheets(1)
2. Sheets("Sheet1")
3. Sheet1

can be 3 totally different sheets. Excel uses 3 different naming systems to reference sheets and they are totally independent.

1. Sheets(1) uses the position in the tab bar of the sheet.
2. Sheets("Sheet1") uses the tab name of the sheet
3. Sheet1 uses the VBA name of the sheet (that has nothing to do with the tab name!).

Also note that using Range without specifying a sheet makes Excec decide which sheet you mean. So if you want reliable code you always have to specify which sheet that range should be in.

You might benefit from reading How to avoid using Select in Excel VBA.

If you try to .Select a range that is not in the active sheet VBA will throw "Runtime Error 1004". So you have to activate/select the sheet first. Also if Selection is not in the same sheet you try to select, it will fail too.

Dim ws As Worksheet
Set ws = ActiveSheet  'This ensures that `Selection` and `ws` are in the same sheet.
ws.Range(Selection.Offset(640, 1), Selection).Select

Note that .Offset(0, 0) does not make any sense. So remove it.

Note that the best practice is not to use .Select at all. Unless you want the user to see that selection.