1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
| [
Sub test()
Dim plage As Range
Set plage = Range("B4:C14")
'Set plage = Range("f3:h9")
pos = DimAndCoordonnees(plage, ActiveSheet.Pictures("pinguouins"))
With ActiveSheet.Pictures("pinguouins")
.Width = pos(2)
.Height = pos(3)
.Left = pos(0)
.Top = pos(1)
'.Placement = 1
End With
End Sub
'la fonction calcule sans toucher a la shape
Function DimAndCoordonnees(RnG As Range, ObJ As Object, Optional MinSpace As Double = 2)
Dim nW#, nH#, wR#, hR#, newT#, newL#, nL, nT#
ratio = ObJ.Width / ObJ.Height ' ration de l'object
wR = RnG.Width ' width range
hR = RnG.Height ' height range
If (wR / hR < ratio) Then
nW = wR - MinSpace
nH = nW / ratio
Else
nH = hR - (MinSpace / ratio)
nW = nH * ratio
End If
nL = RnG.Left + ((wR - nW) / 2)
nT = RnG.Top + ((hR - nH) / 2)
DimAndCoordonnees = Array(nL, nT, nW, nH)
End Function |
Partager