En utilisant VBA pour créer une carte de feuille de travail - des nuls

Avez-vous déjà essayé de donner un sens à un classeur Excel peu familier (et compliqué)? Il serait utile de voir une carte qui fournit une vue d'ensemble des cellules qui contiennent des constantes et des cellules qui contiennent des valeurs.

Vous pouvez créer un utilitaire VBA qui génère une carte de la feuille de calcul active. La carte est générée sur une nouvelle feuille de calcul et se compose de cellules codées par couleur qui vous permettent d'identifier rapidement les valeurs, le texte et les formules.

Ci-dessous, vous voyez un exemple d'une telle carte. Les cellules qui contiennent du texte sont vertes, celles qui contiennent une valeur numérique sont jaunes et les cellules contenant des formules sont rouges. Une telle carte peut vous aider à repérer les erreurs potentielles. Par exemple, si une formule dans un bloc de formules a été écrasée par une valeur, cette cellule ressortira dans la vue de la carte (comme dans la cellule Q11 dans l'exemple).

Le code VBA QuickMap

La procédure VBA qui génère la carte de feuille de travail est répertoriée ci-dessous. Si vous souhaitez utiliser cet utilitaire, copiez le code et collez-le dans un module VBA. Ensuite, activez une feuille de calcul et exécutez le sous-programme QuickMap.

Sub QuickMap () Dim FormulaCells Variant Dim TextCells Comme Variant Dim NumberCells As Variant Dim Area As Range Si TypeName (ActiveSheet) "Worksheet" Then Exit Sub 'Créer des variables d'objet pour des sous-ensembles de cellules Sur Error Resume Next Set FormulaCells = Range ("A1"). SpecialCells _ (xlFormulas, xlNumbers + xlTextValues ​​+ xlLogical) Définit TextCells = Range ("A1"). SpecialCells (xlConstants, xlTextValues) Set NumberCells = Range ("A1"). SpecialCells (xlConstants, xlNumbers) On Error GoTo 0 'Ajoute une nouvelle feuille et la formate Sheets. Ajouter avec des cellules. ColumnWidth = 2. Police. Taille = 8. HorizontalAlignment = xlCenter End With Application. ScreenUpdating = False 'Effectuez les cellules de la formule If Not IsEmpty (FormulaCells) puis pour chaque zone dans FormulaCells. Zones avec ActiveSheet. Plage (zone, adresse). Valeur = "F". Intérieur. ColorIndex = 3 Fin avec la fin de la zone suivante Si les cellules de texte ne sont pas IsEmpty (TextCells), puis pour chaque zone dans TextCells. Zones avec ActiveSheet. Plage (zone, adresse). Valeur = "T". Intérieur. ColorIndex = 4 Terminer avec la fin de la zone suivante Si 'Faire les cellules numériques si non IsEmpty (NumberCells) puis pour chaque zone dans NumberCells. Zones avec ActiveSheet. Plage (zone, adresse). Valeur = "N". Intérieur. ColorIndex = 6 Fin avec la zone suivante Fin Si End Sub

Fonctionnement

La procédure vérifie d'abord que la feuille active est une feuille de calcul. Si ce n'est pas le cas, il y a une sortie rapide sans autre action. Lorsque la feuille active est une feuille de calcul, la procédure crée trois variables d'objet en utilisant la méthode SpecialCells pour identifier les différents types de cellules.La méthode SpecialCells est très utile. Si vous ne le connaissez pas, consultez le fichier d'aide en ligne d'Excel. Notez l'utilisation de On Error Resume Next. Cela permet d'éviter l'erreur qui se produit si aucune cellule n'est qualifiée - par exemple, si la feuille de calcul n'a pas de formules.

Ensuite, la procédure ajoute une nouvelle feuille de calcul, réduit la largeur de la cellule et définit l'alignement horizontal sur le centre. Cette étape est cosmétique. Le sous-marin désactive ensuite la mise à jour de l'écran pour accélérer un peu les choses.

Les trois blocs de code suivants traitent les cellules. Si aucune cellule n'est qualifiée, la variable d'objet est vide, donc les sous-tests pour cela. Ensuite, la routine parcourt chaque objet Area in the Range et formate la cellule. Vous pouvez facilement personnaliser cette partie du sous-programme pour appliquer une mise en forme différente.

Découvrez le complément Power Utility Pak pour une version beaucoup plus sophistiquée de cet utilitaire.