CODEWITHSUNDEEP
vbaexcel
Steven Martin
Steven Martin

Reputation: 3272

VBA With obj - how to reference the obj inside the With without naming it?

If I am inside a With block that is referencing the object I want to reference for a function call for example , must I reference the object by name or is there a "this" ,"me" ref that I can use?

I Have done a search online but not finding much about it in VBA.

Dim shExport As Worksheet

With shExport
    .......
    .......
    'works as expected 
    GetData(shExport)

    'but how can this be achieved without naming it
    GetData(this)
    GetData(me)
    GetData()
    ........
End With



  Function GetData(sh As Worksheet) As Integer
   .....
   .....
   End Function

Upvotes: 1

Views: 1465

Answers (2)

Steve S
Steve S

Reputation: 431

You could specify a property of the object that supports the .Parent property to get a reference back to the object. For example:

With shExport

    GetData(.Cells.Parent)

End With

Upvotes: 1

ilans
ilans

Reputation: 2727

There's no way to reference the object other than directly call it (also look: How to access the object itself in With ... End With).

What you can do is to generically call the worksheets collection like this:

ActiveWorkbook.Worksheets("Sheet1") or ActiveWorkbook.Sheets(1) etc.

Regarding the with block - You can use a dot . inside it to reference the object's child elements/fields, but you can't send the dot . as a parameter.

Upvotes: 0

Related Questions