Referring to object properties by name... 1

[marklenel]

Is it possible to refer to the properties of an object by name, perhaps as if they were part of an array? e.g.

Window.Properties("Caption&quot = "Data Window"
Window.Properties("Height&quot = 400
Window.Properties("Visible&quot = True

The context:- I'm creating an Excel data browser with a number of windows in which to display different charts, data sheets, etc, and I was looking for a tidy way of managing their properties. I thought rather than using a massive long batch of code, I would create a reference table in Excel from which I can read each time I wanted to refresh the window properties. This would make the window properties more accessible and editable.

 

[SkipVought]

Hi,

You'll get alot of help from HELP and understanding more about collections and what collection belong to (are siblings of) what objects.

But in general...

for each ctl in ParentObject.Controls
  with ctl
    select case .type
    case msoBlahBlah1

    case msoBlahBlah2
  
    end select
  end with
next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[marklenel]

Thanks Skip - I'm OK with collections like that... but do you know if there is a properties collection?

For Each prp in Window.Properties etc.

Can't seem to find anything like this.

 

[SkipVought]

Objects have collections

Properties are attributes of objects and collections.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[marklenel]

Cheers Skip, it took me a good few weeks of frustration learning the distinction between all those things!

But I've a little niggle in the back of my head telling me that there is an *alternative* way to refer to an object's properties other than the standard:-

Object.Property

... and I thought of it because of my needs just here. Ooh, it's turning into an itch that needs scratching! I'm hunting on MS Support (without any luck so far).... I'll post any useful info here...

 

[combo]

You can create your own class with one method: Properties (if not works - use other word) and assign window to variable of created type.

combo

 

[strongm]

>Is it possible to refer to the properties of an object by name

Yes

Is it possible to set the values so referenced?

Yes

Here's a simple example. Add a reference to the TypeLib Information Library to your project:

Public Sub SetNamedProperty(objTarget As Object, strPropName As String, NewVal As Variant)
With New TLIApplication
.InvokeHook objTarget, strPropName, INVOKE_PROPERTYPUT, NewVal
End With
End Sub

 

[SkipVought]

Hey strongm!

I Learned something! ==> *

But there seems to be no help files when I load this library.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[combo]

Well done (*) strongm.
Skip, you can find help at:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224331

combo

 

[combo]

Just to complete:
VB/VBA 6 introduced CallByName function. It is possible to use:
[tt]Call SetNamedProperty(ThisWorkbook.Worksheets(1), "Name", "New_SheetName&quot[/tt]
(strongm's function) or
[tt]CallByName ThisWorkbook.Worksheets(1), "Name", VbLet, "New_SheetName"[/tt]
to change sheet's name using property "Name" as string.

combo

 

[marklenel]

Thanks to all,

I'll investigate your suggestions in a while; my project is due for distribution on some external systems (of generally unknown spec) so I want to keep additional references to a minimum - unless I understand exactly what's going on.

I also found - in VB help - the 'Properties Collection' of the Visual Basic Add-In Object Model, allowing access to the properties displayed in the Properties Window.

Cheers,
Mark

 

[strongm]

CallByName is just a wrapper for InvokeHook