Show Name of property (not the value)

[John Kauffman]

All of following using VBA.

I have an exercise for students to help them understand Range().Properties

They use VBA to read various properties of a range and put those property values in B1:B10. For example,
Range("B1").value = Range("RangeDemo").HasFormula.
B1 then shows true/false as they experiment with changing RangeDemo

In A1:A10 I would like them to put the name of the property (using VBA). For example A1 should display "HasFormula" which would indicate the contents of B1.

How can I set a cell.value to the name of a property (not the value of the property)?

 

[shg]

Welcome to the board.

See VBE Help for the CallByName function.

 

[John Kauffman]

Thanks, shg, and tip of the stetson to Texas. Exploring CallByName gave me a lot of new material to absorb. I tried as follows, but still get the value (not name) as the result.

I took two approaches. One is to put the property name in a var and use that var as the name of the property in Object.Property. The other is to pu tthe var in a name and use it in the CallByName.

Both tactics failed (code below - a lot of lines but almost all are explanatory comments). Does any problem pop out at you?

' Objective is student exercise to observe property values in "TestRange"
' in col A display the names of properties
' in COl B display the values of those properties
' For example A1 shows "HasFormula" and B1 shows True or False
' No need to loop through properties in code.
' They can set the value of StrPropertyOfInterest by hand

Dim strPropertyOfInterest As String
strPropertyOfInterest = "HasFormula"
Range("A1").Value = strPropertyOfInterest
Range("B1").Value = Range("TestRange").strPropertyOfInterest
' Problem (I think):
' How to use a variable holding a string of a property name
' in Object.Property?


' As an alternate I've tried to get the name of a property
' using CallByName but code below alwasy shows the value, not the name.
' I think this won't work because to spec property to get the name of,
' I have to get that name from a variable
Dim strPropertyOfInterest As String
strPropertyOfInterest = "HasFormula"
Range("B1").Value = CallByName(Range("TestRange"), strPropertyOfInterest, VbGet)

' Result is that the value (true/false), not the name, is put into A1

 

[shg]

I'm not sure what you're trying to do, but here's an example of what you could do:

<TABLE style="WIDTH: 138pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=184><COLGROUP><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3913" width=86><COL style="WIDTH: 37pt" width=49><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 height=16 width=49>Cell</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 64pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=86>Property</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=49>Value</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16 align=right>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31>Formula</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31>=2^2+3</TD></TR></TBODY></TABLE>

A2 has the formula =2^2+3

B2 has the validation list HasFormula, Formula, FormulaR1C1, Value

C2 has the formula

=GetRangeProperty(A2, B2)

... which uses this UDF

Function GetRangeProperty(r As Range, sProp As String) As Variant
    GetRangeProperty = CallByName(r(1), sProp, VbGet)
End Function

They can select a property in B2 and see the property value in C2.

 

[John Kauffman]

Running to a meeting, will test tomorrow. Thanks a ton.