Excel VBA Shape Rotate makes unwanted Move and Resize to Shape Group.

Copper Contributor

A shape group is getting resized after VBA rotation.

 

The attached Excel 2016 / Office 365 workbook contains a worksheet with 2 sets of shapes:

  • Triangle 1 and Circle 1 (Ungrouped)
  • Triangle 2 and Circle 2 (Grouped)

Changing the value (e.g. 1 thru 360) in cell H4 executes Excel's Worksheet_Change script.

This script rotates the triangles inside the circles.

  • Triangle 1 rotates properly
  • Triangel 2 also rotates, however, it is contained inside of a shape group, and the entire shape group unexpectedly resizes and moves.

Can someone explain why the shape group resizes and moves?

Can someone provide a solution that prevents the shape group from resizing?

I have attached a sandbox xlsm file so you can duplicate this issue for yourself.

Before Triangle RotationBefore Triangle RotationAfter Triangle RotationAfter Triangle Rotation

11 Replies
I'm unable to reproduce the issue, both shape combinations rotate the same and keep their sizes.

Ok, Thanks for trying. It is interesting you cannot duplicate the resize issue.
Are you using Excel 2016 / Office 365 with latest updates applied, as I am?

Additionally, be sure to enter a different value into Cell H4 and press enter.

You may need to do this several times in order to see the effect, because the resize and move occur in very small increments. 

@Grosner

 

Hi,

 

I've tried to rotate them, but they both rotate correctly as the screenshot below!

Rotate Shapes.png

 

I think there is a bug in your current version of Office, so please make sure you have the latest version of Office 365.

 

Regards

I am fully updated. 

 

If you look closely at the result image, you will see the image on the right is slightly larger. I am guessing you only entered a value into cell H4 one time. If you enter any value several more times, pressing enter after each entry, I think you will see the changes more clearly.

 

Also, right click and select size and properties. This should show the same value for height and width for the grouped object. If it does not, then you have replicated my situation.

@Grosner

 

Ok, I confirm the issue!

It seems like the grouping causes this weird bug!

You can send feedback to the Excel Team about it.

For now, the workaround is to forget the grouping!

 

Regards

@Haytham Amairah 

Thank you for confirming this issue.  

I will provide feedback to Microsoft.

It would be nice if anyone has an actual workaround.

 

Thanks!

 

Some tips to ease sending feedback:
- Use Word (or Powepoint) to get screen-shots (Insert tab on ribbon, Screen-shot drop-down), prior and after the VBA code has run
- Paste the screen-shots in your Excel window
- Also paste a screen-shot of your VBA code in the same Excel window
- Open File, Feedback and click send a frown
- Check the include screen-shot box

@Grosner 

My workaround plan is to use Excel VBA to prevent the unwanted move and resize of the shape group is to:

1. Use VBA to ungroup the group object (that way I can rotate my shape without affecting the size or position of the shape group (because my shape will no longer be inside of the group)

For the example on how to do this see: 
https://docs.microsoft.com/en-us/office/vba/api/excel.shape.ungroup

 

2.  Perform the rotation

 

3.  Regroup the ungrouped shaped objects back into their original group. 

For information on regrouping see: 
https://docs.microsoft.com/en-us/office/vba/api/excel.shaperange.regroup

 

Any pointers on how to code this in VBA would be appreciated:

1. First save the group name and all of its member shape (and subgroup) names 

2. Ungroup the grouped Object

3. Rotate one shape in the group by changing the .Rotation property value

4. Regroup the object back into it's original group name

 

@Grosner Perhaps this gives you some ideas:

Sub Demo()
    Dim oObj As Object
    Dim i As Integer
    Dim oGroup As Object
    Dim stMembers() As String
    Dim sGroupName As String
    
    'Set your grouped object to oObj first.
    'Then:
    
    sGroupName = oObj.Name
    
    Set oGroup = oObj.Ungroup
    If Not oGroup Is Nothing Then
        ReDim stMembers(1 To oGroup.Count)
        For i = 1 To oGroup.Count
            stMembers(i) = oGroup(i).Name
        Next
        ' Now rotate any member of the group and then:
' regroup ActiveSheet.DrawingObjects(1, stMembers()).Group.Name = sGroupName End If End Sub

@Jan Karel Pieterse

With a few tweaks (see attached workbook)  Jan's solution enables Excel VBA to ungroup and regroup shapes.

 

For an alternative that uses fewer lines of code, there is a way to use use the Shapes Ungroup and Shapes Regroup method (ineffective for shape groups that are contained inside another shape group):

'Group 3 Rotate - Solution B
Dim ShGroup As Shape
Sheet1.Shapes("Group 3").Ungroup
Sheet1.Shapes.range(Array("Triangle 3")).Rotation = range("H4").Value
Set ShGroup = Sheet1.Shapes.range(Array("Triangle 3")).Regroup
ShGroup.Name = "Group 3"

 I have posted both solutions in the attached workbook. Here are the illustrations of the worksheet when the zoom is and is not set to 100%Excel VBA - Rotate while Zoom = 100%Excel VBA - Rotate while Zoom = 100%Excel VBA - Rotate while Zoom = 98%Excel VBA - Rotate while Zoom = 98%

 

 

 

Excel Resizes & Moves Shape Objects when Zoom is Not 100%:

As the attached worksheet and images above illustrate, when an Excel Sheet's zoom is not 100%, Excel VBA that changes a shape contained in a grouped shape object will cause the grouped shape and its components to resize and to move if the Excel zoom is not 100%.

 

Essentially, the values for shape object's the .Left, .Top, .Height, and .Width change when a property, such as .Rotation, is changed. Why this happens is a mystery to me, but a Google search will find other instances of similar behaviors. 

 

Excel VBA Future Support

One would have hoped that Microsoft fixed these issues, however, a Help-> About done while in Excel VBA shows version Visual Basic for Applications 7.1. According to this Wikipedia article on Visual Basic For Applications the Excel VBA program first appeared in 1993 and was last updated in Office 2013 (about 6 - 7 years ago as of the date of my writing) when it was updated to version 7.1 so perhaps there is no longer any interest in improving Excel VBA. Perhaps the Visual Studio Tools for Office will provide a better alternative for Excel VBA. 

 

A Few More Excel VBA Tips for Working with Shapes

A few lines of Excel VBA Code that came in handy were:

Application.ScreenUpdating = False 'Disable Screen Updating to hide intermediate changes
ActiveWindow.zoom = 100 ' Use to reset zoom to 100% so shapes will not move around

 

@Grosner VBA future: Don't expect them to do any updates to the VBA editor. They do state that new functionality will be added to the object model. Omissions and bugs in the OM are likely to be the same for VBA and for the Visual Studio Office COM interface. If you want to be future-proof, start learning JavaScript and the new JS Office programming paradigm. That too has limitations though.