VB Excel_Area calculated in inches_Inaccurate

[Tze Chyi]

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

 

[strongm]

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:

[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?

 

[Tze Chyi]

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).

 

[strongm]

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:

[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]

 

[Tze Chyi]

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