VBA – Largeur des colonnes et hauteur des lignes en cm

Pour préparer vos feuilles Excel selon une trame en centimètres, vous pouvez utiliser l’affichage Mise en Page. Celui-ci permet d’indiquer en centimètres la hauteur des lignes et la largeur des colonnes en centimètres. Par contre, comment le faire en code VBA ?

Hauteur des lignes en centimètres

Rien de plus simple de convertir la hauteur des lignes qui s’attribue en point. En effet, il existe une fonction CentimetersToPoints qui convertit un nombre de centimètres en Point, il existe également la fonction InchesToPoints qui convertit des pouces.

Sub LignesEnCentimetres()
Dim Cm As Variant
      'Utilisation de la méthode InputBox d'Excel afin de forcer la saisie d'un nombre (Type:=1)
       Cm = Application.InputBox("Entrer la hauteur de la ligne en centimetres", "Hauteur de la ligne souhaitée", Type:=1)
       ' si l'utilisateur clique sur le bouton Annuler Cm sera égale à False
       ' la propriété Height renvoie la hauteur de la ligne mais est en lecture seule
       ' Il est donc obligatoire d'utiliser RowHeight qui renvoit également la hauteur mais permet aussi de lui attribuer une hauteur
       If Cm <> False Then Selection.RowHeight = Application.CentimetersToPoints(Cm)
End Sub

Largeur des colonnes en centimètres

Ici cela se corse… En effet la largeur par défaut d’une colonne est de 8,43 caractères standard , c’est à dire la largeur de 8,43 caractères 0 en police Arial de taille 10 (ça ne s’invente pas !!  et où ils ont décidés cela ??).

Il va donc falloir faire de l’approximation, pour cela il existe plusieurs solutions, mais en voici une que j’utilise à chaque fois que j’en ai besoin.

Sub ColonnesEnCentimetres()
     Dim Cm As Variant
     Dim TaillePoints As Long, OldLargeur As Long
     Dim Nb As Long, TailleMax As Long, TailleCurrent As Long, TailleMin As Long
     
     Cm = Application.InputBox("Entrer la largeur de la colonne en Cms", "Largeur de la colonne souhaitée", Type:=1)
     'on sort si l'utilisateur clique sur Annuler
     If Cm = False Then Exit Sub
     
     TaillePoints = Application.CentimetersToPoints(Cm)
     OldLargeur = ActiveCell.ColumnWidth
     'la largeur d'une colonne se fait en fonction de la largeur des caractères 
     ' on simule donc la largeur avec 255 caractères
     TailleMin = 0
     TailleMax = 255
     ActiveCell.ColumnWidth = TailleMax
     If TaillePoints > ActiveCell.Width Then
          MsgBox "la largeur de" & Cm & "est trop large", vbOKOnly + vbExclamation, "largeur non valable"
          ActiveCell.ColumnWidth = OldLargeur
          Exit Sub
     End If
     'maintenant on recherche par approximation la largeur la plus proche de celle demandée
     ' en divisant par 2 à chaque boucle
     ActiveCell.ColumnWidth = 127.5
     TailleCurrent = ActiveCell.ColumnWidth
     Nb = 0
     'afin de ne pas faire trop de boucles on se limite à 20 au maximum,
     ' par expérience plus de boucles ne permet pas d'avoir une meilleure précision
     ' Width est une propriété qui récupère la largeur en points, mais elle n'est qu'en lecture
     Do While (ActiveCell.Width <> TaillePoints) And (Nb < 20)
          If ActiveCell.Width < TaillePoints Then 'trop petit
               TailleMin = TailleCurrent
               'pour écrire la largeur de la colonne, nous devons utiliser la propriété ColumnWidth
               Selection.ColumnWidth = (TailleCurrent + TailleMax) / 2
          Else 'trop grand
               TailleMax = TailleCurrent
               Selection.ColumnWidth = (TailleCurrent + TailleMin) / 2
          End If
          TailleCurrent = ActiveCell.ColumnWidth
          Nb = Nb + 1 'compteur pour limiter la bouche
     Loop
End Sub 

Bonne utilisation …