{"id":1540,"date":"2023-11-17T12:48:02","date_gmt":"2023-11-17T12:48:02","guid":{"rendered":"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/"},"modified":"2023-12-06T05:57:36","modified_gmt":"2023-12-06T05:57:36","slug":"lus-bladeren","status":"publish","type":"page","link":"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/","title":{"rendered":"Hoe door alle bladen heen bladeren met vba in excel?"},"content":{"rendered":"<div class=\"excel-voor-de-inhoud\" id=\"excel-2746348382\"><script type=\"text\/javascript\">\r\n\tatOptions = {\r\n\t\t'key' : 'c1158f160081d6540a8409e6925dab94',\r\n\t\t'format' : 'iframe',\r\n\t\t'height' : 250,\r\n\t\t'width' : 300,\r\n\t\t'params' : {}\r\n\t};\r\n<\/script>\r\n<script type=\"text\/javascript\" src=\"\/\/www.highperformanceformat.com\/c1158f160081d6540a8409e6925dab94\/invoke.js\"><\/script><\/div> <p>Er zijn twee manieren om alle werkbladen in een werkmap te doorlopen.<\/p><ul><li> VOOR DE VOLGENDE LUS<\/li><li> VOOR ELKE KRUL<\/li><\/ul><h2 class=\"wp-block-heading\"> 1. Gebruik voor elke lus<\/h2><p> Zoals u weet, kunt u met FOR EACH alle objecten in de verzameling doorlopen, en in een werkmap zijn werkbladen een verzameling van alle werkbladen.<\/p><p> <strong>Gebruik de volgende stappen:<\/strong><\/p><ol><li> Declareer eerst een variabele die verwijst naar een werkblad voor de lus. <br><img loading=\"lazy\" decoding=\"async\" title=\"1-declareer-een-variabele-om-te-verwijzen\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-declarer-une-variable-a-referencer.png\" sizes=\"auto, \" srcset=\"\" alt=\"declare-a-variable-to-refer\" width=\"387\" height=\"259\"><\/li><li> Start daarna de lus met het trefwoord &#8216;Voor elk&#8217; en verwijs naar elk werkblad in de werkmap. <br><img loading=\"lazy\" decoding=\"async\" title=\"2-start-lus-met-trefwoord\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/2-demarrer-la-boucle-avec-le-mot-cle.png\" sizes=\"auto, \" srcset=\"\" alt=\"start-the-loop-with-the-keyword\" width=\"461\" height=\"277\"><\/li><li> Stel nu dat u een waarde wilt invoeren in cel A1 van elk werkblad, dan kunt u de schrijfcode als volgt gebruiken. <br><img loading=\"lazy\" decoding=\"async\" title=\"3-wil-een-waarde-in-de-cel-invoeren\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/3-veulent-entrer-une-valeur-dans-la-cellule.png\" sizes=\"auto, \" srcset=\"\" alt=\"want-to-enter-a-value-in-the-cell\" width=\"459\" height=\"301\"><\/li><li> Gebruik aan het einde het trefwoord \u201cEnd\u201d om de lus te be\u00ebindigen. <br><img loading=\"lazy\" decoding=\"async\" title=\"4-gebruik-het-einde-van-het-trefwoord\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/4-utilisez-le-mot-cle-fin.png\" sizes=\"auto, \" srcset=\"\" alt=\"use-the-keyword-end\" width=\"457\" height=\"307\"><\/li><\/ol><p class=\"content-box-grey\"> <em>Handige links: <a href=\"https:\/\/exceladvisor.org\/nl\/voer-een-macro-uit-2\/\">Een macro uitvoeren<\/a> \u2013 <a href=\"https:\/\/exceladvisor.org\/nl\/een-macro-opnemen\/\">Macrorecorder<\/a> \u2013 <a href=\"https:\/\/exceladvisor.org\/nl\/eenvoudige-visuele-editor\/\">Visual Basic Editor<\/a> \u2013 <a href=\"https:\/\/exceladvisor.org\/nl\/persoonlijk-macronotitieboekje\/\">Handboek voor persoonlijke macro&#8217;s<\/a><\/em><\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Sub vba_loop_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Range(\"A1\").Value = \"Yes\" Next ws End Sub<\/code><\/pre><p> Deze code doorloopt elk blad en voert de waarde in cel A1 van elk blad in. Het voordeel van het gebruik van deze methode is dat alle bladen in de werkmap worden doorlopen.<\/p><p> En als u door alle werkbladen in een gesloten werkmap wilt bladeren, gebruikt u de onderstaande code.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Sub vba_loop_sheets() Dim wb As Workbook Dim ws As Worksheet Application.DisplayAlerts = False Set wb = Workbooks.Open(\"C:UsersDellDesktopsample-file.xlsx\") For Each ws In wb.Worksheets ws.Range(\"A1\").Value = \"Done\" Next ws wb.Close SaveChanges:=True Application.DisplayAlerts = True End Sub<\/code><\/pre><h2 class=\"wp-block-heading\"> 2. Gebruik de For Next-lus<\/h2><p> U kunt ook de <a href=\"https:\/\/exceladvisor.org\/nl\/voor-de-lus\/\">FOR NEXT<\/a> -lus gebruiken, waarbij u het aantal bladen kunt gebruiken om te beslissen hoeveel lussen u wilt maken en de lusteller kunt gebruiken om naar elk blad te verwijzen.<\/p><p> Hier zijn de stappen die u moet volgen:<\/p><ol><li> Eerst moet u twee variabelen declareren om de telwaarde voor de lus op te slaan, en \u00e9\u00e9n voor het aantal bladen dat u in de werkmap heeft. <br><img loading=\"lazy\" decoding=\"async\" title=\"5-declare-to-variable-to-write-count-value\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/5-declarer-a-la-variable-pour-ecrire-la-valeur-de-comptage.png\" sizes=\"auto, \" srcset=\"\" alt=\"declare-to-variable-to-write-count-value\" width=\"410\" height=\"233\"><\/li><li> Stel nu de waarde van de variabele \u201cshtCount\u201d in die overeenkomt met het aantal bladen dat u in de werkmap heeft. <br><img loading=\"lazy\" decoding=\"async\" title=\"6-stel-de-shtcount-waarde in\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/6-definir-la-valeur-pour-le-shtcount.png\" sizes=\"auto, \" srcset=\"\" alt=\"set-the-value-for-the-shtcount\" width=\"438\" height=\"323\"><\/li><li> Start daarna de code voor de lus met behulp van het trefwoord &#8220;For i&#8221; en gebruik het aantal bladeren van de maximale waarde voor de lusteller. <br><img loading=\"lazy\" decoding=\"async\" title=\"7-start-code\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/7-demarrer-le-code.png\" sizes=\"auto, \" srcset=\"\" alt=\"start-the-code\" width=\"439\" height=\"380\"><\/li><li> Vanaf hier moet u de lusteller gebruiken om alle bladen te doorlopen en de waarde &#8220;Ja&#8221; in cel A1 van elk blad in te voeren. <br><img loading=\"lazy\" decoding=\"async\" title=\"8-gebruik-de-lus-tegen-lus\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/8-utilisez-le-compteur-de-boucle-pour-boucler.png\" sizes=\"auto, \" srcset=\"\" alt=\"use-the-loop-counter-to-loop\" width=\"430\" height=\"349\"><\/li><\/ol><p> <strong>Volledige code<\/strong><\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Sub vba_loop_sheets() Dim i As Long Dim shtCount As Long shtCount = Sheets.Count For i = 1 To shtCount Sheets(i).Range(\"A1\").Value = \"Yes\" Next i End Sub<\/code><\/pre><p> En als u door een gesloten werkmap wilt bladeren, gebruikt u de volgende code.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\">Sub vba_loop_sheets() Dim i As Long Dim shtCount As Long Set wb = Workbooks.Open(\"C:UsersDellDesktopsample-file.xlsx\") shtCount = wb.Sheets.Count Application.DisplayAlerts = False For i = 1 To shtCount wb.Sheets(i).Range(\"A1\").Value = \"Yes\" Next i wb.Close SaveChanges:=True Application.DisplayAlerts = True End Sub<\/code><\/pre><div class=\"gb-container gb-container-4db6181c gb-container-mt\"> <a class=\"gb-button gb-button-c5897111 gb-button-text gb-button-mt-button\" href=\"https:\/\/exceladvisor.org\/nl\/vba\/\">Wat is VBA<\/a><h2 class=\"gb-headline gb-headline-665e5f6b gb-headline-text gb-headline-mt-heading\"> Gerelateerde tutorials <\/h2><div class=\"gb-grid-wrapper gb-grid-wrapper-a20baf7c gb-grid-wrapper-mt-grid\"><div class=\"gb-grid-column gb-grid-column-65ddf67e\"><div class=\"gb-container gb-container-65ddf67e\"><ul><li> <a href=\"https:\/\/exceladvisor.org\/nl\/lussen\/\">VBA-loops (beginner tot gevorderd)<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/selecteer-de-zaak\/\">VBA Selecteer geval<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/even-doen\/\">Excel VBA Do While Loop en (Do Loop While) &#8211; Een gids<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/\">Loop over een bereik met behulp van VBA (Kolommen, Rij en UsedRange)<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/voor-de-lus\/\">VBA FOR LOOP (voor volgende, voor elke)<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/ga-naar-1\/\">GoTo VBA-declaratie<\/a> <\/li><\/ul><\/div><\/div><div class=\"gb-grid-column gb-grid-column-50067b57\"><div class=\"gb-container gb-container-50067b57\"><\/div><\/div><\/div><\/div>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"ub_ctt_via":"","footnotes":""},"class_list":["post-1540","page","type-page","status-publish","hentry"],"featured_image_src":null,"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Hoe door alle bladen heen bladeren met VBA in Excel?<\/title>\n<meta name=\"description\" content=\"Om alle werkbladen in Excel te doorlopen met VBA (Visual Basic for Applications), kunt u een lusstructuur gebruiken\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/\" \/>\n<meta property=\"og:locale\" content=\"nl_NL\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Hoe door alle bladen heen bladeren met VBA in Excel?\" \/>\n<meta property=\"og:description\" content=\"Om alle werkbladen in Excel te doorlopen met VBA (Visual Basic for Applications), kunt u een lusstructuur gebruiken\" \/>\n<meta property=\"og:url\" content=\"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/\" \/>\n<meta property=\"og:site_name\" content=\"Excel Advisor\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-06T05:57:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-declarer-une-variable-a-referencer.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Geschatte leestijd\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minuten\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/\",\"url\":\"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/\",\"name\":\"Hoe door alle bladen heen bladeren met VBA in Excel?\",\"isPartOf\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-declarer-une-variable-a-referencer.png\",\"datePublished\":\"2023-11-17T12:48:02+00:00\",\"dateModified\":\"2023-12-06T05:57:36+00:00\",\"description\":\"Om alle werkbladen in Excel te doorlopen met VBA (Visual Basic for Applications), kunt u een lusstructuur gebruiken\",\"breadcrumb\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/#breadcrumb\"},\"inLanguage\":\"nl-NL\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"nl-NL\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/#primaryimage\",\"url\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-declarer-une-variable-a-referencer.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-declarer-une-variable-a-referencer.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Thuis\",\"item\":\"https:\/\/exceladvisor.org\/nl\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Hoe door alle bladen heen bladeren met vba in excel?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/#website\",\"url\":\"https:\/\/exceladvisor.org\/nl\/\",\"name\":\"Excel Advisor\",\"description\":\"Uw gids voor datadominantie\",\"publisher\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/exceladvisor.org\/nl\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"nl-NL\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/#organization\",\"name\":\"Excel Advisor\",\"url\":\"https:\/\/exceladvisor.org\/nl\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"nl-NL\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"width\":105,\"height\":36,\"caption\":\"Excel Advisor\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Hoe door alle bladen heen bladeren met VBA in Excel?","description":"Om alle werkbladen in Excel te doorlopen met VBA (Visual Basic for Applications), kunt u een lusstructuur gebruiken","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/","og_locale":"nl_NL","og_type":"article","og_title":"Hoe door alle bladen heen bladeren met VBA in Excel?","og_description":"Om alle werkbladen in Excel te doorlopen met VBA (Visual Basic for Applications), kunt u een lusstructuur gebruiken","og_url":"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/","og_site_name":"Excel Advisor","article_modified_time":"2023-12-06T05:57:36+00:00","og_image":[{"url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-declarer-une-variable-a-referencer.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Geschatte leestijd":"3 minuten"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/","url":"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/","name":"Hoe door alle bladen heen bladeren met VBA in Excel?","isPartOf":{"@id":"https:\/\/exceladvisor.org\/nl\/#website"},"primaryImageOfPage":{"@id":"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/#primaryimage"},"image":{"@id":"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/#primaryimage"},"thumbnailUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-declarer-une-variable-a-referencer.png","datePublished":"2023-11-17T12:48:02+00:00","dateModified":"2023-12-06T05:57:36+00:00","description":"Om alle werkbladen in Excel te doorlopen met VBA (Visual Basic for Applications), kunt u een lusstructuur gebruiken","breadcrumb":{"@id":"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/#breadcrumb"},"inLanguage":"nl-NL","potentialAction":[{"@type":"ReadAction","target":["https:\/\/exceladvisor.org\/nl\/lus-bladeren\/"]}]},{"@type":"ImageObject","inLanguage":"nl-NL","@id":"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/#primaryimage","url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-declarer-une-variable-a-referencer.png","contentUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-declarer-une-variable-a-referencer.png"},{"@type":"BreadcrumbList","@id":"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Thuis","item":"https:\/\/exceladvisor.org\/nl\/"},{"@type":"ListItem","position":2,"name":"Hoe door alle bladen heen bladeren met vba in excel?"}]},{"@type":"WebSite","@id":"https:\/\/exceladvisor.org\/nl\/#website","url":"https:\/\/exceladvisor.org\/nl\/","name":"Excel Advisor","description":"Uw gids voor datadominantie","publisher":{"@id":"https:\/\/exceladvisor.org\/nl\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/exceladvisor.org\/nl\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"nl-NL"},{"@type":"Organization","@id":"https:\/\/exceladvisor.org\/nl\/#organization","name":"Excel Advisor","url":"https:\/\/exceladvisor.org\/nl\/","logo":{"@type":"ImageObject","inLanguage":"nl-NL","@id":"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/","url":"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","contentUrl":"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","width":105,"height":36,"caption":"Excel Advisor"},"image":{"@id":"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/"}}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages\/1540","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/comments?post=1540"}],"version-history":[{"count":1,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages\/1540\/revisions"}],"predecessor-version":[{"id":2391,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages\/1540\/revisions\/2391"}],"wp:attachment":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/media?parent=1540"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}