2

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
Community
  • 1
  • 1
DaveU
  • 1,082
  • 2
  • 14
  • 25
  • 1
    What is `pbOK`? Am I missing something here? – Siddharth Rout Nov 05 '13 at 22:01
  • if the second example works what is wrong with that? Why do you search for other solution? – Kazimierz Jawor Nov 05 '13 at 22:09
  • No such Worksheet property as `pbOK` (sounds like a Form component?). What is the actual object type you are passing? Add `Debug.Print TypeName( )` to check. – PatricK Nov 05 '13 at 22:17
  • @PatricK, you can create your own property declaring public variable in any sheet module. – Kazimierz Jawor Nov 05 '13 at 22:19
  • @KazJaw, thanks for the tip, but doing so does not compile unless using `Object`. Found out this method cannot hold changes after workbook is reopened. @DaveU you can do it like this: `Debug.Print Thisworkbook.Worksheets(Wk.Name).pbOK` – PatricK Nov 06 '13 at 00:33
  • @Patrick, thanks, hadn't thought of that, works great. – DaveU Nov 06 '13 at 02:00
  • @SiddharthRout, pbOK is custom property (True or False) I'm using to flag which sheets in the workbook require certain actions, such as protection, etc. – DaveU Nov 06 '13 at 02:14

2 Answers2

1

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?

Blackhawk
  • 5,984
  • 4
  • 27
  • 56
1

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.

PatricK
  • 6,375
  • 1
  • 21
  • 25
  • I see I should have phrased my question (my first one) better, sorry. All the sheets in my project have different requirements, so I'm using custom properties to flag the various actions that need to be executed, such as protection, visibility, etc. – DaveU Nov 06 '13 at 02:11