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:

Shape size from 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 collShp):

Selection of two shapes

- Here is when only the problematic shapes is selected:

Selection of the problecmatic shape only

+++ 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


        Set shp = Application.Selection

    End If

End Sub
  • 1
    It's not possible to help you without knowing your code. Please share a minimum example where the error can be reproduced, also a screenshot of the sheet before the error. – Máté Juhász Apr 21 '17 at 19:06
  • @MátéJuhász; The sheet does not change because there is no error per se. The error looks like a misinterpretation of the size of the shapes which now looks even more random since the same file without any change worked after being closed then re-opened to answer your comment. The only "visible" error is the one I posted showing the results in the "Locals Window" from the VBE. – simpLE MAn Apr 21 '17 at 19:42
  • 1
    You discuss Division and Shapes and Sizes. These are very big numbers and very small Accurate numbers. Think about precision. What are you using for measurements? What DIMs? I don't know about the rest of your project or any other code. I don't see measurements in the given code. We can only conjecture, and blindly to boot. – ejbytes Apr 21 '17 at 20:39
  • @ejbytes; my tool works, it's just that I randomly get wrong dimensions from excel. Measurements are in "points" as stated in the question. Measurements are stored in Double variables. Did no show it because prior putting the width in the variable, the source is already off as shown in the second picture compared to the third one. – simpLE MAn Apr 22 '17 at 13:45
  • I'm with @ejbytes we can't help without code that exhibits the problem. – Raystafarian Apr 23 '17 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.