application properties Excel

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

application properties Excel

Post by ErikJan »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: application properties Excel

Post by HansV »

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
Best wishes,
Hans

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: application properties Excel

Post by ErikJan »

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)

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: application properties Excel

Post by ErikJan »

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:

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: application properties Excel

Post by HansV »

Thanks!
Best wishes,
Hans

User avatar
Oliver
2StarLounger
Posts: 128
Joined: 08 Mar 2010, 13:53
Location: Missouri

Re: application properties Excel

Post by Oliver »

Hey Erik,
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

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: application properties Excel

Post by ErikJan »

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)

User avatar
Oliver
2StarLounger
Posts: 128
Joined: 08 Mar 2010, 13:53
Location: Missouri

Re: application properties Excel

Post by Oliver »

Thanks!
Regards,
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison