Using the object of a `With` statement as paramete

2019-07-20 00:49发布

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

2条回答
Juvenile、少年°
2楼-- · 2019-07-20 01:32

You can use

drawBorder .Cells

Note: no need to use Call, the Sub name on its own followed by unbracketed parameters is sufficient

查看更多
我想做一个坏孩纸
3楼-- · 2019-07-20 01:33

I 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 say With myObject, you can easily refer to myObject's children methods and properties, but there is no way to indirectly refer to myObject 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:

Dim rng as Range
'...
With ReportSheet
    '...
    Set rng = .Range(.Cells(j + 9, 2), .Cells(k + 9, 2))
    With rng
        DrawBorder rng ' can also move this outside With block if you prefer
        .DoThis
        .DoThat
        '...
    End With        
End With

i.e. set an explicit reference to the object you are interested in, and use that thenceforth.

查看更多
登录 后发表回答