Home |
Search |
Today's Posts |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
iterating through all properties of an object
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
iterating through all properties of an object
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. On 21 Aug 2003 12:20:44 -0700, (Barney Fife) wrote: Would one of you mind showing me what the code would then appear like? I have xl2002. (Wild Bill) wrote in message ... Thanks. Clear and specific :) On Thu, 21 Aug 2003 04:31:34 -0700, keepitcool wrote: 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 (Wild Bill) wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
iterating through all properties of an object
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...b;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 (Wild Bill) wrote: 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. On 21 Aug 2003 12:20:44 -0700, (Barney Fife) wrote: Would one of you mind showing me what the code would then appear like? I have xl2002. (Wild Bill) wrote in message . .. Thanks. Clear and specific :) On Thu, 21 Aug 2003 04:31:34 -0700, keepitcool wrote: 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 (Wild Bill) wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
iterating through all properties of an object
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 On Thu, 21 Aug 2003 18:11:58 -0700, keepitcool wrote: 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 ..... [more helpful instructions on installation and usage from keepitcool] ..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterating files in folder | Excel Worksheet Functions | |||
Iterating through string in a cell | Excel Discussion (Misc queries) | |||
Excel: how set default for Format Object Properties to Move and s | Setting up and Configuration of Excel | |||
iterating checkboxes | Excel Discussion (Misc queries) | |||
Iterating through row cells on column Match, help please. | Excel Programming |