CODEWITHSUNDEEP
excelvba
Kabir Shah
Kabir Shah

Reputation: 21

Invalid use of property Excel VBA

Sub test()
    Worksheets("Sheet1").Range("A1").Value = 20
End Sub

This simple code is giving error when I compile it. activesheet. works fine.

I want to know whats resulting in an error and how to fix it...

looks like it's not identifying the sheets, workbook etc.

Upvotes: 1

Views: 4401

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

The answer depends on which error you get. There can be 2 issues:

1. Workbook not specified

You have more than one workbook and Excel is looking in the wrong workbook for your sheet named "Sheet1", then you need to specify the workbook.

Workbooks("my-workbook").Worksheets("Sheet1").Range("A1").Value = 20

or if it is in the workbook where the code is running at it is better to use

ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = 20

Note that you should avoid ActiveWorkbook which is not very reliable.

2. Wrong worksheet name

There is no worksheet named Sheet1. Check your worksheet names. Note that there are different ways to specify a worksheet.

  • Specify by number

      Worksheets(1).Range("A1")

    This uses the position of the worksheet in the tab bar below the worksheets. Note that is not very reliable because position can easily be changed by moving the tabs around.

  • Specify by tab name

      Worksheets("Sheet1").Range("A1")

    This is probably the most common method. The worksheet is specified by its tab name. This is more reliable than by number.

  • Specify by VBA name

      Sheet1.Range("A1")

    Here the VBA name of the sheet is used. This name can only be changed in the VB editor and is not visible to the user, and has nothing to do with the tab name. Using this ensures that the VBA code still works on the desired worksheet even if a user changes the tab name of the worksheet.

So if the tab name is Sheet1 its VBA name can be Sheet5 and it can be on position 3 in the tab bar.
Using this example …

Worksheets("Sheet1").Range("A1")
Sheet5.Range("A1")
Worksheets(3).Range("A1")

… are all 3 accessing the exact same worksheet just by different names. So better to use meaningful names (and no numbers) here to not confuse.

Upvotes: 7

Related Questions