Measure length of an AutoShape Line?

[John]

Is there a function or way in Excel to measure the length of an autoshape
line?

What I would like to do is be able to draw an autoshape line and then in an
Excel cell display the length of that line.

Thank you in advance to any help provided!

Best Regards,
John




---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 0615-3, 04/14/2006
Tested on: 4/16/2006 4:12:11 PM
avast! - copyright (c) 1988-2005 ALWIL Software.
http://www.avast.com

 

[Guest]

Try:

Sub ShowLineLength()
If TypeName(Selection) = "Line" Then
Range("A1").Value = GetLen(Selection)
End If
End Sub

Function GetLen(shp As Line) As Single
Dim W As Single, H As Single
W = shp.Width: H = shp.Height
GetLen = Sqr(W ^ 2 + H ^ 2)
End Function

"Line" is now considered a "hidden member" and possibly is no longer
supported in some versions beyond xl2000 which I am using. This can be fixed
if this is the case with you. Will be a little less concise however.

Regards,
Greg

 

[Guest]

Just FYI, Still there in Excel 2003.

 

[Peter T]

Not having XL2003 to hand I was alarmed to read Greg's comment about the
potentially hidden Line and relieved to read your FYI..!

Trust same goes for all the other old hidden members.

Regards,
Peter T

 

[Guest]

I am not aware of any that are not there.

 

[Peter T]

Thanks Tom,

I'll take that as confirmation even though you were careful not to put it
that way <g>

Regards,
Peter T

 

[Guest]

Thanks Tom for the tip. I wrote that more out of worry about Murphy's Law
than real concern it wasn't supported. Should I assume that all hidden
members are and will be supported till the end of VBA? I ask this in view of
the impression I've received that VBA is no longer being upgraded and MS
wants to change to VB.Net.

Greg