Property Sniffer

Chris directed me to an interesting thread at Mr. Excel regarding inpsecting the properties of a cell. There’s two parts to this thread: Viewing the properties and looping through the properties. I’ve tried to create a program that loops through the properties by reading the library. I always start at Chip’s site and his TypeLib Utilities download. I pour through the code getting ever closer to understanding it. Then my head explodes and I give up. I’m not saying it’s possible even if I did understand it. Just that it seems like a good place to start, and that’s as far as I get.

You could easily put all the properties of a cell into a message box, but you’d have to hard code all the properties, which brings us back to looping through the properties. What would be nice, I think, is if the Properties dialog (Control Toolbox > Properties) had a Selection object choice in the dropdown. Then, the properties of whatever you had selected would show in that box, be it a range or a shape or whatever. Alas, it doesn’t.

The only other convenient place to find the properties, that I couldn’t think of, is the Locals Window. To that end, I wrote this:

Sub SniffRange()

    Dim cell As Range
    If TypeName(Selection) = “Range” Then
        Set cell = Selection
        Application.CommandBars.FindControl(, 1695).Execute ‘show the vbe
       Application.VBE.CommandBars.FindControl(, 2555).Execute ‘show the locals window
       Stop ‘keep the variable in scope
   End If
End Sub

It may be possible to 1) make “cell” global so that you don’t have to Stop the macro and 2) use API’s to show the Locals Windows without showing the VBE as if it was a child window of the main Excel application. I leave that to you.

Posted in Uncategorized

5 thoughts on “Property Sniffer

  1. Fun.

    I notice that you can change the properties of the cell while the Sub is stopped, refresh the Locals Window and see the changes.

    I’m not sure what I would do with this, but it’s good to know. Cells have a lot of properties . . .

    Thanks again, Dick, for this fine initiative.

  2. Hi,
    Would you know of any tools that can tell you all the outstanding references and variables with their values, kind of like the clr profiler? The reason is that I have a spreadsheet that is consuming an awful amount of memory (something like 1.5GB and rising) before it crashes after windows increases the paging file several times. All the tools I could find (VBACP, Aivisto) did not seem to show current variables and their values. Great blog by the way.

  3. Dick,

    Here’s a bit of code that may get you part of the way there. I’m sure it could be refined/expanded on quite a bit.

    Sub ListCellProperties(rrngCell As Range)
    Dim objTL As TLI.TLIApplication
    Dim objTLI As TLI.TypeLibInfo
    Dim mi As TLI.MemberInfo
    Dim sValue As String

    Set objTL = New TLI.TLIApplication
    Set objTLI = New TLI.TypeLibInfo

    objTLI.ContainingFile = Application.Path & “Excel.exe”

    For Each mi In objTLI.GetTypeInfo(“Range”).Members
    If mi.InvokeKind = INVOKE_PROPERTYGET Then
    sValue = vbNullString
    On Error Resume Next
    sValue = objTL.InvokeHook(rrngCell, _
    On Error GoTo 0
    Debug.Print mi.Name & “: ” & sValue
    End If
    Next mi

    Set objTLI = Nothing
    Set objTL = Nothing
    End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.