1

Situation:

Whilst writing an answer for a question on SO I got stuck with the following:

Question: How to pass a worksheet property as a variable?

For example, I was looking at worksheet protection properties and wanted to assign them via a variable e.g. with psuedo code:

myVar =  ProtectDrawingObjects 

If Worksheets("Sheet1").myVar = True Then ......

What I have tried:

1) In the first instance I searched for a set of enums to use but to no avail. I found references in Visual Basic to enums but not the kind of thing I am after. I was looking for something like Activesheet.Protection(1).

2) I then tried concatenation (which I expected to see fail) e.g.

If ws & "." & "ProtectDrawingObjects" = False Then MsgBox "False"

Which indeed failed with

Object doesn't support this property or method

3) I tried declaring object/variant variables and assigning values to these resulting in expected Type Mismatch errors or the above property not supported error.

4) I also tried to find similar SO questions such as: Get worksheet property using worksheet variable, but that seemed to imply I would need to create a custom property rather than the access the existing property of the worksheet.

5) As per @TimWilliam's helpful suggestion I tried the CallByName() function e.g.

Dim result As Boolean
result = CallByName(ws, "ProtectDrawingObjects", VbGet)

This worked well for those properties that commenced with "Protect" e.g. "ProtectDrawingObjects" but not for any of the properties that commenced with "Allow" e.g. "AllowFormattingCells"; which returned:

Object doesn't support this property or method

All the properties are READ and there clearly is a Get mechanism as one can do:

If ws.Protection.AllowFormattingCells = False then

Is it simply not exposed via CallByName() ? And is there a workaround?

Overall objective:

I wanted to have the different possible properties of the protection object in an array e.g. ("AllowDeletingColumns", "AllowDeletingRows",......) and then loop to test whether they were set to True or False.

I know I can use If statements to determine the states but I am interested in if there is a way essentially to do the following?

Worksheet.myVar

Where in pseudo code, in a loop, this would be 

If Worksheet.myArr(i) = False Then ..Do Something.....
Community
  • 1
  • 1
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • 2
    You can likely use `CallByName()` for this. https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/callbyname-function – Tim Williams Jan 16 '18 at 06:49
  • How can i set the value in a similar fashion? ws.myVar = False..... I assume using the vbLet but am unsure how to assign to the worksheet protection. – QHarr Jan 16 '18 at 07:40
  • Yes, use the vbLet/vbSet options. Eg: `CallByName ws, "ProtectDrawingObjects", VbLet, True` – Tim Williams Jan 16 '18 at 07:42
  • This allows me to access the value but is it technically not possible to do worksheet.myVar? – QHarr Jan 16 '18 at 07:55
  • Also, CallByName(ws, "AllowFormattingCells", VbGet) fails with object does not support this property or method. It is read only so I would have expected to be able to get its value. – QHarr Jan 16 '18 at 08:18
  • Edited comment from earlier: I used result = CallByName(ws, "ProtectDrawingObjects", VbGet) and it worked. – QHarr Jan 16 '18 at 08:19
  • I have noticed that all the properties that start with "Protect" can be accessed with vbGet, but all those starting with "Allow" cannot. – QHarr Jan 16 '18 at 08:29

1 Answers1

2

Too large for a comment, but you can use CallByName on worksheet protection "Allowxxxxxx" properties. Note these are properties of Worksheet.Protection, not direct properties of Worksheet 

Dim ws As Worksheet
Set ws = Sheet1
Debug.Print CallByName(ws.Protection, "AllowFormattingCells", VbGet) '>> False

or

Dim ws As Worksheet
Dim p As Object

Set ws = Sheet1

Set p = CallByName(ws, "Protection", VbGet)
Debug.Print CallByName(p, "AllowDeletingColumns", VbGet) '>> False
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Really appreciate the help. – QHarr Jan 16 '18 at 19:44
  • I’m still interested in if I can do ws.myVar =. ......... is this possible? Or have I missed something here and am being dumb? – QHarr Jan 16 '18 at 19:46
  • 1
    No there's no facility in VBA for that kind of dynamic evaluation: the only thing you could maybe do is use VBE programming to construct a dynamic procedure and execute that. E.g. https://stackoverflow.com/questions/10036711/converting-a-string-representation-of-a-constant-into-a-constant/10037332#10037332 – Tim Williams Jan 16 '18 at 20:13
  • Great. I will have a look at that. I neglected to mention that I had started to look at the VBIDE to see if I could do anything there but stalled. – QHarr Jan 16 '18 at 20:15