Jun 26 2019 08:04 AM
A shape group is getting resized after VBA rotation.
The attached Excel 2016 / Office 365 workbook contains a worksheet with 2 sets of shapes:
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.
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.
Jun 26 2019 08:25 AM
Jun 26 2019 08:32 AM - edited Jun 26 2019 08:38 AM
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.
Jun 26 2019 08:34 AM
Hi,
I've tried to rotate them, but they both rotate correctly as the screenshot below!
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
Jun 26 2019 08:45 AM - edited Jun 26 2019 08:48 AM
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.
Jun 26 2019 11:18 AM - edited Jun 26 2019 11:19 AM
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
Jun 26 2019 11:40 AM - edited Jun 26 2019 12:24 PM
Thank you for confirming this issue.
I will provide feedback to Microsoft.
It would be nice if anyone has an actual workaround.
Thanks!
Jun 27 2019 02:12 AM
Jun 27 2019 07:54 AM
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
Jun 27 2019 08:29 AM
@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
Jun 28 2019 11:14 AM - edited Jun 28 2019 11:19 AM
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 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.
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 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
Jul 02 2019 01:04 AM
@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.