Show Name of property (not the value)

John Kauffman

New Member
Joined
Oct 13, 2011
Messages
23
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)?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,786
Office Version
  1. 2010
Platform
  1. Windows
Welcome to the board.

See VBE Help for the CallByName function.
 
Last edited:

John Kauffman

New Member
Joined
Oct 13, 2011
Messages
23
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,786
Office Version
  1. 2010
Platform
  1. Windows
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

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,577
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top