title: VBA what to dimension a variable when it can take value of a string or number (single/double)
body: <p>I have created a macro that I use for bilinear interpolation and it works fine, however, I want to generate error outputs i.e. "Error: y and x outside of table range" to do this I have done:</p>
  
  <pre><code>Static Function biinterp(x As Double, y As Double, xrng As Range, yrng As Range, zrng As Range) As Double
  
  Dim x1 As Double
  Dim x2 As Double
  Dim y1 As Double
  Dim y2 As Double
  Dim z11 As Double
  Dim z12 As Double
  Dim z21 As Double
  Dim z22 As Double
  
  If y &lt; WorksheetFunction.Min(yrng) And x &lt; WorksheetFunction.Min(xrng) Then
      biinterp = "Error: y and x outside of table range, use your eyes"
  ElseIf y &gt; WorksheetFunction.Max(yrng) And x &lt; WorksheetFunction.Min(xrng) Then
      biinterp = "Error: y and x outside of table range, use your eyes"
  ElseIf y &lt; WorksheetFunction.Min(yrng) And x &gt; WorksheetFunction.Max(xrng) Then
      biinterp = "Error: y and x outside of table range, use your eyes"
  ElseIf y &gt; WorksheetFunction.Max(yrng) And x &gt; WorksheetFunction.Max(xrng) Then
      biinterp = "Error: y and x outside of table range, use your eyes"
  ElseIf x &lt; WorksheetFunction.Min(xrng) Then
      biinterp = "Error: x outside of table range (take a min value for y)"
  ElseIf x &gt; WorksheetFunction.Max(xrng) Then
      biinterp = "Error: x outside of table range (take a max value for y)"
  ElseIf y &gt; WorksheetFunction.Max(yrng) Then
      biinterp = "Error: y outside of table range (take a max value for x)"
  ElseIf y &lt; WorksheetFunction.Min(yrng) Then
      biinterp = "Error: y outside of table range (take a min value for x)"
  else "my function here"
  end if 
  end function
  </code></pre>
  
  <p>If I enter x or y outside the table range I get a #VALUE! error in the cell, but I can't understand why it won't evaluate the else if conditions.</p>
  
  <p>[EDIT] I discovered that since I wished to output a string or double I had to change the dimension for biinterp to string to allow a number or text to be output.</p>