如何使用变量访问工作表属性? [英] How to access a worksheet property using a variable?
问题描述
情况:
在写关于问题的答案时我陷入了以下困境:
Whilst writing an answer for a question on SO I got stuck with the following:
问题:如何将工作表属性作为变量传递?
例如，我正在查看工作表保护属性，并希望通过变量(例如带有伪代码:
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 ......
我尝试过的事情:
1)首先，我搜索了一组要使用但没有用的枚举.我在 Visual Basic中找到了引用枚举，但不是我所追求的那种.我正在寻找类似 Activesheet.Protection(1).
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)然后，我尝试进行级联(希望会失败)，例如
2) I then tried concatenation (which I expected to see fail) e.g.
If ws & "." & "ProtectDrawingObjects" = False Then MsgBox "False"
哪个确实失败了
对象不支持此属性或方法
Object doesn't support this property or method
3)我尝试声明对象/变量变量并为其分配值，从而导致预期的Type Mismatch错误或上述属性不受支持的错误.
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)我还试图找到类似的SO问题，例如:使用以下方法获取工作表属性工作表变量，但这似乎意味着我需要创建一个自定义属性，而不是访问工作表的现有属性.
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)
这对于以"Protect"开头的属性非常有效，例如"ProtectDrawingObjects "，但不适用于以允许"，例如"AllowFormattingCells" ;返回:
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
所有属性均为 READ ，并且显然可以采用一种 Get 机制:
All the properties are READ and there clearly is a Get mechanism as one can do:
If ws.Protection.AllowFormattingCells = False then
是不是仅通过 CallByName()公开了吗?有解决方法吗?
Is it simply not exposed via CallByName() ? And is there a workaround?
总体目标:
我想在数组中具有保护对象的不同可能属性，例如("AllowDeletingColumns"，"AllowDeletingRows"，......)，然后循环测试它们是否设置为 True 或 False .
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.
我知道我可以使用 If 语句确定状态，但是我对是否有一种本质上可以执行以下操作的方法感兴趣?
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.....
推荐答案
对于注释太大，但是您可以在工作表保护"Allowxxxxxx"属性上使用CallByName.请注意，这些是 Worksheet.Protection 的属性，而不是 Worksheet
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
或
Dim ws As Worksheet
Dim p As Object
Set ws = Sheet1
Set p = CallByName(ws, "Protection", VbGet)
Debug.Print CallByName(p, "AllowDeletingColumns", VbGet) '>> False
这篇关于如何使用变量访问工作表属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
