title: Scraping data from a website with a dynamic array function - Excel VBA
body: <p>I want to eventually create a function where I can specify a web page element and URL and populate all instances of that element down a column. But am currently only experiencing limited success with this function:</p>
  
  <pre><code>Sub GrabAnchorTags() '(URL As String) As Variant'
  
      Dim objIE As InternetExplorer
      Dim elem As Object
  
      Set objIE = New InternetExplorer
      objIE.Visible = False
  
      objIE.navigate "http://example.com/"
      Do While objIE.Busy = True Or objIE.readyState &lt;&gt; 4: DoEvents: Loop
  
      Dim aRange As Range
      Debug.Print objIE.document.getElementsByTagName("a").Length
  
      For Each elem In objIE.document.getElementsByTagName("a")
          Debug.Print elem
          ActiveCell.Offset(x, y).Value = elem
          ActiveCell.Offset(x, y + 1).Value = elem.textContent
          x = x + 1
  
      Next
  
      objIE.Quit
      Set objIE = Nothing
  End Sub
  </code></pre>
  
  <p>I would like to be able to turn this successfully from a macro to a function. </p>
  
  <ol>
  <li><p>Currently, it uses a for loop to populate the cells and I wonder if it's possible to accomplish the same thing using evaluate or something similar because the for loop is inefficient.</p></li>
  <li><p>This function would need to live in a cell, reference a URL in another cell, and populate the cells bellow it with all elements of a type found on the page. I am currently working on the anchor tag. </p></li>
  </ol>
  
  <p>Many other solutions I referenced used macros:</p>
  
  <p><a href="https://stackoverflow.com/questions/32380445/scraping-data-from-website-using-excel-vba">Scraping data from website using excel vba</a></p>
  
  <p><a href="https://stackoverflow.com/questions/19976326/getting-links-url-from-a-webpage-excel-vba">Getting links url from a webpage excel vba</a></p>
  
  <p><a href="http://automatetheweb.net/vba-getelementsbytagname-method/" rel="nofollow noreferrer">VBA – Web scraping with getElementsByTagName()</a></p>