Is there code that lists (and even better: also documents) all "Application." properties in Excel?
There are many and some are really nice! E.g. Application.PrintCommunication...
See e.g. https://docs.microsoft.com/en-us/office ... activecell
application properties Excel
-
- BronzeLounger
- Posts: 1245
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: application properties Excel
The link in your post is from the official Microsoft documentation. In the left hand pane you can see all events, methods and properties of the Application object (and other objects).
You can also use the Object Browser - press F2 in the Visual Basic Editor and type Application in the search box.
And see Get list of all Properties and Methods for an object with VBA code alone
You can also use the Object Browser - press F2 in the Visual Basic Editor and type Application in the search box.
And see Get list of all Properties and Methods for an object with VBA code alone
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1245
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: application properties Excel
Works. Thanks. I wanted a list, the code shared works. Pity I can't 'do' all the vbGet application calls (I have an idea to get that... will share if that works)
-
- BronzeLounger
- Posts: 1245
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: application properties Excel
OK, so I took the code shared by Hans. That shows all "Application" properties (including some strange ones... I see a "Dummy22" and "Dummy23" in the list for example).
Of course, I'd wanted to also see some of the contents of VbGet-calls. Therefore, I augmented (only) the "Test" subroutine and I made it write VBA code that adds the calls and then runs that code. ;-)
Here's that code:
Of course, I'd wanted to also see some of the contents of VbGet-calls. Therefore, I augmented (only) the "Test" subroutine and I made it write VBA code that adds the calls and then runs that code. ;-)
Here's that code:
Code: Select all
' List all Methods and Properties of the excel application Object
' EJH (2021) Add code to list contents of VbGet functions
Public Sub Test()
Dim oFuncCol As New Collection, i As Long, oObject As Object, sObjName As String
Set oObject = Application '<=== Choose here target object as required.
Application.StatusBar = "Collecting calls... this can take a few minutes..."
Set oFuncCol = GetObjectFunctions(TheObject:=oObject, FuncType:=0)
Cells.CurrentRegion.Offset(1).Clear
Dim Func As String, Typ As String
'
'>> Code to create a new VBA Module
Dim proj As VBIDE.VBProject, comp As VBIDE.VBComponent
Set proj = ActiveWorkbook.VBProject
Set comp = proj.VBComponents.Add(vbext_ct_StdModule)
Dim codeMod As CodeModule, lineNum As Integer, ModName As String
ModName = comp.Name
Set codeMod = comp.CodeModule
With codeMod
lineNum = .CountOfLines + 1: .InsertLines lineNum, "Public Sub AppProp()"
lineNum = lineNum + 1: .InsertLines lineNum, " Dim S"
lineNum = lineNum + 1: .InsertLines lineNum, " On Error Resume Next"
End With
'<<
'
For i = 1 To oFuncCol.Count
Func = Split(oFuncCol.Item(i), vbTab)(0)
Range("A" & i + 1) = Func
Typ = Split(oFuncCol.Item(i), vbTab)(1)
Range("B" & i + 1) = Typ
If Typ = "VbGet" Then
If Not (Func = "FileDialog" Or Func = "Range" Or Func = "ShortcutMenus" Or Func = "_Default") Then
Application.StatusBar = "Adding code for: " & Func
'>> Add a code line
With codeMod
lineNum = .CountOfLines + 1: .InsertLines lineNum, " S = Application." & Func
lineNum = .CountOfLines + 1: .InsertLines lineNum, " If Trim(S)<>"""" Then"
lineNum = .CountOfLines + 1: .InsertLines lineNum, " Range(""C"" & " & i & "+ 1) = S"
lineNum = .CountOfLines + 1: .InsertLines lineNum, " Range(""C"" & " & i & "+ 1).Interior.ColorIndex = 40"
lineNum = .CountOfLines + 1: .InsertLines lineNum, " End If"
End With
'<<
End If
End If
Next
Application.StatusBar = False
'>> Close Sub
With codeMod
lineNum = .CountOfLines + 1: .InsertLines lineNum, " Columns(""C:C"").HorizontalAlignment = xlLeft"
lineNum = .CountOfLines + 1: .InsertLines lineNum, "End Sub"
End With
Set codeMod = Nothing
Set comp = Nothing
Set proj = Nothing
'<<
Range("B1") = "Total: " & oFuncCol.Count
'Run the code just created
Application.StatusBar = "Now running Application code..."
Application.Run ModName & ".AppProp"
Application.StatusBar = False
Cells(1).Resize(, 3).EntireColumn.AutoFit
'
On Error Resume Next
sObjName = oObject.Name
If Len(sObjName) Then
MsgBox "(" & oFuncCol.Count & ") functions found for:" & vbCrLf & vbCrLf & sObjName
End If
On Error GoTo 0
End Sub
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 128
- Joined: 08 Mar 2010, 13:53
- Location: Missouri
Re: application properties Excel
Hey Erik,
how did you run this, I tried it but I'm getting an error.
how did you run this, I tried it but I'm getting an error.
Regards,
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison
-
- BronzeLounger
- Posts: 1245
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: application properties Excel
In Options - Trust Center - Settings - Macro Settings you need to check the option "Trust access to the VBA project model"
(sorry, should have mentioned that)
(sorry, should have mentioned that)
-
- 2StarLounger
- Posts: 128
- Joined: 08 Mar 2010, 13:53
- Location: Missouri
Re: application properties Excel
Thanks!
Regards,
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison