excel对指定的表格图片自动调整大小vba代码

Sub autoResizeImage() '定义变量 Dim shp As Shape Dim rng As Range Dim ratio As Double '设置表格区域 Set rng = Range("B2:F10") '循环表格区域中的所有图片 For Each shp In ActiveSheet.Shapes If Not Intersect(shp.TopLeftCell, rng) Is Nothing And shp.Type = msoPicture Then '计算图片比例 ratio = shp.Width / shp.Height '调整图片大小 If rng.Width / ratio > rng.Height Then shp.Height = rng.Height shp.Width = rng.Height * ratio Else shp.Width = rng.Width shp.Height = rng.Width / ratio End If End If Next shp End Sub