title: Using named range in VBA function for VLOOKUP
body: <p>I have a the following on my worksheet:</p>
  
  <ul>
  <li>A cell that shows a currency [in <code>A1</code>]</li>
  <li>A range of cells (two columns, one for the currency, and the other for a corresponding commission percentage) [defined as/named <code>RANGE</code>, and scoped to the worksheet]</li>
  <li>A cell that [is trying] to determine the calculated commission percentage based on <code>A1</code> and <code>RANGE</code></li>
  </ul>
  
  <p>I then have a VBA function called <code>Calculate</code>, as follows:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Function Calculate(LookupValue As Double, LookupRange As Range) As Double
      Calculate = [VLOOKUP(LookupValue, LookupRange, 2)]
  End Function
  </code></pre>
  
  <p>The cell that determines the percentage has the following:</p>
  
  <pre><code>=Calculate(A1, RANGE)
  </code></pre>
  
  <p>Problem is that the cell just returns <code>#VALUE!</code>...</p>
  
  <p>Any idea what I could be doing wrong?</p>
  
  <p>I have tried several things like type-hinting to <code>Range()</code>, passing <code>LookupRange.Value2</code> to <code>VLOOKUP</code>, etc, none of which worked.</p>
  
  <p>I have also tried to debug, noting that <code>LookupRange</code> does actually contain the range required in <code>Value2</code>, which is is why I tried to pass it to the function.</p>
  
  <p><strong><em>Side Note:</strong> The function and layout mentioned above is just a dummy - the actual function is somewhat more complex as it relies on negotiated rates, monthly margins, etc. This is why I'm using VBA in the first place. I know that I'm doing something wrong with the lookup, as it is the only thing that seems to be failing within the function - everything else corresponds and calculates.</em></p>