title: using vba in excel to determinine minimum value from array of named ranges
body: <p>I have a spread sheet with multiple dynamic named ranges such as HR_B1, to HR_B10 etc.</p>
  
  <p>I am trying to create a function that will find the minimum value from whichever ranges are inserted into the function, i.e. the user function will input two values into the function say 3 and 6 and it will find the minimum value over ranges HR_B3 to HR_B6.</p>
  
  <p>I have created a array and for loop that stores the names of the named ranges in the array.</p>
  
  <p>However I cannot get the WorksheetFunction.Min code to read the contents of the array as named ranges and output the min value.</p>
  
  <p>My code is:</p>
  
  <pre><code>Public Function HR_Min_Range(minval As Integer, maxval As Integer) As Variant
  
  Dim fullrange() As Variant
  Dim total_birds As Integer
  Dim i As Long
  
  total_birds = (maxval - minval)
  
  ReDim fullrange(total_birds)
  
  For i = 0 To total_birds
  
  fullrange(i) = "HR_B" &amp; (i + minval)
  Next i
  
  HR_Min_Range = WorksheetFunction.Min(Sheets("HR_Depths").Range(fullrange))
  
  End Function
  </code></pre>