CODEWITHSUNDEEP
vbaexcel
Helloguys
Helloguys

Reputation: 289

Call a function within "with" block?

Is it possible to call a function within the "with" block? I kept getting error "invalid or unqualified reference". How may I reference the parent object in the "with" statement? Thank you!

Sub Test()
    Set wb = Workbooks.Open(“C:\Book1.xls", True, True)

    With wb.Worksheets("Sheet1")
        lRow = LastRow()
        msgbox(lRow)
    End With

    With wb.Worksheets("Sheet2")
        lRow = LastRow()
        msgbox(lRow)
    End With

    wb.Close False
End Sub

Function LastRow()
    LastRow =.Cells.Find(What:="*", _
            After:=Range("A1"), _
            LookAt:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row
End Function

Upvotes: 4

Views: 618

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

A With block can hold an object reference; that object reference is scoped to the procedure the With block lives in, is born at With and dead & buried at End With: a With block variable doesn't suddenly become global/transient. The compiler has a much more grammatical view of the code, what you're asking for would require context that can only exist at run-time.

However, the function can easily get that context from a parameter:

'...

With wb.Worksheets("Sheet1")
    lRow = LastRow(.Cells)
    MsgBox lRow
End With

'...

Avoid using parentheses in VB6/VBA when invoking a Function (or Property Get) without capturing its return value into a local variable, or when invoking a Sub procedure. This isn't a question of coding style, grammatically superfluous parentheses change the semantics of the code and will inevitably end up causing problems (both at compile and run time).

I've added an explicit access modifier and an explicit return type, the source parameter, and some validation:

Private Function LastRow(ByVal source As Range) As Long

    Dim result As Range
    Set result = source.Find(What:="*", _
                             After:=source.Range("A1"), _
                             LookAt:=xlPart, _
                             LookIn:=xlFormulas, _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlPrevious, _
                             MatchCase:=False)

    If Not result Is Nothing Then
        LastRow = result.Row
    Else
        'source is empty
        LastRow = -1
    End If

End Function

Range.Find returns Nothing when it doesn't find what it's looking for. In this case given an empty source, .Row will be invoked against Nothing, and that would throw run-time error 91 in your code: you can never assume that Range.Find will return a valid object reference ;-)

Also After:=Range("A1") wouldn't necessarily be on the same sheet as source.Range("A1"), if this function is written in a standard code module (.bas) - unqualified, Range would be an implicit reference to [_Global].Range, a public property on a hidden application-scope module that returns the reference for ActiveSheet. In this case it's not a big deal, but in other situations, with other functions in the Excel object model, qualifying a method with one sheet, and parameterizing with a range from another sheet, can mean a cryptic run-time error 1004 being thrown.

Upvotes: 6

Related Questions