Comprendre les cellules Excel et les fonctions de plage dans VBA

Excel est puissant. Si vous l’utilisez beaucoup, vous connaissez probablement déjà beaucoup d’astuces avec les formules ou le formatage automatique, mais en utilisant Cellules et Gamme fonctions dans VBA, vous pouvez améliorer vos analyses Excel à un tout autre niveau.

Le problème lié à l’utilisation des fonctions Cells et Range dans VBA est qu’aux niveaux avancés, la plupart des gens ont du mal à comprendre comment ces fonctions fonctionnent réellement. Les utiliser peut devenir très déroutant. Voici comment vous pouvez les utiliser d'une manière que vous n'avez probablement jamais imaginée.

La fonction des cellules

Les fonctions Cellules et Plage vous permettent d'indiquer à votre script VBA 4 Erreurs que vous pouvez éviter lors de la programmation de macros Excel avec VBA 4 Erreurs que vous pouvez éviter lors de la programmation de macros Excel avec VBA Le code et les macros simples sont la clé des superpuissances Microsoft Excel. Même les non-programmeurs peuvent facilement ajouter des fonctionnalités impressionnantes à leurs feuilles de calcul avec Virtual Basics for Applications (VBA). Il suffit d'éviter ces erreurs de débutant en programmation! exactement où vous voulez obtenir sur votre feuille de calcul, ou placer des données. La principale différence entre les deux cellules est ce à quoi elles font référence.

Cellules référence généralement une seule cellule à la fois, tandis que Gamme fait référence à un groupe de cellules à la fois. Le format de cette fonction est Cellules (ligne, colonne).

Cela fait référence à chaque cellule de la feuille entière. C'est le seul exemple où la fonction Cells ne fait pas référence à une seule cellule:

Feuilles de calcul ("Sheet1"). Cellules

Ceci fait référence à la troisième cellule à partir de la gauche, dans la rangée supérieure. Cellule C1:

Feuilles de calcul ("Sheet1"). Cellules (3)

Le code suivant fait référence à la cellule D15:

Feuilles de calcul ("Sheet1"). Cellules (15,4)

Si vous le souhaitez, vous pouvez également référencer la cellule D15 avec “Cellules (15,”ré”)”-vous êtes autorisé à utiliser la lettre de colonne.

Il est très flexible de pouvoir référencer une cellule en utilisant un numéro de colonne et de cellule, en particulier avec des scripts pouvant faire une boucle un grand nombre de cellules (et y effectuer des calculs) très rapidement. Nous y reviendrons plus en détail ci-dessous.

La fonction de portée

À bien des égards, la fonction Range est beaucoup plus puissante que Cellules, car elle vous permet de référencer simultanément une seule cellule ou une plage spécifique de cellules. Vous n'allez pas vouloir parcourir une fonction Range, car les références des cellules ne sont pas des nombres (sauf si vous intégrez la fonction Cells à l'intérieur de celle-ci)..

Le format de cette fonction est Range (Cell # 1, Cell # 2). Chaque cellule peut être désignée par une lettre.

Regardons quelques exemples.

Ici, la fonction de plage fait référence à la cellule A5:

Feuilles de calcul ("Sheet1"). Range ("A5")

Ici, la fonction de plage fait référence à toutes les cellules entre A1 et E20:

Feuilles de calcul ("Sheet1"). Range ("A1: E20")

Comme mentionné ci-dessus, il n'est pas nécessaire d'utiliser des affectations de cellules de lettres numériques. Vous pouvez en fait utiliser deux fonctions Cells dans une fonction Range pour identifier une plage sur la feuille, comme ceci:

 Avec des feuilles de calcul ("Sheet1") .Range (.Cells (1, 1), _ .Cells (20, 5)) End With 

Le code ci-dessus fait référence à la même plage que la plage (“A1: E20”) la fonction fait. Son utilité, c’est que cela vous permettrait d’écrire du code qui fonctionne de manière dynamique avec des plages utilisant des boucles. Comment les boucles Do-While fonctionnent-elles en programmation informatique? Comment les boucles Do-While fonctionnent-elles en programmation informatique? Les boucles sont l’un des premiers types de contrôle ll apprendra en programmation. Vous connaissez probablement les boucles while et for, mais que réalise une boucle do-while? .

Maintenant que vous avez compris comment formater les fonctions Cells et Range, voyons comment utiliser ces fonctions de manière créative dans votre code VBA..

Traitement de données avec la fonction de cellules

La fonction Cells est particulièrement utile lorsque vous souhaitez effectuer une formule complexe sur plusieurs plages de cellules. Ces plages peuvent également exister sur plusieurs feuilles..

Prenons un exemple simple. Supposons que vous dirigiez une équipe de vente de 11 personnes et que vous souhaitiez examiner chaque mois leurs performances..

Vous pourriez avoir Feuille1 qui suit leur nombre de ventes, et leur volume de ventes.

Sur Feuille2 est l'endroit où vous suivez leur évaluation des commentaires des clients de votre entreprise au cours des 30 derniers jours.

Si vous souhaitez calculer le bonus sur la première feuille à l'aide des valeurs des deux feuilles, vous pouvez procéder de différentes manières. Vous pouvez écrire une formule dans la première cellule qui effectue le calcul en utilisant des données sur les deux feuilles et la faire glisser vers le bas. Ça va marcher.

Une alternative à cela consiste à créer un script VBA que vous voulez soit exécuter à chaque ouverture de la feuille, soit déclenché par un bouton de commande sur la feuille afin que vous puissiez contrôler son calcul. Vous pouvez utiliser un script VBA pour extraire toutes les données de vente d'un fichier externe..

Alors pourquoi ne pas simplement déclencher les calculs pour la colonne bonus dans le même script à ce moment-là?

La fonction des cellules en action

Si vous n'avez jamais écrit VBA dans Excel, vous devez activer l'élément de menu Développeur. Pour ce faire, allez à Fichier > Les options. Cliquer sur Personnaliser le ruban. Enfin, choisissez Developer dans le volet de gauche, Ajouter dans le volet de droite et assurez-vous que la case à cocher est sélectionnée.

Maintenant, quand vous cliquez D'accord et revenir à la feuille principale, vous verrez l'option de menu Développeur.

Vous pouvez utiliser le Insérer menu pour insérer un bouton de commande, ou cliquez simplement sur Voir le code pour commencer à coder.

Dans cet exemple, le script sera exécuté à chaque fois que le classeur est ouvert. Pour ce faire, il suffit de cliquer sur Voir le code dans le menu du développeur et collez la nouvelle fonction suivante dans la fenêtre de code.

Private Sub Workbook_Open () End Sub

Votre fenêtre de code ressemblera à quelque chose comme ça.

Vous êtes maintenant prêt à écrire le code pour gérer le calcul. En utilisant une seule boucle, vous pouvez parcourir les 11 employés et, avec la fonction Cellules, extraire les trois variables nécessaires au calcul..

Rappelez-vous que la fonction Cells a une ligne et une colonne en tant que paramètres pour identifier chaque cellule. Nous ferons “X” la ligne, utilisez un numéro pour demander les données de chaque colonne. Le nombre de lignes correspond au nombre d'employés. Le nombre d'identificateurs de colonne sera donc 2 pour le nombre de ventes, 3 pour le volume des ventes et 2 de la feuille 2 pour le score de retour..

Le calcul final utilise les pourcentages suivants pour ajouter jusqu'à 100% du total des points bonus. Il est basé sur un nombre de ventes idéal de 50, un volume de ventes de 50 000 $ et un score de rétroaction de 10..

• (Chiffre d'affaires / 50) x 0.4
• (Volume des ventes / 50 000) x 0,5
• (Score de commentaires / 10) x 0.1

Cette approche simple donne aux employés des ventes un bonus pondéré. Pour un compte de 50, un volume de 50 000 $ et un score de 10, ils obtiennent la totalité du bonus maximum pour le mois. Cependant, tout ce qui est sous parfait sur n'importe quel facteur réduit le bonus. Quelque chose de mieux que l'idéal augmente le bonus.

Voyons maintenant comment toute cette logique peut être exploitée dans un script VBA très court et très simple:

Private Sub Workbook_Open () Pour x = 2 à 12 feuilles de calcul ("Sheet1"). Cellules (x, 4) = (Feuilles de calcul ("Sheet1"). Cells (x, 2) .Value / 50) * 0.4 _ + (Feuilles de calcul ("Sheet1"). Cellules (x, 3) .Value / 50000) * 0.5 _ + (feuilles de calcul ("Sheet2"). Cellules (x, 2) .Value / 10) * 0.1 _ Suivant x End Sub 

Voici à quoi ressemblera la sortie de ce script.

Si vous souhaitez que la colonne Bonus affiche le bonus réel en dollars plutôt que le pourcentage, vous pouvez le multiplier par le montant maximal du bonus. Mieux encore, placez ce montant dans une cellule d'une autre feuille et faites-le référence dans votre code. Cela faciliterait la modification ultérieure de la valeur sans avoir à modifier votre code..

La beauté de la fonction Cells réside dans le fait que vous pouvez créer une logique assez créative pour extraire des données de beaucoup de cellules à travers de nombreuses feuilles différentes, et effectuer des calculs assez complexes avec eux.

Vous pouvez effectuer toutes sortes d’actions sur les cellules à l’aide de la fonction Cellules, comme l’effacement des cellules, la modification du format de la police, etc..

Pour explorer tout ce que vous pouvez faire, consultez la page Microsoft MSDN pour l'objet Cells..

Formatage des cellules avec la fonction Range

Pour effectuer une boucle dans plusieurs cellules une à la fois, la fonction Cells est parfaite. Mais si vous voulez appliquer quelque chose à une plage entière de cellules en même temps, la fonction Range est beaucoup plus efficace..

Un cas d'utilisation pourrait être de formater une plage de cellules à l'aide d'un script, si certaines conditions sont remplies..

Par exemple, supposons que si le total des ventes de tous les employés des ventes dépasse 400 000 USD, vous souhaitez mettre en surbrillance toutes les cellules de la colonne des bonus en vert pour indiquer que l'équipe a gagné un bonus d'équipe supplémentaire..

Voyons comment procéder avec une instruction IF. Comment utiliser les instructions IF dans Microsoft Excel. Comment utiliser les instructions IF dans Microsoft Excel. Que vous soyez un expert expérimenté ou un débutant en tableur, vous aurez intérêt à vérifier ceci: guide pour les instructions IF dans Excel. .

Private Sub Workbook_Open () Si Feuilles de calcul ("Feuille1"). Cellules (13, 3) .Value> 400000 Puis ActiveSheet.Range ("D2: D12"). Interior.ColorIndex = 4 End If End End

Lorsque cette option est exécutée, si la cellule dépasse l'objectif de l'équipe, toutes les cellules de la plage seront remplies en vert.

Ceci n'est qu'un exemple simple des nombreuses actions que vous pouvez effectuer sur des groupes de cellules à l'aide de la fonction Range. Les autres choses que vous pouvez faire incluent:

• Appliquer un contour autour du groupe
• Vérifier l'orthographe du texte dans une plage de cellules
• Effacer, copier ou couper des cellules
• Rechercher dans une plage avec le “Trouver” méthode
• Beaucoup plus

Assurez-vous de lire la page Microsoft MSDN pour l’objet Range pour voir toutes les possibilités..

Prenez Excel au niveau suivant

Maintenant que vous comprenez les différences entre les fonctions Cells et Range, il est temps d’amener votre script VBA au niveau supérieur. L'article de Dann sur l'utilisation des fonctions de comptage et d'ajout dans Excel vous permettra de créer des scripts encore plus avancés capables d'accumuler très rapidement des valeurs dans tous vos ensembles de données..

Et si vous commencez à utiliser VBA dans Excel, n'oubliez pas que nous avons un guide d'introduction fantastique à Excel VBA Le didacticiel de programmation Excel VBA pour les débutants Le didacticiel de programmation Excel VBA pour les débutants VBA est un outil puissant de Microsoft Office. Vous pouvez l'utiliser pour automatiser des tâches avec des macros, définir des déclencheurs, etc. Nous vous présenterons la programmation visuelle de base Excel avec un projet simple. pour toi aussi.