How to draw out the WorkSheet properties?

[Kris Kurek]

Does somebody know how to draw out properties set of a Worksheet object with VBA code? It is not problem for Workbook object thanks to BuiltinDocumentProperties property, but such or similar property doesn't exist for WorkSheet,
See piece of code I used for a workbook set of proprieties

Sub wbp1()

' Macro Workbook properties

Dim rw As Integer, i As Integer


' properties counter
rw = ActiveWorkbook.BuiltinDocumentProperties.Count

For i = 1 To rw
' Property name
Sheet1.Cells(i, 4) = ActiveWorkbook.BuiltinDocumentProperties(i).Name

' property index
Sheet1.Cells(i, 5) = "Index " & i

' Check - Is property used for this workbook
On Error Resume Next
' property value
Sheet1.Cells(i, 6) = ActiveWorkbook.BuiltinDocumentProperties(i).Value
If Err <> 0 Then Sheet1.Cells(i, 6) = "Not used"
On Error Resume Next
Next i
End Sub

[JimmyTheHand]

Sub List_WorkSheet_Properties()
    Dim VC As VBComponent, WS As Worksheet
    Dim p As Property
    
    Set WS = ActiveSheet
    Set VC = ThisWorkbook.VBProject.VBComponents("Sheet1")
    For Each p In VC.Properties
        Debug.Print p.Name
    Next
End Sub

You need to set a reference to Microsoft Visual Basic for Applications Extensibility.

[Kris Kurek]

Many Thanks - It is great solution - although I understand the code I will be happy however if you could say few words about Microsoft Visual Basic for Applications Extensibility - honestly speaking I know nothing about this feature - or may be you know a link to get some information about it

[JimmyTheHand]

Using the Microsoft Visual Basic for Applications Extensibility library you can directly manipulate your VBA Project. For example: you can add code modules, add/delete/modify VBA code, etc,. Mostly, whatever you can do in design mode, you can also do it via code, using the Microsoft Visual Basic for Applications Extensibility.
A great reading material: http://www.cpearson.com/excel/vbe.aspx

[Kris Kurek]

At this time it was only my personal anger that I am not able to solve the problem – and I was sure that solution must exists - only lack of my knowledge doesn’t allow me to find the answer – that’s why I have been looking for help.

But it may have practical reference - when you write a code sometimes you don’t know, which properties you may use for your macro – to be more precise, you don’t know which ones are “active” – Object Browser shows you all available properties of relevant object, but only some of them “are used” for particular object. Running the macro you precisely know which properties you can use for your purposes. I wrote macros for Workbook, Chart, Shape objects, but I had no idea how to “bite” a task for Worksheet object.

Anyway – I’ve ticked the “Microsoft Visual Basic for Applications Extensibility 5.3” item at the Reference list for VBAProject and tried to run the macro. Unfortunately it generates an “Run time error 1004” when performing
Set VC = ThisWorkbook.VBProject.VBComponents("Sheet1") statement. Furthermore - Set WS = ActiveSheet is not used further in the macro. Why - I do not know the answer – May be I am doing something wrong – please advice.

Thanks for the link – very useful

[JimmyTheHand]

Yes, you are right, sorry. WS is not used, it was used in an earlier version of the code, and I forgot to remove it. You can delete that line, and also you can remove the declaration of WS from the Dim line.

As for the error message, it is another overlook on my part. By default, the VBA Projects are protected from any meddling. If you want to use the extensibility functions, you need to switch off that protection. The process is called "enable trust access to the VBA project object model". Here is a link of how to do it: https://www.ibm.com/support/knowledg..._settings.html

Warning: this is a security setting of the Excel application, not that of the workbook. If you enable trust access, it remains enabled until you disable it. It poses a certain risk if you tend to trust and open unknown Excel workbooks.

As for the original problem, i.e. knowing the properties of objects, I would suggest to use the Intellisense service of the VBA environment. All you need to do is to exactly declare the object variable beforehand.
For example

Dim WS As Worksheet --> OK
Dim WS As Object --> Not OK
Dim WS As Variant --> Not OK

If the VBA editor knows the exact type of your object, it will offer you the possible properties and methods of that object class, as soon as you press the dot, as in
WS.

See also: https://sourcedaddy.com/ms-excel/advantage-vba-intellisense.html

[Paul_Hossler]

Out of courisity, why do you want to list the Worksheet properties?

The Workbook has Document properties (e.g. Author, Manager, Company, etc.) as well as 'software' properties (e.g. .Name, .Worksheets collection, etc.) but a Worksheet doesn't have any equivalent to the document-type properties of a Workbook

[Kris Kurek]

Jimmi,

Many thanks for your help and advice - see my macro below - it works as I wanted. Of course the VBA IntelliSense assits me well.

Sub List_WorkSheet_Properties()
Dim VC As VBComponent
Dim p As Property, PropCnt As Integer
Dim ty As Long, i As Integer

Set VC = ThisWorkbook.VBProject.VBComponents("Arkusz2") ' In local language Arkusz means Sheet
PropCnt = VC.Properties.Count
ty = VC.Type
i = 1
For Each p In VC.Properties
Sheets("PropList").Cells(i, 1) = i
Sheets("PropList").Cells(i, 2) = p.Name
On Error Resume Next
Sheets("PropList").Cells(i, 3) = p.Value
On Error GoTo 0
i = i + 1
Next
Sheets("PropList").Cells(1, 4) = PropCnt
Sheets("PropList").Cells(2, 4) = ty
End Sub

[Kris Kurek]

Hi Paul
If you go to VBE Editor you can display a Worksheet properties (see screen shot below), which are different form properties of ThisWorkbook – you can draw them out with ActiveWorkbook.BuiltinDocumentProperties property with no problem. My task was to draw out properties of a Worksheet. My experience with VBA is not smashing – I am rather closer to beginner then to experienced code writer. May be my task was not useful in general, but it had been making me angry that I was not able to “bite” the task - moreover I was pretty sure a solution must exists. So I’ve returned to this forum for help. Any way it was very good experience – I’ve learned few new things, which I perhaps use in the future.

ShProp.JPG