title: Custom Function to Extract Data from JSON API Based on Column Values in Excel VBA
body: <p>I've an excel workbook that looks something like this:</p>
  
  <pre><code>/-------------------------------------\
  |   Lat    |   Long   |     Area      |
  |-------------------------------------|
  | 5.3      | 103.8    |   AREA_NAME   |
  \-------------------------------------/
  </code></pre>
  
  <p>I also have a JSON api with a url of the following structure:</p>
  
  <pre><code>https://example.com/api?token=TOKEN&amp;lat=X.X&amp;lng=X.X
  </code></pre>
  
  <p>that returns a JSON object with the following structure:</p>
  
  <pre><code>{ "Area": "AREA_NAME", "OTHERS": "Other_details"}
  </code></pre>
  
  <p>I tried to implement a VBA function that will help me to extract AREA_NAME. However, I keep getting syntax errors. I don't know where I am going wrong.</p>
  
  <pre><code>Function get_p()
      Source = Json.Document (Web.Contents("https://example.com/api?token=TOKEN&amp;lat=5.3&amp;lng=103.8"))
      name = Source[Area]
      get_p = Name
  End Function
  </code></pre>
  
  <p>I intentionally hardcoded the lat and long value for development purposes. Eventually, I want the function to accept lat and long as parameters. I got the first line of the function from PowerQuery Editor.</p>
  
  <p>Where am I going wrong? How to do this properly in VBA? Or is there a simpler way using PowerQuery? </p>