CODEWITHSUNDEEP
vbaexcel
DaveU
DaveU

Reputation: 1082

Get worksheet property using worksheet variable

I apologize if this has already been answered, but I had no luck searching on this.

What’s the best way to access a worksheet property, when the worksheet is referenced by a variable?

This one raises a compile error:

Sub Tst1(Wk As Worksheet)
    Debug.Print Wk.pbOK
End Sub

This one works, but is there a better way? Thanks.

Sub Tst2(Wk As Object)
    Debug.Print Wk.pbOK
End Sub

Upvotes: 2

Views: 332

Answers (2)

PatricK
PatricK

Reputation: 6433

With a tip from KazJaw, found out that you can get it compile no issues if:

Sub Tst1(Wk As Worksheet)
    Debug.Print Thisworkbook.Worksheets(Wk.Name).pbOK
End Sub

The only usefulness of this method i can think of is to store the state of macro processed (or mark worksheet as processed at a session), as values does not get saved after the workbook is re-opened.

Upvotes: 1

Blackhawk
Blackhawk

Reputation: 6120

I would say combine the two of them. The advantage of the first is that it preserves the Type Safety of the function. The advantage of the second is that it works. :)

Try keeping the parameter as a Worksheet, but cast it to an object (using a temp variable, it only costs a 4 byte pointer/copy) before you ask about the property:

Public Sub Test1(Wk As Worksheet)
    Dim tempObj As Object
    Set tempObj = Wk
    Debug.Print tempObj.pbOk
End Sub

I know this is supposed to be an answer and not a question, but can anyone tell my why VBA doesn't seem to have CObj() so the worksheet could be cast directly without the temp variable? I would have used that but it wasn't recognized. VB only perhaps?

Upvotes: 1

Related Questions