Is it possible to use the object of a With
statement as parameter for a procedure called from within a With
block, without having to fully qualify that object? It could be the equivalent of a this
or me
.
With thisThing.thatThing.otherThing.myObject
MySub [?] ' How do I specify myObject as the parameter?
MySub This 'No, that's not it...
MySub Me 'Not this either... what is it?
'Of course I could do this:
MySub thisThing.thatThing.otherThing.myObject
'But I'd prefer not having to fully qualify myObject like that
....
End With
Example :
With Worksheet.Range("A1:E4")
Call SubName(<range from with>)
End With
<range from with>
would be referring to Worksheet.Range("A1")
Edit:
Seems that I was implying a single value by giving a range of just a single cell range, my bad. I'm specifically trying to parse a range into the procedure that I'm calling (it draws some borders around the specified range).
My actual code:
With ReportSheet
// Call drawBorder(.Range(.Cells(j + 9, 2), .Cells(k + 9, 2))) <--What I have to do right now
With .Range(.Cells(j + 9, 2), .Cells(k + 9, 2))
//Call drawBorder(<the specified range above> <--What I want to do
//Other code
End With
End With
Sub drawBorder(drawRange As Range)
With drawRange
//Various code
End With
End Sub
You can use
Note: no need to use
Call
, theSub
name on its own followed by unbracketed parameters is sufficientI see what you are getting at, but unfortunately there is no way to directly do what you are asking. One weakness of the
With
statement is exactly what you are pointing to: when you sayWith myObject
, you can easily refer tomyObject
's children methods and properties, but there is no way to indirectly refer tomyObject
itself.That may be why the
With
statement has not caught on beyond Visual Basic. In other languages, this would be the standard way of doing it:i.e. set an explicit reference to the object you are interested in, and use that thenceforth.