Forum Discussion

Grosner's avatar
Grosner
Copper Contributor
Jun 26, 2019

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

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 an...
developer
excel
Macros and VBA
office 365
Grosner's avatar
Grosner
Copper Contributor
Jun 27, 2019

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

 

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jun 27, 2019

    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
    • Grosner's avatar
      Grosner
      Copper Contributor
      Jun 28, 2019

      JKPieterse

      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 = 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 https://en.wikipedia.org/wiki/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 https://docs.microsoft.com/en-us/visualstudio/vsto/create-vsto-add-ins-for-office-by-using-visual-studio?view=vs-2019 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

       

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Jul 02, 2019

        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.

Resources