CODEWITHSUNDEEP
excelvba
kuldeep singh
kuldeep singh

Reputation: 23

why range object does not automatically generate when we write like this worksheets(1).range("A1")

Workbooks(test.xlsm).Worksheets("AAA").Range("a1").Copy 
Workbooks(Practice.xlsm).Sheets("AAA").Range("c1")

Why Range object does not auto generate in above query as worksheets auto generate after workbooks object while writting above code.

Upvotes: 2

Views: 41

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

A very good question. Here is my understanding of it which I think is the right one but then... It may not be the right one :D

This can be explained in terms of "Earlybinding" and "Latebinding" concept. In latebinding, the object is resolved at runtime and not beforehand and hence Intellisense is not available for Late Bound objects.

Type those 2 words separately in the VBE and you will see this

enter image description here

Workbooks object is Early Bound (As Workbook) and Worksheets object is Late Bound (As Object).

So how do we bypass that? Simply declare your objects. For example

Sub Sample()
    Dim wb As Workbook
    Dim ws As Worksheet '<~~ EARLY BINDING

    Set wb = Workbooks("Test.xlsm")
    Set ws = wb.Worksheets("AAA")
End Sub

Now you will get the intellisense

enter image description here

Upvotes: 4

Related Questions