20

Is there a way to get a list of all valid properties for a given object?

If I wanted to start at cell a1, and go down and assign a1, a2, a3, all of the valid properties for let's say a worksheet object for example, is that something that can be done? I can't find any:

list = object.enumproperties

Any ideas?

Teamothy
  • 2,000
  • 3
  • 16
  • 26
user2021539
  • 949
  • 3
  • 14
  • 31

1 Answers1

21

Tools - References - TypeLib Information.

Then:

Sub DumpProperties(ByVal o As Object)

  Dim t As TLI.TLIApplication
  Set t = New TLI.TLIApplication

  Dim ti As TLI.TypeInfo
  Set ti = t.InterfaceInfoFromObject(o)

  Dim mi As TLI.MemberInfo, i As Long
  For Each mi In ti.Members
    i = i + 1
    ActiveSheet.Cells(i, 1).Value = mi.Name
  Next

End Sub
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • 1
    I had to download the DLL from a shady source (and regsvr32 it) but the sub works great, thanks! – ashleedawg Mar 02 '18 at 15:11
  • I get "Wrong Number of arguments" when calling DumpProperties(myObject) within another subs loop. Is there a specific method to running this sub? – Ken Ingram Apr 18 '19 at 17:01
  • @KenIngram There is not. Did you include [unwanted parentheses](https://stackoverflow.com/q/6331106/11683)? – GSerg Apr 18 '19 at 17:25
  • I was. Leaving off the parens I got a new error. "ActiveX component can't create object" -> Set t = New TLI.TLIApplication – Ken Ingram Apr 19 '19 at 13:19
  • Yep. I guess that's the problem. WTF? So, just left hanging with no access to viewing Object properties....jfc – Ken Ingram Apr 19 '19 at 19:35
  • @KenIngram For viewing properties you have Object Browser, and you can add the object to the Watch pane that will also show properties. Depending on the object, you may also create a vbscript file to create and examine the properties, then you would use `Set t = CreateObject("TLI.TLIApplication")` instead of `= New TLI.TLIApplication`. – GSerg Apr 19 '19 at 20:32
  • 1
    @GSerg The TypeLib Information DLL is not on my Reference list, and when I try to add it (I can find it in C:\Windows\System32) I get the error "Can't add a reference to the specified file". Any ideas how to get around this? I tried using `Declare ... Lib "typelib" ...` but that didn't help either. – Bilbottom Oct 18 '19 at 07:45
  • @BillWallis Are you running 64-bit Excel? – GSerg Oct 18 '19 at 07:49
  • @GSerg No, I am running 32-bit. – Bilbottom Oct 18 '19 at 08:09
  • @BillWallis Then you should be able to add it. On my 64-bit Windows and 32-bit Office, it's located in Windows\SysWow64\TBLINF32.DLL. – GSerg Oct 18 '19 at 08:12
  • @GSerg The DLL that I was looking for was typelib.dll -- I cannot find the tblinf32.dll file in my C:\Windows\SysWow64 or my C:\Windows\System32 folders. Do I need to download this from somewhere? – Bilbottom Oct 18 '19 at 08:44
  • @BillWallis Actually I'm not sure which component installs it, I always thought it came with Windows. I also made a typo, it's TLBINF32.dll. – GSerg Oct 18 '19 at 09:20
  • @GSerg No worries, I can do without it for the time being (I cannot find tlbinf32.dll either). Thank you for your help though, I may pick this up later! – Bilbottom Oct 18 '19 at 10:07
  • 1
    I am trying to reproduce this. But at `Set t = New TLI.TLIApplication` I get a `Class not registered` error. I have added a reference to tlbinf32.dll. Does anyone have any suggestion what could be wrong? – Michael Wycisk Jul 27 '20 at 19:43
  • This used to work when I was on windows 8 but, I think since my work computer was upgraded to windows 10, it stopped working. I'm getting back into software development lately and this functionality was just, so so useful. Does anyone know how to get this working again, and without having administrator access to one's PC ? – Shodan Nov 19 '22 at 03:14
  • @Shodan There should be no problem on Windows 10. Are you using 64-bit Office? – GSerg Nov 19 '22 at 09:47