I have a macro to place shapes within the printing area of my excel file. Briefely, to do it I use the width of the selected shapes (
Application.Selection) and remove it from the printing area width then divide it on the total selected shapes.
It works 97% of the time. The 3% it does not work is because excel seems not to get the right dimenson of the shape. The width is like off by ≈50%.
So I wonder how and why does it happen? It seems to be random although when it happens, it is only when more than one shape is selected. If I run the script selecting only the problematic shape, the dimensions are ok.
Did it happen to anyone else and how could it be corrected?
- Here is the size of the shape in excel:
From these dimensions and knowing that sizes in excel vba are in points they would translate to height ≈ 92.41 and Width ≈ 191.91.
- Here is the size I get from vba when two shapes are selected (stored in a collection
- Here is when only the problematic shapes is selected:
+++ Edited to add some code: +++ Here is the portion of the code messing with the shapes. It looks like it's random because I just openned the same file and the sizes are alright now... The shapes are simple screen shots like "printscreened" and pasted right into the excel file.
Sub centerShapeCompressed() If VarType(Application.Selection) <> 9 Then Exit Sub 'Type "9" is type "Object" Dim shp As Object Dim xlapp As Application Set xlapp = Excel.Application On Error Resume Next Debug.Print Application.Selection.Count 'if "Selection" contains less than 2 -> throws an error If Err.Number = 0 Then 'If Selection.Count > 1 On Error GoTo 0 Dim collShp As Collection Set collShp = New Collection For Each shp In Application.Selection collShp.Add shp, shp.Name Next shp Else Set shp = Application.Selection End If End Sub