{"id":5678,"date":"2015-02-01T15:08:19","date_gmt":"2015-02-01T13:08:19","guid":{"rendered":"http:\/\/www.auditsi.eu\/?p=5678"},"modified":"2019-02-03T23:00:28","modified_gmt":"2019-02-03T22:00:28","slug":"excel-et-vba-manipuler-des-plages-de-cellules-dans-des-variables-tableaux","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=5678","title":{"rendered":"EXCEL et VBA : Manipuler des plages de cellules dans des variables tableaux"},"content":{"rendered":"<p style=\"text-align: justify;\">Le <a title=\"Articles sur le langage de programmation VBA\" href=\"http:\/\/www.auditsi.eu\/?tag=vba\">VBA<\/a> est un langage de programmation puissant, int\u00e9gr\u00e9 \u00e0 l&#8217;ensemble des logiciels du pack OFFICE, qui permet d&#8217;encha\u00eener de nombreux calculs. Sous Excel, ces calculs portent fr\u00e9quemment sur des plages de cellules.<\/p>\n<p style=\"text-align: justify;\">Or, les op\u00e9rations de lecture et d&#8217;\u00e9criture sur des plages Excel de grande taille ralentissent de mani\u00e8re significative l&#8217;ex\u00e9cution des programmes VBA ; il en est ainsi des traitements sur des \u00e9critures comptables qui peuvent repr\u00e9senter des volumes cons\u00e9quents m\u00eame pour des PME. Pour s&#8217;affranchir de cet inconv\u00e9nient, il est possible d&#8217;utiliser les variables tableaux.\u00a0L&#8217;utilisation des variables tableaux est une des techniques d&#8217;optimisation du code.<\/p>\n<p><a style=\"text-align: justify;\" href=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2014\/11\/Liste-clients-RFA.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5679\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2014\/11\/Liste-clients-RFA.png\" alt=\"Liste de clients : calcul des remises de fin d'ann\u00e9e\" width=\"268\" height=\"439\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2014\/11\/Liste-clients-RFA.png 268w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2014\/11\/Liste-clients-RFA-183x300.png 183w\" sizes=\"auto, (max-width: 268px) 100vw, 268px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Comme nous le verrons plus loi, l&#8217;usage d&#8217;une variable tableau est parfaitement appropri\u00e9 pour stocker une plage de cellules. Principe g\u00e9n\u00e9ral : le contenu de la plage de cellules est copi\u00e9 dans une variable tableau, puis les donn\u00e9es contenues dans la variable tableau sont retrait\u00e9es par le programme et enfin retranscrites dans les cellules du tableau Excel.<\/p>\n<p style=\"text-align: justify;\">Nous appuierons notre propos sur l&#8217;exemple d&#8217;un programme de calcul de remises de fin d&#8217;ann\u00e9es (RFA). La table des chiffres d&#8217;affaires (CA) r\u00e9alis\u00e9s avec chaque client est rapproch\u00e9e de la grille de RFA, la RFA est calcul\u00e9e pour chaque client selon la formule CA x Taux de RFA. Dans notre exemple, la table CA est comprise dans la plage de cellules A6 \u00e0 C8000 :<\/p>\n<ul>\n<li style=\"text-align: justify;\">le premier champ (colonne A) \u00e9tant le code client,<\/li>\n<li style=\"text-align: justify;\">le deuxi\u00e8me (colonne B), le montant du chiffre d&#8217;affaires annuel et<\/li>\n<li style=\"text-align: justify;\">le dernier (colonne C), le montant de la RFA (que le programme doit calculer).<\/li>\n<\/ul>\n<p style=\"text-align: justify;\"><em>1. D\u00e9claration de la variable tableau<\/em><\/p>\n<p style=\"text-align: justify;\">Les variables tableaux sont une cat\u00e9gorie de\u00a0variables qui permettent de stocker non pas une seule donn\u00e9e comme une simple <a title=\"Initiation \u00e0 la programmation en Basic \u2013 ETAPE 3 : D\u00e9claration des variables\" href=\"http:\/\/www.auditsi.eu\/?p=1642\">variable<\/a> (pour m\u00e9moire, une variable est un espace m\u00e9moire accessible par un programme \u00e0 l&#8217;aide d&#8217;un nom ; selon son type, une variable peut stocker une donn\u00e9e : une cha\u00eene de caract\u00e8re, un montant chiffr\u00e9&#8230;) mais une des s\u00e9ries de donn\u00e9es sur une ou plusieurs dimensions. C&#8217;est la possibilit\u00e9 de stockage en deux dimensions qui est mise \u00e0 profit pour traiter des plages de cellules ; en effet, une plage de cellules Excel forme un tableau \u00e0 deux dimensions (colonnes et lignes formant chacune une dimension).<\/p>\n<p style=\"text-align: justify;\">Il est tout d&#8217;abord n\u00e9cessaire de d\u00e9clarer la variable tableau (qui se nommera ici TableauDonn\u00e9es) :<\/p>\n<pre class=\"lang:default decode:true\">Dim TableauDonn\u00e9es As Variant<\/pre>\n<p style=\"text-align: justify;\">Le type <em>Variant<\/em> autorise le stockage de n&#8217;importe quelle sorte de donn\u00e9es (texte et num\u00e9rique), c&#8217;est pourquoi il est syst\u00e9matiquement retenu pour stocker des plages de donn\u00e9es (celles-ci accueillant n&#8217;importe quel genre de donn\u00e9es).<\/p>\n<p style=\"text-align: justify;\"><em>2. Stockage de la plage de cellules dans la variable tableau<\/em><\/p>\n<p style=\"text-align: justify;\">Le stockage de la plage de cellules s&#8217;effectue simplement. La lecture des donn\u00e9es est op\u00e9r\u00e9e par le signe \u00e9gal dispos\u00e9 entre d&#8217;une part la variable tableau et d&#8217;autre part la plage de donn\u00e9es :<\/p>\n<pre class=\"lang:default decode:true\">TableauDonn\u00e9es = ActiveWorkbook.Sheets(1).Range(\"A6:C8000\").Value<\/pre>\n<p style=\"text-align: justify;\">L&#8217;appel de la plage de donn\u00e9es est effectu\u00e9 \u00e0 l&#8217;aide des instructions suivantes :<\/p>\n<ul>\n<li style=\"text-align: justify;\"><em>ActiveWorkbook<\/em> : classeur Excel en cours d&#8217;utilisation,<\/li>\n<li style=\"text-align: justify;\"><em>.Sheets(1)<\/em> : premi\u00e8re feuille de calcul Excel en partant de la gauche,<\/li>\n<li style=\"text-align: justify;\"><em>.Range(&#8220;A6:C8000&#8221;)<\/em> : plage de cellules A6 \u00e0 C8000,<\/li>\n<li style=\"text-align: justify;\"><em>.Value<\/em> : le traitement portera sur les valeurs et non sur les formules (.<em>Formula<\/em>) contenues dans la plage de cellules.<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Pour r\u00e9sumer, la variable TableauDonn\u00e9es stockera les valeurs de la plage de cellules A6 \u00e0 C8000 de la premi\u00e8re feuille de calcul du classeur Excel actif.<\/p>\n<p style=\"text-align: justify;\"><em>3. Traitement des donn\u00e9es<br \/>\n<\/em><\/p>\n<p style=\"text-align: justify;\">Les donn\u00e9es \u00e9tant stock\u00e9es dans la variable tableau TableauDonn\u00e9es, le programme n&#8217;a plus qu&#8217;\u00e0 lancer les traitements n\u00e9cessaires.<\/p>\n<p style=\"text-align: justify;\">Dans notre cas, la table de CA (stock\u00e9e dans la variable nomm\u00e9e TableauDonn\u00e9es) sera oppos\u00e9e \u00e0 la grille de RFA suivante :<\/p>\n<table>\n<tbody>\n<tr>\n<td style=\"text-align: center;\">CA<\/td>\n<td style=\"text-align: center;\">Taux de remise<\/td>\n<\/tr>\n<tr>\n<td>&gt; 100 K\u20ac<\/td>\n<td style=\"text-align: center;\">15 %<\/td>\n<\/tr>\n<tr>\n<td>&gt; 50 K\u20ac<\/td>\n<td style=\"text-align: center;\">10 %<\/td>\n<\/tr>\n<tr>\n<td>&gt; 25 K\u20ac<\/td>\n<td style=\"text-align: center;\">5 %<\/td>\n<\/tr>\n<tr>\n<td>&lt; 25 K\u20ac<\/td>\n<td style=\"text-align: center;\">0 %<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: justify;\">Le rapprochement entre la table des CA et la grille de RFA est effectu\u00e9 \u00e0 l&#8217;aide de simples <a title=\"Initiation \u00e0 la programmation en Basic \u2013 ETAPE 8 : Tests conditionnels\" href=\"http:\/\/www.auditsi.eu\/?p=1797\">tests conditionnels<\/a> (<em>If&#8230; Then&#8230; ElseIf&#8230;<\/em> : Si condition r\u00e9alis\u00e9e Alors r\u00e9alise l&#8217;action 1 Sinon l&#8217;action 2) qui sch\u00e9matiquement se repr\u00e9sentent ainsi :<\/p>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2012\/06\/Test-conditionnel.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1799\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2012\/06\/Test-conditionnel.png\" alt=\"Test conditionnel\" width=\"469\" height=\"309\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2012\/06\/Test-conditionnel.png 748w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2012\/06\/Test-conditionnel-300x197.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2012\/06\/Test-conditionnel-1x1.png 1w\" sizes=\"auto, (max-width: 469px) 100vw, 469px\" \/><\/a>Voici le code VBA de la proc\u00e9dure (<em>Sub&#8230; End Sub<\/em>) CalculRFA :<\/p>\n<pre class=\"lang:default decode:true \">Sub CalculRFA\r\n  Dim Taux As Double\r\n  Dim CA As Double\r\n  Dim i As Integer\r\n  For i = 1 to UBound(TableauDonn\u00e9es)\r\n    Taux = 0\r\n    CA = TableauDonn\u00e9es(i,2)\r\n    If CA &gt; 100000 Then Taux = 0.15\r\n    ElseIf CA &gt; 50000 Then Taux = 0.1\r\n    ElseIf CA &gt; 25000 then Taux = 0.05\r\n    End If\r\n    TableauDonn\u00e9es(i,3) = CA * Taux\r\n  Next i\r\nEnd Sub<\/pre>\n<p style=\"text-align: justify;\">La proc\u00e9dure CalculRFA effectue les op\u00e9rations suivantes :<\/p>\n<ul>\n<li style=\"text-align: justify;\">D\u00e9finit les variables <em>Taux<\/em> (taux de RFA \u00e0 appliquer au client en cours de traitement) et <em>CA<\/em> (chiffre d&#8217;affaires du client en cours) ainsi que la variable <em>i<\/em> qui correspondra au num\u00e9ro de la ligne de la table de CA en cours de traitement,<\/li>\n<li style=\"text-align: justify;\">Fait tourner une <a title=\"Initiation \u00e0 la programmation en Basic \u2013 ETAPE 7 : Boucles\" href=\"http:\/\/www.auditsi.eu\/?p=1696\">boucle compteur<\/a> <em>For&#8230; To&#8230; Next<\/em> qui passe en revue toutes les occurrences de la table CA (TableauDonn\u00e9es), de la premi\u00e8re (<em>1<\/em>) \u00e0 la derni\u00e8re (<em>UBound(TableauDonn\u00e9es)<\/em>),<\/li>\n<li style=\"text-align: justify;\">Le CA du client en cours de traitement est lu \u00e0 partir de la table CA \u00e0 l&#8217;aide de l&#8217;instruction :<\/li>\n<\/ul>\n<pre class=\"lang:default decode:true\" style=\"padding-left: 120px;\">CA = TableauDonn\u00e9es(i,2)<\/pre>\n<ul style=\"list-style-type: disc;\">\n<li>Le CA est oppos\u00e9 \u00e0 la grille de RFA \u00e0 l&#8217;aide des tests conditionnels,<\/li>\n<li>Le montant de la RFA est stock\u00e9 dans la 3\u00e8me colonne de la table CA :<\/li>\n<\/ul>\n<pre class=\"lang:default decode:true\">    TableauDonn\u00e9es(i,3) = CA * Taux\r\n<\/pre>\n<ul style=\"list-style-type: disc;\">\n<li style=\"text-align: justify;\">Ceci fait, le programme passe au client suivant (<em>Next i<\/em>).<\/li>\n<\/ul>\n<p style=\"text-align: justify;\"><em>4. Restitution des donn\u00e9es dans Excel<br \/>\n<\/em><\/p>\n<p style=\"text-align: justify;\">Le calcul des RFA \u00e9tant achev\u00e9, il est temps de transf\u00e9rer la table de CA dans Excel :<\/p>\n<pre class=\"lang:default decode:true\">ActiveWorkbook.Sheets(1).Range(\"A6:C8000\").Value = TableauDonn\u00e9es<\/pre>\n<p style=\"text-align: justify;\">Vous noterez que la construction de cette instruction d&#8217;\u00e9criture d&#8217;une plage de donn\u00e9es dans Excel est la m\u00eame que celle de la lecture vue au point 2 ci-avant, les deux termes situ\u00e9s de part et d&#8217;autre de l&#8217;\u00e9galit\u00e9 (=) \u00e9tant invers\u00e9s.<\/p>\n<p style=\"text-align: justify;\"><em>Pour conclure :<\/em><\/p>\n<p style=\"text-align: justify;\">J&#8217;utilise cette technique d&#8217;optimisation avec succ\u00e8s dans mes <a title=\"R\u00e9alisations personnelles\" href=\"http:\/\/www.auditsi.eu\/?page_id=1355\">diff\u00e9rents projets<\/a> qui traitent de grandes quantit\u00e9s de donn\u00e9es (plusieurs dizaines de milliers de lignes). Les gains de temps sont cons\u00e9quents.<\/p>\n<p style=\"text-align: justify;\">Approfondir le sujet : <a title=\"Programmer en VBA\" href=\"http:\/\/www.auditsi.eu\/?cat=1569\">programmer en VBA<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"EXCEL%20et%20VBA%20%3A%20Manipuler%20des%20plages%20de%20cellules%20dans%20des%20variables%20tableaux\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Le VBA est un langage de programmation puissant, int\u00e9gr\u00e9 \u00e0 l&#8217;ensemble des logiciels du pack OFFICE, qui permet d&#8217;encha\u00eener de nombreux calculs. Sous Excel, ces calculs portent fr\u00e9quemment sur des plages de cellules. Or, les op\u00e9rations de lecture et d&#8217;\u00e9criture sur des plages Excel de grande taille ralentissent de mani\u00e8re significative l&#8217;ex\u00e9cution des programmes VBA &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=5678\" class=\"more-link\">Continue reading &lsquo;EXCEL et VBA : Manipuler des plages de cellules dans des variables tableaux&rsquo; &raquo;<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"EXCEL%20et%20VBA%20%3A%20Manipuler%20des%20plages%20de%20cellules%20dans%20des%20variables%20tableaux\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[7,472,1326,1569,215],"tags":[1565,391,1562,273,249,235,1633,387,223,225,1217,1561,263,1564,1563,389,222,1566,261,1133,619,840,390,1641],"class_list":["post-5678","post","type-post","status-publish","format-standard","hentry","category-breves","category-cycle-clients-controle-des-comptes","category-maitriser-excel","category-programmer-en-vba","category-vba","tag-formula","tag-sheets","tag-activeworkbook","tag-boucles","tag-cells","tag-dim-as","tag-excel","tag-for-to-next","tag-if-then-elseif","tag-microsoft-office","tag-optimisation-du-code","tag-plage-de-cellules","tag-range","tag-remise-de-fin-dannee","tag-rfa","tag-sub-end-sub","tag-tests-conditionnels","tag-ubound","tag-value","tag-variable-tableau","tag-variable-tableau-multidimensionnel","tag-variables","tag-variant","tag-vba"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/5678","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5678"}],"version-history":[{"count":26,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/5678\/revisions"}],"predecessor-version":[{"id":7988,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/5678\/revisions\/7988"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5678"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5678"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5678"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}