title: Excel VBA - function min max of a range
body: <p>I've got 2 very similar functions, that were working before I switched my code to the <strong>Option Explicit</strong> for debugging puposes (success !). Since then, the <code>Max</code> function does not <em>work</em> anymore and I can't elaborate the reason why and solve it as an xl vba perfect noob. </p>
  
  <ul>
  <li><p><strong>The Max function</strong> (does not work):</p>
  
  <pre><code>Function MaxAddress(The_Range) As Variant
  ' See http://support.microsoft.com/kb/139574
  
  Dim MaxNum As Variant
  Dim cell As Range
  
    ' Sets variable equal to maximum value in the input range.
    MaxNum = Application.Max(The_Range)
    ' Loop to check each cell in the input range to see if equals the
    ' MaxNum variable.
    For Each cell In The_Range
       If cell.Value = MaxNum Then
          ' If the cell value equals the MaxNum variable it
          ' returns the address to the function and exits the loop.
          MaxAddress = cell.Address
          Exit For
       End If
    Next cell
  
  End Function
  </code></pre></li>
  <li><p>The <strong>runtime error</strong> :    </p>
  
  <blockquote>
    <p>I receive "error 91" at the runtime, with the Xmax valuing : "Nothing"
    Error 91 stands for : undefined object or With block variable</p>
  </blockquote></li>
  <li><p>The <strong>min</strong> function (works)</p>
  
  <pre><code>Function MinAddress(The_Range) As Variant
  ' See http://support.microsoft.com/kb/139574
  
  Dim MinNum As Variant
  Dim cell As Range
  
    ' Sets variable equal to maximum value in the input range.
    MinNum = Application.Min(The_Range)
    ' Loop to check each cell in the input range to see if equals the
    ' MaxNum variable.
    For Each cell In The_Range
       If cell.Value = MinNum Then
          ' If the cell value equals the MaxNum variable it
          ' returns the address to the function and exits the loop.
          MinAddress = cell.Address
          Exit For
       End If
    Next cell
  
    End Function
  </code></pre></li>
  </ul>
  
  <p>How I call both functions :</p>
  
  <pre><code>Set rng = ws_source.Range("3:3")
  X_min = MinAddress(rng)
  X_max = MaxAddress(rng) ' returns : X_max = Nothing
  </code></pre>
  
  <p>The data are in the row 3, containing formatted numbers and text.</p>