{"id":11498,"date":"2022-09-18T23:10:53","date_gmt":"2022-09-18T14:10:53","guid":{"rendered":"https:\/\/prodskill.com\/?p=11498"},"modified":"2022-10-04T19:00:29","modified_gmt":"2022-10-04T10:00:29","slug":"excel-vba-coding-pattern-range-loop-read","status":"publish","type":"post","link":"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/","title":{"rendered":"VBA Coding Pattern: Range Loop - Read"},"content":{"rendered":"<p>Among the VBA coding patterns, look at the contents of Range Loop-Read.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">&lt;&lt;Table of Contents&gt;&gt;<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/#%EC%9A%94%EC%95%BD\" >summary<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/#Test_data_Test_VBA_Code\" >Test data, Test VBA Code<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/#Range_Loop-%EC%9D%BD%EA%B8%B0Read_%EA%B8%B0%EB%B3%B8_%ED%8C%A8%ED%84%B4-Nested_Loop\" >Range Loop-Read Basic Pattern-Nested Loop<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/#%EB%8B%A8%EC%9D%BC_Loop%EB%A5%BC_%EC%82%AC%EC%9A%A9%ED%95%98%EB%8A%94_%ED%8C%A8%ED%84%B4-Single_Loop1_Text_%EC%82%AC%EC%9A%A9\" >Pattern using a single loop - use single loop(1), text<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/#%EB%8B%A8%EC%9D%BC_Loop%EB%A5%BC_%EC%82%AC%EC%9A%A9%ED%95%98%EB%8A%94_%ED%8C%A8%ED%84%B4-Single_Loop2_Text_%EB%8C%80%EC%8B%A0_Value2_%EC%82%AC%EC%9A%A9\" >Pattern using Single Loop-Single Loop(2), Use Value2 instead of Text<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/#%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%A5%BC_%ED%95%9C%EB%B2%88%EC%97%90_memory%EB%A1%9C_%EC%9D%BD%EB%8A%94_%EB%B0%A9%EB%B2%95-Variant_Array_%EC%82%AC%EC%9A%A9\" >How to read data into memory at once - Using Variant Array<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/#%EC%84%B1%EB%8A%A5%EB%B9%84%EA%B5%90\" >Performance Comparison<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"\uc694\uc57d\"><span class=\"ez-toc-section\" id=\"%EC%9A%94%EC%95%BD\"><\/span>summary<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Range Loop is a coding pattern often used when reading and processing data entered in an Excel sheet with VBA code. The basic pattern of sequentially reading from the start cell to the last cell and the performance improvement pattern of reading at once instead of sequentially are introduced below, and their performances are compared.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Test data set\"><span class=\"ez-toc-section\" id=\"Test_data_Test_VBA_Code\"><\/span>Test data, Test VBA Code<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>For explanation, the example data is created with 100,000 rows, 10 columns, and a total of 1 million cells, and the value of each cell is set as the coordinate value of (row, column).<\/p>\n\n\n\n<p>In the github file link below, you can check the example data, \u201cB2\u201d cell as the starting point, and the coding pattern and execution result of exploring the range from 10 rows (100 cells) to 100,000 rows (1 million cells).<\/p>\n\n\n\n<p>File Link: github\u00a0<a href=\"https:\/\/github.com\/DAToolset\/VBACode\/raw\/main\/VBA%20%EC%BD%94%EB%94%A9%20%ED%8C%A8%ED%84%B4-Range%20Loop(Read).xlsm\" target=\"_blank\" rel=\"noreferrer noopener\">DAToolset\/VBACode\/VBA Coding Patterns-Range Loop(Read).xlsm<\/a><\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-133.png\"><img loading=\"lazy\" decoding=\"async\" width=\"653\" height=\"324\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-133.png\" alt=\"\ud45c1. Test Data\" class=\"wp-image-11499\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-133.png 653w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-133-300x149.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-133-18x9.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-133-24x12.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-133-36x18.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-133-48x24.png 48w\" sizes=\"auto, (max-width: 653px) 100vw, 653px\" \/><\/a><figcaption>Table 1. Test Data<\/figcaption><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"\uae30\ubcf8 \ud328\ud134: Nested Loop\"><span class=\"ez-toc-section\" id=\"Range_Loop-%EC%9D%BD%EA%B8%B0Read_%EA%B8%B0%EB%B3%B8_%ED%8C%A8%ED%84%B4-Nested_Loop\"><\/span>Range Loop-Read Basic Pattern-Nested Loop<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The basic pattern increases the offset of the row and column based on the starting cell, and uses the loop statement for the column inside the loop statement for the row. The detailed code is as follows.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"11\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">'\uae30\ubcf8 \ud328\ud134\nPublic Sub NestedLoop(aTargetRowCnt As Long)\n    Dim lRowOffset As Long, lColOffset As Long, oBaseRange As Range, sVal As String\n    Set oBaseRange = Range(\"B2\")\n    lRowOffset = 0\n    For lRowOffset = 0 To aTargetRowCnt '\uc77d\ub294 \ud589\uc758 \uc218(\ucda9\ubd84\ud788 \ud070 \uc218\ub85c \uc9c0\uc815\ud558\uace0 Loop \ub0b4\ubd80\uc5d0\uc11c \uc885\ub8cc \uc870\uac74 \uc124\uc815)\n        If Trim(oBaseRange.Offset(lRowOffset, 0).Value) = \"\" Then Exit For 'Loop \uc885\ub8cc \uc870\uac74\n        For lColOffset = 0 To 9 '\uc77d\ub294 \uc5f4\uc758 \uc218\n            'cell \ub2e8\uc704\ub85c \uc77d\uace0 \ucc98\ub9ac\ud558\ub294 \ucf54\ub4dc \uc608\uc2dc\n            'Debug.Print oBaseRange.Offset(lRowOffset, lColOffset).Value\n            sVal = oBaseRange.Offset(lRowOffset, lColOffset).Text\n        Next lColOffset\n    Next lRowOffset\nEnd Sub<\/pre>\n\n\n\n<p>The following code of the inner For Loop is written as an example of the simplest code to read a cell value.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"11\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">            sVal = oBaseRange.Offset(lRowOffset, lColOffset).Text<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\ub2e8\uc77c Loop\ub97c \uc0ac\uc6a9\ud558\ub294 \ud328\ud134: Single Loop(1), Text \uc0ac\uc6a9\"><span class=\"ez-toc-section\" id=\"%EB%8B%A8%EC%9D%BC_Loop%EB%A5%BC_%EC%82%AC%EC%9A%A9%ED%95%98%EB%8A%94_%ED%8C%A8%ED%84%B4-Single_Loop1_Text_%EC%82%AC%EC%9A%A9\"><\/span>Pattern using a single loop - use single loop(1), text<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>This is a syntax that replaces the internal loop statement for columns with statements as many as the number of columns. The detailed code is as follows. Text was used to retrieve the value of Range.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">'\ub2e8\uc77c Loop\ub97c \uc0ac\uc6a9\ud558\ub294 \ud328\ud134: Single Loop(1), Text  \uc0ac\uc6a9\nPublic Sub SingleLoop1(aTargetRowCnt As Long)\n    Dim lRowOffset As Long, lColOffset As Long, oBaseRange As Range, sVal As String\n    Set oBaseRange = Range(\"B2\")\n    lRowOffset = 0\n    Dim lRowCount As Long, lColCount As Long, oHeaderRange As Range\n    Set oHeaderRange = oBaseRange.Offset(-1, 0)\n    'lRowCount = Range(oBaseRange, oBaseRange.End(xlDown)).Rows.Count\n    lRowCount = aTargetRowCnt\n    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count\n    For lRowOffset = 0 To lRowCount - 1 '\uc77d\ub294 \ud589\uc758 \uc218 (Offset\uc740 0\ubd80\ud130 \uc2dc\uc791\ud558\ubbc0\ub85c Count -1 \uae4c\uc9c0 Loop \uc2e4\ud589\ud574\uc57c \ud568)\n        sVal = oBaseRange.Offset(lRowOffset, 0).Text\n        sVal = oBaseRange.Offset(lRowOffset, 1).Text\n        sVal = oBaseRange.Offset(lRowOffset, 2).Text\n        sVal = oBaseRange.Offset(lRowOffset, 3).Text\n        sVal = oBaseRange.Offset(lRowOffset, 4).Text\n        sVal = oBaseRange.Offset(lRowOffset, 5).Text\n        sVal = oBaseRange.Offset(lRowOffset, 6).Text\n        sVal = oBaseRange.Offset(lRowOffset, 7).Text\n        sVal = oBaseRange.Offset(lRowOffset, 8).Text\n        sVal = oBaseRange.Offset(lRowOffset, 9).Text\n    Next lRowOffset\nEnd Sub<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\ub2e8\uc77c Loop\ub97c \uc0ac\uc6a9\ud558\ub294 \ud328\ud134: Single Loop(2), Text \ub300\uc2e0 Value2 \uc0ac\uc6a9\"><span class=\"ez-toc-section\" id=\"%EB%8B%A8%EC%9D%BC_Loop%EB%A5%BC_%EC%82%AC%EC%9A%A9%ED%95%98%EB%8A%94_%ED%8C%A8%ED%84%B4-Single_Loop2_Text_%EB%8C%80%EC%8B%A0_Value2_%EC%82%AC%EC%9A%A9\"><\/span>Pattern using Single Loop-Single Loop(2), Use Value2 instead of Text<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>When maintaining the Single Loop syntax and pulling out the value of Range, Value2 was used instead of Text. The performance comparison is written at the bottom.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">' \ud589\ub9cc Loop, Text \ub300\uc2e0 Value2 \uc0ac\uc6a9\nPublic Sub SingleLoop(aTargetRowCnt As Long)\n    Dim lRowOffset As Long, lColOffset As Long, oBaseRange As Range, sVal As String\n    Set oBaseRange = Range(\"B2\")\n    lRowOffset = 0\n    Dim lRowCount As Long, lColCount As Long, oHeaderRange As Range\n    Set oHeaderRange = oBaseRange.Offset(-1, 0)\n    'lRowCount = Range(oBaseRange, oBaseRange.End(xlDown)).Rows.Count\n    lRowCount = aTargetRowCnt\n    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count\n    For lRowOffset = 0 To lRowCount - 1 '\uc77d\ub294 \ud589\uc758 \uc218 (Offset\uc740 0\ubd80\ud130 \uc2dc\uc791\ud558\ubbc0\ub85c Count -1 \uae4c\uc9c0 Loop \uc2e4\ud589\ud574\uc57c \ud568)\n        sVal = oBaseRange.Offset(lRowOffset, 0).Value2\n        sVal = oBaseRange.Offset(lRowOffset, 1).Value2\n        sVal = oBaseRange.Offset(lRowOffset, 2).Value2\n        sVal = oBaseRange.Offset(lRowOffset, 3).Value2\n        sVal = oBaseRange.Offset(lRowOffset, 4).Value2\n        sVal = oBaseRange.Offset(lRowOffset, 5).Value2\n        sVal = oBaseRange.Offset(lRowOffset, 6).Value2\n        sVal = oBaseRange.Offset(lRowOffset, 7).Value2\n        sVal = oBaseRange.Offset(lRowOffset, 8).Value2\n        sVal = oBaseRange.Offset(lRowOffset, 9).Value2\n    Next lRowOffset\nEnd Sub<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\ub370\uc774\ud130\ub97c \ud55c\ubc88\uc5d0 memory\ub85c \uc77d\ub294 \ubc29\ubc95: Variant Array \uc0ac\uc6a9\"><span class=\"ez-toc-section\" id=\"%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%A5%BC_%ED%95%9C%EB%B2%88%EC%97%90_memory%EB%A1%9C_%EC%9D%BD%EB%8A%94_%EB%B0%A9%EB%B2%95-Variant_Array_%EC%82%AC%EC%9A%A9\"><\/span>How to read data into memory at once - Using Variant Array<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>This is a method of setting the range to read data and reading it at once as a 2D Variant Array. The detailed code is as follows.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"7\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">' \ud55c\ubc88\uc5d0 \uc77d\ub294 \ud328\ud134\nPublic Sub VariantArray(aTargetRowCnt As Long)\n    Dim vRngArr As Variant, oBaseRange As Range, oHeaderRange As Range, lColCount As Long, sVal As String\n    Set oBaseRange = Range(\"B2\")\n    Set oHeaderRange = oBaseRange.Offset(-1, 0)\n    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count\n    vRngArr = oBaseRange.Resize(aTargetRowCnt, lColCount).Value2\n    Dim lCol As Long, lRow As Long\n    For lRow = LBound(vRngArr, 1) To UBound(vRngArr, 1)\n        For lCol = LBound(vRngArr, 2) To UBound(vRngArr, 2)\n            'Debug.Print vRngArr(lRow, lCol)\n            sVal = vRngArr(lRow, lCol)\n        Next lCol\n    Next lRow\nEnd Sub<\/pre>\n\n\n\n<p>The key here is the following code.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"7\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">    vRngArr = oBaseRange.Resize(aTargetRowCnt, lColCount).Value2<\/pre>\n\n\n\n<p>Resize expands the range from the starting range (Range) to the specified number of rows and columns to set the target to read values from. Value2 is a property that retrieves the value of Range as its original value without formatting. If the Range to retrieve a value includes several rows and columns, the value is retrieved as a 2-dimensional Variant Array. So, the code above reads the entire range to be read into memory at once.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"\uc131\ub2a5\ube44\uad50\"><span class=\"ez-toc-section\" id=\"%EC%84%B1%EB%8A%A5%EB%B9%84%EA%B5%90\"><\/span>Performance Comparison<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The execution time of each method is summarized as follows while increasing the row count from 10 to 100,000.<br>(Execution environment: 2019 LG Gram, CPU i5, Ram 16GB, 256GB SSD)<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-134.png\"><img loading=\"lazy\" decoding=\"async\" width=\"507\" height=\"307\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-134.png\" alt=\"\ud45c2. \uc785\ub825 Count\uc640 \uc18c\uc694\uc2dc\uac04(\ucd08)\" class=\"wp-image-11500\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-134.png 507w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-134-300x182.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-134-18x12.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-134-24x15.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-134-36x22.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-134-48x29.png 48w\" sizes=\"auto, (max-width: 507px) 100vw, 507px\" \/><\/a><figcaption>Table 2. Input Count and time taken (seconds)<\/figcaption><\/figure>\n<\/div>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135.png\"><img loading=\"lazy\" decoding=\"async\" width=\"655\" height=\"428\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135.png\" alt=\"\uadf8\ub9bc1. \uc785\ub825 Count\uc640 \uc18c\uc694\uc2dc\uac04(\ucd08)\" class=\"wp-image-11501\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135.png 655w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135-300x196.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135-18x12.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135-24x16.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135-36x24.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135-48x31.png 48w\" sizes=\"auto, (max-width: 655px) 100vw, 655px\" \/><\/a><figcaption>Figure 1. Input Count and time taken (seconds)<\/figcaption><\/figure>\n<\/div>\n\n\n<p>Execution performance can be summarized as follows.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>There is almost no difference in the execution performance of Nested Loop and Single Loop repetition statements themselves.<\/li><li>When extracting a value from Range, using Text is slow, and using Value2 is fast.<\/li><li>Using Variant Array, even if there is a lot of data, it can be read very quickly.<\/li><\/ul>\n\n\n\n<p>For reference, the comparison of the properties of Text, Value, and Value2, which are used to retrieve values from the Range, will be organized later when the time is right. First of all, I link the article on stackoverflow.com that I found by searching on Google.<br><a href=\"https:\/\/stackoverflow.com\/questions\/17359835\/what-is-the-difference-between-text-value-and-value2\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/stackoverflow.com\/questions\/17359835\/what-is-the-difference-between-text-value-and-value2<\/a><\/p>\n\n\n\n<p><\/p>","protected":false},"excerpt":{"rendered":"<p>Among the VBA coding patterns, look at the contents of Range Loop-Read. Summary Range Loop is a coding pattern often used when reading and processing data entered in an Excel sheet with VBA code. The basic pattern of reading sequentially from the start cell to the last cell, and reading at once without reading sequentially...<\/p>","protected":false},"author":1,"featured_media":11501,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[17,73,74,75,76,77,78],"class_list":["post-11498","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-vba-coding-pattern","tag-vba","tag-excel-vba","tag-74","tag-codding-pattern","tag-range-loop","tag-77","tag-read"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>VBA \ucf54\ub529 \ud328\ud134: Range Loop-\uc77d\uae30(Read) - \uc0dd\uc0b0\uc131 Skill<\/title>\n<meta name=\"description\" content=\"VBA \ucf54\ub529 \ud328\ud134 \uc911 Range Loop-\uc77d\uae30(Read) \uc5d0 \ub300\ud55c \ub0b4\uc6a9\uc744 \uc0b4\ud3b4\ubcf8\ub2e4. \uae30\ubcf8 \ud328\ud134\uc778 Nested Loop, \ub2e8\uc77c Loop, Variant Array\ub97c \uc0ac\uc6a9\ud558\ub294 \uac01\uac01\uc758 \ubc29\ubc95\uc5d0 \ub300\ud574 \uc131\ub2a5\uc744 \ube44\uad50\ud55c\ub2e4.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VBA \ucf54\ub529 \ud328\ud134: Range Loop-\uc77d\uae30(Read) - \uc0dd\uc0b0\uc131 Skill\" \/>\n<meta property=\"og:description\" content=\"VBA \ucf54\ub529 \ud328\ud134 \uc911 Range Loop-\uc77d\uae30(Read) \uc5d0 \ub300\ud55c \ub0b4\uc6a9\uc744 \uc0b4\ud3b4\ubcf8\ub2e4. \uae30\ubcf8 \ud328\ud134\uc778 Nested Loop, \ub2e8\uc77c Loop, Variant Array\ub97c \uc0ac\uc6a9\ud558\ub294 \uac01\uac01\uc758 \ubc29\ubc95\uc5d0 \ub300\ud574 \uc131\ub2a5\uc744 \ube44\uad50\ud55c\ub2e4.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/\" \/>\n<meta property=\"og:site_name\" content=\"\uc0dd\uc0b0\uc131 Skill\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-18T14:10:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-10-04T10:00:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135.png\" \/>\n\t<meta property=\"og:image:width\" content=\"655\" \/>\n\t<meta property=\"og:image:height\" content=\"428\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Zerom\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Zerom\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/\"},\"author\":{\"name\":\"Zerom\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\"},\"headline\":\"VBA \ucf54\ub529 \ud328\ud134: Range Loop-\uc77d\uae30(Read)\",\"datePublished\":\"2022-09-18T14:10:53+00:00\",\"dateModified\":\"2022-10-04T10:00:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/\"},\"wordCount\":113,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\"},\"image\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/image-135.png\",\"keywords\":[\"\uc5d1\uc140VBA\",\"Excel VBA\",\"\ucf54\ub529\ud328\ud134\",\"Codding Pattern\",\"Range Loop\",\"\uc77d\uae30\",\"Read\"],\"articleSection\":[\"\uc5d1\uc140 VBA \ucf54\ub529\ud328\ud134\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/\",\"url\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/\",\"name\":\"VBA \ucf54\ub529 \ud328\ud134: Range Loop-\uc77d\uae30(Read) - \uc0dd\uc0b0\uc131 Skill\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/image-135.png\",\"datePublished\":\"2022-09-18T14:10:53+00:00\",\"dateModified\":\"2022-10-04T10:00:29+00:00\",\"description\":\"VBA \ucf54\ub529 \ud328\ud134 \uc911 Range Loop-\uc77d\uae30(Read) \uc5d0 \ub300\ud55c \ub0b4\uc6a9\uc744 \uc0b4\ud3b4\ubcf8\ub2e4. \uae30\ubcf8 \ud328\ud134\uc778 Nested Loop, \ub2e8\uc77c Loop, Variant Array\ub97c \uc0ac\uc6a9\ud558\ub294 \uac01\uac01\uc758 \ubc29\ubc95\uc5d0 \ub300\ud574 \uc131\ub2a5\uc744 \ube44\uad50\ud55c\ub2e4.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/#primaryimage\",\"url\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/image-135.png\",\"contentUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/image-135.png\",\"width\":655,\"height\":428},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-range-loop-read\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"VBA \ucf54\ub529 \ud328\ud134: Range Loop-\uc77d\uae30(Read)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#website\",\"url\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/\",\"name\":\"\uc0dd\uc0b0\uc131 Skill\",\"description\":\"Meta Thinking, Meta Working\",\"publisher\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\",\"name\":\"Zerom\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\",\"url\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\",\"contentUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\",\"width\":512,\"height\":512,\"caption\":\"Zerom\"},\"logo\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\"},\"url\":\"https:\\\/\\\/prodskill.com\\\/en\\\/author\\\/proda\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"VBA Coding Pattern: Range Loop - Read - Productivity Skill","description":"This article examines the Range Loop-Read method, a VBA coding pattern. It compares the performance of each method, including the basic Nested Loop, Single Loop, and Variant Array.","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:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/","og_locale":"en_US","og_type":"article","og_title":"VBA \ucf54\ub529 \ud328\ud134: Range Loop-\uc77d\uae30(Read) - \uc0dd\uc0b0\uc131 Skill","og_description":"VBA \ucf54\ub529 \ud328\ud134 \uc911 Range Loop-\uc77d\uae30(Read) \uc5d0 \ub300\ud55c \ub0b4\uc6a9\uc744 \uc0b4\ud3b4\ubcf8\ub2e4. \uae30\ubcf8 \ud328\ud134\uc778 Nested Loop, \ub2e8\uc77c Loop, Variant Array\ub97c \uc0ac\uc6a9\ud558\ub294 \uac01\uac01\uc758 \ubc29\ubc95\uc5d0 \ub300\ud574 \uc131\ub2a5\uc744 \ube44\uad50\ud55c\ub2e4.","og_url":"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-range-loop-read\/","og_site_name":"\uc0dd\uc0b0\uc131 Skill","article_published_time":"2022-09-18T14:10:53+00:00","article_modified_time":"2022-10-04T10:00:29+00:00","og_image":[{"width":655,"height":428,"url":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135.png","type":"image\/png"}],"author":"Zerom","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Zerom","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/#article","isPartOf":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/"},"author":{"name":"Zerom","@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd"},"headline":"VBA \ucf54\ub529 \ud328\ud134: Range Loop-\uc77d\uae30(Read)","datePublished":"2022-09-18T14:10:53+00:00","dateModified":"2022-10-04T10:00:29+00:00","mainEntityOfPage":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/"},"wordCount":113,"commentCount":0,"publisher":{"@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd"},"image":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/#primaryimage"},"thumbnailUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135.png","keywords":["\uc5d1\uc140VBA","Excel VBA","\ucf54\ub529\ud328\ud134","Codding Pattern","Range Loop","\uc77d\uae30","Read"],"articleSection":["\uc5d1\uc140 VBA \ucf54\ub529\ud328\ud134"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/","url":"https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/","name":"VBA Coding Pattern: Range Loop - Read - Productivity Skill","isPartOf":{"@id":"https:\/\/prodskill.com\/ko\/#website"},"primaryImageOfPage":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/#primaryimage"},"image":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/#primaryimage"},"thumbnailUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135.png","datePublished":"2022-09-18T14:10:53+00:00","dateModified":"2022-10-04T10:00:29+00:00","description":"This article examines the Range Loop-Read method, a VBA coding pattern. It compares the performance of each method, including the basic Nested Loop, Single Loop, and Variant Array.","breadcrumb":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/#primaryimage","url":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135.png","contentUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-135.png","width":655,"height":428},{"@type":"BreadcrumbList","@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-range-loop-read\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/prodskill.com\/ko\/"},{"@type":"ListItem","position":2,"name":"VBA \ucf54\ub529 \ud328\ud134: Range Loop-\uc77d\uae30(Read)"}]},{"@type":"WebSite","@id":"https:\/\/prodskill.com\/ko\/#website","url":"https:\/\/prodskill.com\/ko\/","name":"Productivity Skills","description":"Meta Thinking, Meta Working","publisher":{"@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/prodskill.com\/ko\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd","name":"Zerom","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png","url":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png","contentUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png","width":512,"height":512,"caption":"Zerom"},"logo":{"@id":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png"},"url":"https:\/\/prodskill.com\/en\/author\/proda\/"}]}},"_links":{"self":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/posts\/11498","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/comments?post=11498"}],"version-history":[{"count":0,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/posts\/11498\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/media\/11501"}],"wp:attachment":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/media?parent=11498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/categories?post=11498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/tags?post=11498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}