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:
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.
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.
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.
Thanks!
Hi Dick,
There’s a help file for this thing:
http://download.microsoft.com/download/vb60pro/update/6.0/w9×2k/en-us/tlbinfht.exe
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, _
mi.MemberId, INVOKE_PROPERTYGET)
On Error GoTo 0
Debug.Print mi.Name & “: ” & sValue
End If
Next mi
Set objTLI = Nothing
Set objTL = Nothing
End Sub
I had to change this line
but other than that, it works perfectly.