Show all Excel constants

Tuesday, October 14th, 2008 | Excel

It is often very useful to have a list of all the constants that are used in Excel 2003. One way to get a list is to search the web, but you can produce such a list yourself. All you have to make sure is that you have the following DLL installed TlbInf32.dll. If you don’t have it, search the web and you will find. Once installed create a new mrs file and add the following code

Dim R
Dim Member
Dim oPath 

oPath = "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE"

With CreateObject("TLI.typelibinfo")
	.ContainingFile = oPath
	For Each R In .Constants
		debug.Log("<b>" + R.Name + "</b>")
		debug.Log("<table border=""1"">")

		For Each Member In R.Members
			debug.Log("<tr><td>" + CTEXT(Member.Name) + _
			"</td><td>" + CTEXT(Member.Value) + _
			"</td></tr>" + mr.NewLine)
		Next
		debug.Log("</table><br />" + mr.NewLine)

	Next
End With

When you run the code successfully, it will produce a list of all the constants and their values, copy and past the output from the output window and paste it into a html file and there you have it all the constants listed and their values. Another thing that you could do would be to change the code so that it produced an include file used in studio, to do this, try the following code.

Dim R
Dim Member
Dim oPath 

oPath = "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE"

With CreateObject("TLI.typelibinfo")
	.ContainingFile = oPath
	For Each R In .Constants

		For Each Member In R.Members
			debug.Log("CONST " + CTEXT(Member.Name) + " = " + _
			CTEXT(Member.Value) + mr.NewLine)
		Next 

	Next
End With

Depending on what script you run, will depend on what output you get, but if you dont have the dll then click here to get the output anyway. I have not managed to do this yet, but i would love to get this code to work for Word and Powerpoint. If you think you know how to do it then please let us know.