Convertir toutes les formules des feuilles en valeurs

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Convertir toutes les formules en valeurs

Pour créer une copie figée d'un classeur, Excel offre une astuce de code très simple pour transformer toutes les formules de toutes les feuilles du classeur en valeurs. Certes il existe une technique manuelle pour réaliser cette conversion. Elle consiste à copier et à coller les cellules des formules en lieu et place et à exploiter la balise active qui se déclenche pour ne conserver que les valeurs. Mais lorsque ces formules sont nombreuses, éparpillées et qu'elles sont de plus présentes sur de nombreuses feuilles du classeur, ce mécanisme s'avère très fastidieux, long et pas forcément précis.



Sur le cas illustré par la capture, nous travaillons à partir d'un classeur constitué de trois feuilles présentant toutes des formules. Pour l'exemple, ces tableaux ont été remplis automatiquement grâce à une formule très simple et propagée sur toutes les cellules concernées :

=PLANCHER(ALEA.ENTRE.BORNES(500; 5000); 100)

Depuis la version 2019 d'Excel, chaque cellule portant un calcul est d'ailleurs repérée par une petite coche verte dans son angle supérieur gauche. Dès lors, si l'utilisateur clique sur un bouton de macro, depuis un ruban personnalisé, toutes les formules de toutes les feuilles sont instantanément remplacées par leurs valeurs sans formules.

Classeur Excel à télécharger
Pour développer ce code VBA de conversion et l'associer à un bouton, nous proposons de baser l'étude sur un classeur existant et offrant de multiples formules à convertir.

• Télécharger le classeur convertir-formules-valeurs.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous débouchons sur la première des trois feuilles de ce classeur. Elle relate des ventes réalisées sur les cinq premiers mois de l'année. Le tableau de la deuxième feuille est similaire. Il retranscrit les ventes réalisées de juin à octobre. La dernière feuille résume les deux derniers mois de l'année. Et chaque tableau accueille la formule aléatoire pour un remplissage automatique.

La procédure VBA dans le modèle
Pour débuter la construction de la solution, nous proposons d'accueillir le code dans un module à créer dans le modèle Excel. Ainsi et attachée à un bouton de ruban, la fonctionnalité sera disponible pour toutes les utilisations d'Excel.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, cliquer sur l'élément VBAProject(Personal.xlsb),

Il s'agit du modèle Excel. Ce fichier est associé à toute utilisation et il porte naturellement les macros VBA d'un classeur à un autre.



S'il n'est pas visible dans votre environnement, vous devez cliquer sur le bouton Afficher dans le ruban Affichage d'Excel.

• En haut de la fenêtre de l'éditeur VBA Excel, cliquer sur le menu Insertion,
• Dans la liste des propositions, choisir l'option Module,

Nous créons ainsi un nouveau module de code VBA et sa feuille vierge apparaît au centre de l'écran.

• Dans cette feuille, créer la procédure formulesEnValeurs, comme suit :

Sub formulesEnValeurs()

End Sub

C'est par son nom que nous ferons ensuite l'association avec un bouton de ruban.

Parcourir les feuilles du classeur
Nous l'avons dit, il n'est pas seulement question de transformer les formules de la première feuille. Toutes les feuilles de n'importe quel classeur sont concernées. Nous devons donc engager une boucle For Each pour les passer toutes en revue. Et pour cela, nous avons tout d'abord besoin de déclarer une variable objet représentant une feuille au sens large.

• Dans les bornes de la procédure, ajouter les instructions VBA suivantes :

Sub formulesEnValeurs()
Dim feuille As Worksheet

For Each feuille In ActiveWorkbook.Worksheets

Next feuille

End Sub

Nous déclarons tout d'abord cet objet de feuille. Et grâce à lui, nous engageons une boucle sur la collection des feuilles du classeur actif. Cette collection est renvoyée par la propriété Worksheets de l'objet ActiveWorkbook qui désigne le classeur en cours.

Transformer les formules
Désormais sur chaque feuille passée en revue, nous devons agir sur l'intégralité des cellules exploitées. Et pour cela, un objet de type Worksheet propose une propriété tout à fait intéressante. Elle se nomme UsedRange.

• Dans la boucle For Each, ajouter les instructions suivantes :

Sub formulesEnValeurs()
Dim feuille As Worksheet

For Each feuille In ActiveWorkbook.Worksheets

With feuille.UsedRange
.Value = .Value
End With

Next feuille

End Sub

Pour ne pas répéter l'objet et sa propriété à deux reprises, nous organisons le code dans un petit bloc With. Et la simple égalité sur la propriété Value produit une affectation qui colle le contenu des plages ciblées en lieu et place sans les formules.

• Enregistrer les modifications (CTRL + S),

Nous pourrions tester le code en l'état en exécutant directement la procédure.

Le bouton pour remplacer les formules
Mais comme nous l'annoncions, cette fonctionnalité peut s'avérer intéressante dans l'environnement global d'Excel. Nous proposons donc de la matérialiser par un bouton à placer dans un ruban.

• Revenir sur la feuille Excel (ALT + Tab),
• En haut de la fenêtre Excel, cliquer droit n'importe où sur le ruban actif,
• Dans le menu contextuel qui apparaît, choisir la commande Personnaliser le ruban,
• Dans la boîte de dialogue qui suit, choisir la catégorie Macros avec la liste déroulante,
• Dès lors sélectionner la macro formulesEnValeurs dans la liste du dessous,
• Puis, la glisser dans un ruban existant ou un nouveau de la liste de droite,

Il apparaît ensuite opportun d'adapter son intitulé et son icône, grâce au bouton Renommer.

• Dès lors, cliquer sur le bouton Ok de la boîte de dialogue pour valider la création du bouton,
• Cliquer alors sur ce nouveau bouton dans le ruban qui l'héberge,

Le traitement est très rapide. Si vous utilisez une version Excel 2019 ou supérieure, vous voyez instantanément disparaître les petites coches vertes annonçant des formules non protégées et ce, pour toutes les feuilles du classeur. Et en effet, si vous sélectionnez une cellule d'un montant et que vous consultez sa barre de formule, vous constatez que seule la valeur numérique subsiste. Toutes les formules ont été converties.