CODEWITHSUNDEEP
excelvbavariables
stevekkong
stevekkong

Reputation: 1

excel vba, variable specify for worksheet function

I would like to specify a variable instead of using a repetitive expression.

I wonder if you could please help me to fix this code below.

Instead of keep writing Thisworkbook.sheets("certainsheet") I would like to define a variable to express this.

Sub specificsheet()

Dim i As Integer

Dim v As Sheets


For i = 1 To 10


v = ThisWorkbook.Sheets("certainsheet")
v.Cells(i, 1).Value = i


Next




End Sub

Thank you so much for any comments.

Upvotes: 0

Views: 175

Answers (1)

José Miguel
José Miguel

Reputation: 92

You can look on your VBE for the code name of that sheet and call that.
Let's assume it is called "ws_CertainSheet".
You can edit that codename by checking the properties of the that "Microsoft Excel Object" Here is an example. I usually set the codename as "ws_" + worksheet's name

On a side note, a frequent practice we do at the company I work at is to have specialized function calls for ListObjects, named ranges or other Excel Objects.
So, on our framework we have an mListObjects module with calls to all ListObjects of interest.
Something like:

Public Function tblSomeTable() As ListObject: Set tblSomeTable= ws_CertainSheet.ListObjects("tblSomeTable"): End Function

Upvotes: 1

Related Questions