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

Question

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

score 1 · Accepted Answer · answered Oct 05 '14 at 18:05

1

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

answered Oct 05 '14 at 18:05

Steve S

421
3
4

2

Although this is a technically correct answer to the question, this doesn't feel like a better option than just referencing the object directly. – Degustaf Oct 05 '14 at 18:19
Of course its better, it means the code can be reused without having to retype all the variable names. in a large file that starts with "with shname" only having to change it at the start makes plenty of sense and not looking for other references because Microsoft didnt bother to add a this of Self obj – Steven Martin Oct 05 '14 at 18:52
ill just write refSelf or varSelf = .Cells.Parent and then its clear to other maintainers – Steven Martin Oct 05 '14 at 18:55
@Degustaf: I agree. It seems to be a pretty circuitous route to get a reference to an object that you already have by name. In any case, thanks for recognizing that I answered the OP's question. – Steve S Oct 06 '14 at 01:53

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

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.

edited May 23 '17 at 10:33

Community

1
1

answered Oct 05 '14 at 10:53

ilans

2,537
1
28
29

are you saying just use GetData(.) ? really? – Steven Martin Oct 05 '14 at 10:54
i want to reference the object that is being referenced by the with, not a child of it – Steven Martin Oct 05 '14 at 10:57
You cannot do this... if you find a way plz post here. But 10 years of vb6/vba/vb.net taught me it can't be done. – ilans Oct 05 '14 at 11:07
Specifically you can refer to the worksheet collection `ActiveWorkbook.Worksheets("Sheet1")` – ilans Oct 05 '14 at 11:10
but i already have a variable for the sheet, and i have that in the code above, i can use it just fine to call the function, but since i am in a WITH that is referecing that object , there must me a ME or THIS, that can use the object i am currently at with the WITH – Steven Martin Oct 05 '14 at 11:17
Again, you can only use the dot `.` , but I doubt it can be used in function calls. – ilans Oct 05 '14 at 11:20
Why is it so important for you to use a generic reference here? – ilans Oct 05 '14 at 11:21
Look at this: http://stackoverflow.com/questions/1152983/how-to-access-the-object-itself-in-with-end-with – ilans Oct 05 '14 at 11:35

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

2 Answers2