title: VBA User Defined Function only Working on one Worksheet in Workbook
body: <p>I have the below code that creates an array from a table in excel and then searches that array based on multiple criteria from the inputs.</p>
  
  <pre><code>Function output_string(id As Long, dt As Date, descr As String)
  
  'set variables
  Dim myarray As Variant
  Dim ws As Worksheet
  Dim col_count As Integer
  Dim row_count As Long
  Dim source_range As Range
  
  'set worksheet
  Set ws = Worksheets("Data Store")
  
  dt = DateValue(app_dt)
  
  'set up column and row counts for populated range
  col_count = ws.Range("A2").End(xlToRight).Column
  row_count = ws.Range("A2").End(xlDown).Row
  
  'set range for populated cell space
  Set source_range = ws.Range(Cells(2, 1), Cells(row_count, col_count))
  
  'create the dimensions of the array that will store the table
  ReDim myarray(1 To source_range.Rows.Count, 1 To source_range.Columns.Count)
  
  'load data from excel table into array
  myarray = source_range.Value
  
  
  'get row with matching criteria
  For i = LBound(myarray) To UBound(myarray)
      If myarray(i, 1) = id And dt &gt;= myarray(i, 2) And dt &lt;= myarray(i, 4) _
         And descr = myarray(i, 5) Then
         output_string = myarray(i, 3)
         Exit For
      End If
  Next i
  
  End Function
  </code></pre>
  
  <p>The function gives the correct output.  The problem is that the function only works on the <code>Data Store</code> worksheet.  All the other worksheets return the <code>#VALUE!</code> error when I put in the exact same input.  The arguments to the function can be cell references from other worksheets without erroring out. </p>
  
  <p>Also, every once and a while (seemingly at random) the working function on the <code>Data Source</code> tab will convert from a correct value to <code>#VALUE!</code> as well.</p>
  
  <p>I have the function in a regular module in the same workbook that I'm using.  The module also has a sub program in it. </p>
  
  <p>I'm really stumped on this one.  Has anyone seen this before/do you know a solution? </p>