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 la mise en forme automatique, mais en utilisant Cellules et Intervalle 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! Lisez Plus exactement où, sur votre feuille de calcul, vous souhaitez obtenir 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 Intervalle fait référence à un groupe de cellules à la fois. Le format de cette fonction est Cellules (rangée, colonne).

Cela fait référence à chaque cellule de la feuille entière. C'est un 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 la colonne.

Il est très flexible de pouvoir référencer une cellule en utilisant un numéro pour la colonne et la cellule, en particulier avec des scripts pouvant faire une boucle un grand nombre de cellules (et 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 une seule cellule ou une plage spécifique de cellules simultanément. 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 par 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. L’utilité de l’utiliser, 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? ll apprendra en programmation. Vous connaissez probablement les boucles while et for, mais que produit une boucle do-while? Lire la suite .

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 des 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 en utilisant les 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 quand même 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 est cochée.

Maintenant, quand vous cliquez D'accord et revenir à la page 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 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 en dollars réel 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, telles que la suppression 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 fonction Range

Pour effectuer une boucle dans plusieurs cellules une à la fois, la fonction Cells est parfaite. Mais si vous souhaitez appliquer tous les éléments simultanément à toute une plage de cellules, 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 à consulter ceci. guide des instructions IF dans Excel. Lire la suite .

Private Sub Workbook_Open () If Worksheets ("Sheet1"). Cells (13, 3) .Value> 400000 Then ActiveSheet.Range ("D2: D12"). Interior.ColorIndex = 4 End If End End

Lorsque ceci est exécuté, 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 de 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 débutez avec 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 et bien plus encore. Nous vous présenterons la programmation visuelle de base Excel avec un projet simple. Lisez plus pour vous aussi.

.