iterating through all properties of an object

  • Thread starter Thread starter Wild Bill
  • Start date Start date
W

Wild Bill

1. I have racked my brains trying and searched the web and groups for
hours on this. What do I'd like to do is run through a sheet's
properties. Essentially:

Dim prp As Property
For Each prp In Sheets(1).Properties

I've tried all kinds of variations on Sheets(1). Except perhaps the
correct one.
DAO 3.6 is enabled.
Chip's GetInfo disclaims use on XL97.

2. I would like to access a "variable" property in a sub, a la
call MySub("activecell.Font")
sub MySub(prp as Property)
debug.print prp.name
end sub

Obviously all of the code above is faulty, but I hope it illustrates
what I'm trying to achieve. I confess that I am swimming over my head,
being less than an expert on VB(A) objects.
 
Wild Wild Bill...

don't draw your sixshooter on me :)

you need CallByName (as used in chip's code)
but.., you'll find it only in VBA6 (xl2000+)
and... there's NO WAY ELSE.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Would one of you mind showing me what the code would then appear like?
I have xl2002.
 
I code for XL97 so I don't have it. You might find what you want in the
GetInfo code at
http://www.cpearson.com/excel/GetInfo.htm
It shows the second thing I was seeking, namely, to be able make a call
like MySub(activesheet, "Font") and set or get the Font's value there.
As to how to list all the property names for an object (e.g. "Font",
"Name","Parent", ...) someone else will have to show you.
 
Wild Bill...

I've got news for you..


http://www.cpearson.com/excel.htm

Chip has done more work on this, had a quick glance... supposed to work
under xl97 !!!

Download XLTLI - Type Lib Utilities
This download provides about 20 VBA utility functions for working with
the TypeLib Information object library, which is used to read type
libraries. TypeLib Info is the "engine" behind the VBA Object Browse,
and this library gives you programmatic access to type libraries,
allowing you to determine the various arguments for methods, the
symbolic names of constants, the methods of an object and so on.
Complete documentation is in the VBA code of this workbook. This code
is for advanced VBA programmers, not novices. An advanced version of
XLTLI is available for commercial customers.


Download http://www.cpearson.com/Zips/TLIUtils.ZIP .


You'll need TLBINF32 typelibrary from Bill G. et.al.
http://support.microsoft.com/default.aspx?scid=kb;en-us;224331


This contains a workbook called XLTLI.xls which has about 1200 lines of
VBA code related to working with the Excel and Office type libraries.
One of the procedures is called ParametersOfMethod which returns a
Collection object containing CParam objects (CParam is a class defined
in the VBProject), one CParam object for each parameter to the specified
method.


Note: You must have the TypeLib Information DLL installed on your
machine (typically C:\WINDOWS\SYSTEM32\TLBINF32.DLL) and referenced in
the VBProject.

For example to get all the methods for the Sort method of the Range
object, use
code like

Sub TestIt()
Dim N As Long
Dim V As Collection
Dim P As CParam
Set V = ParametersOfMethod("Range", "Sort", SearchAll)
For N = 1 To V.Count
Set P = V(N)
Debug.Print P.Name, P.Position, P.DataType, P.DefaultValue
Next N
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Hey Kool <g>

Dude on that extensive, thorough and enthusiastic reply man! I'm all
over it. WTG.

I grabbed TLBINF32.DLL from a Win98 machine and put it in
\WINDOWS\SYSTEM for XP machine.

Then on to Tools/References: uh oh,
MISSING: Microsoft Visual Basic for Applications Extensibility 5.3
Location: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\V
and that's the end of the line, edge of the dialog (just wait until I
meet the #$%^ing jerkoff who first invented unsizable dialogs) (and then
the MS developer geniuses who use them).

At any rate mefears I've seen enough to have to resign to this being
VB6+. My directory tree ends with the \VBA\ there. I do have
"Microsoft Visual Basic for Applications Extensibility" available (no
5.3 on its name), so I can uncheck the missing one and check him. That
still leaves a compile (rather, load) error with enum, for starters.

I only code for XL97 compatibility. If in fact this won't fly with 97 I
still want to express my sincere gratitude for your persistence trying
this, and for the utility it provides for those who do capitalize on
your (and naturally Chip's) effort.

WB

Wild Bill...

I've got news for you..


http://www.cpearson.com/excel.htm

Chip has done more work on this, had a quick glance... supposed to work
under xl97 !!!

Download XLTLI - Type Lib Utilities
Click to expand...
.....
[more helpful instructions on installation and usage from keepitcool]
.....
 
Back
Top