Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VB Excel_Area calculated in inches_Inaccurate

Status
Not open for further replies.

Tze Chyi

Technical User
Aug 25, 2016
11
MY
Hi,
I had convert the shape area to inches by dividing the width and height by 72.
but the data shown is inaccurate.
For example,
I had the rectangle shape with Width = 10.95", Height = 8.87"
Thru calculator I got the area as 97.1265
But macro gave me 96.7

Sub ShowArea()
Dim Width As Double
Dim Height As Double

Width = Selection.ShapeRange(1).Width / 72
Height = Selection.ShapeRange(1).Height / 72

With Selection.ShapeRange(1)

Selection.ShapeRange(1).TextFrame.Characters.Text = Round(Width * Height, 1) & "m2"

End With
End Sub
 
It often pays to check that the values are what you think they are. We could examone via Watch points or using breakpoints. But for now, try the following:

Code:
[blue]Sub ShowArea()
Dim Width As Double
Dim Height As Double
Dim mySelect As Range

Set mySelect = Selection.Range

MsgBox "Width: " & Selection.ShapeRange(1).Width & " Height: " & Selection.ShapeRange(1).Height
Width = Selection.ShapeRange(1).Width / 72
Height = Selection.ShapeRange(1).Height / 72
MsgBox "Width: " & Width & " Height: " & Height

With Selection.ShapeRange(1)
    Selection.ShapeRange(1).TextFrame.Characters.Text = Round(Width * Height, 1) & "m2"
End With
End Sub[/blue]

I'd expect to see something like:
Width: 788.4 Height: 638.6
Width: 10.9500003390842 Height: 8.86944410536024

What do you get?
 
Hi Strongm,
Yes, I got
Width: 668.5715; Height: 144.3213
Width: 9.28571510314941; Height: 2.00446200370789
While Excel Shape format gave me
Width: 9.33; Height: 2

Based on my observation, the area of shape get from macro seems to be 0.4% different from what I got from excel shape format (own calculation thru calculator).
 
So, can you see what is happening? The problem is that the shape object rounds each of the values that it displays in the shape format dialog, whilst your calculation rounds AFTER you multiply.

For example, try:

Code:
[blue]Sub ShowArea()
Dim Width As Double
Dim Height As Double
Dim mySelect As Range

Set mySelect = Selection.Range

MsgBox "Width: " & Selection.ShapeRange(1).Width & " Height: " & Selection.ShapeRange(1).Height
Width = Selection.ShapeRange(1).Width / 72
Height = Selection.ShapeRange(1).Height / 72
MsgBox "Width: " & Width & " Height: " & Height

[b]MsgBox Round(Width * Height, 2) & " " & Round(Width, 2) * Round(Height, 2)[/b]

With Selection.ShapeRange(1)
    Selection.ShapeRange(1).TextFrame.Characters.Text = Round(Width * Height, 1) & "m2"
End With
End Sub[/blue]

 
Hi,
I try with both code but it seems to be not much different.
Selection.ShapeRange(1).TextFrame.Characters.Text = Round(Width, 1) * Round(Height, 1) & "m2"
Area: 42.84

Selection.ShapeRange(1).TextFrame.Characters.Text = Round(Width * Height, 2) & "m2"
Area: 42.88

Calculator: 43.08
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top