title: user defined function in excel works in VBA and returns #value error in worksheet
body: <p>I have designed a UDF to get the average sales of a product without the promotion period. <br>
  The way it should work that it replaces the values of sales in promotion period with <code>""</code> and then get the average of sales. <br>
  Note that: the same product may have one or more promotional period <br>
  It works well when called in VBA or by MsgBox, but returns <code>#Value</code> in the worksheet</p>
  
  <pre><code>Public Function promless(itemcode As Range, daterange As Range, salesvalues As Range) As Double
  
   Application.Volatile
  
  'find promotion rows
  
  Dim porrange1 As Range
  Dim pormotionrow() As Long
  ReDim pormotionrow(1 To 10)
  Dim code As String
  code = itemcode.Value
  
  
     Set porrange1 = Sheets("por").Range("a:a").Find(code)
    pormotionrow(1) = porrange1.Row
  
  If Not pormotionrow(1) = 0 Then
    For n = 2 To 10
     pormotionrow(n) = Sheets("por").Range("a:a").FindNext(porrange1).Row
     If pormotionrow(n) = pormotionrow(n - 1) Then
       pormotionrow(n) = 0
       Exit For
     End If
    Next
  End If
  Size = n - 1
  ReDim Preserve pormotionrow(1 To Size)
  '-------------------------------------------------------------------------------------
  'put sart and end of promotions
  Dim porstart(), porend() As Variant
  ReDim porstart(1 To Size)
  ReDim porend(1 To Size)
  
  For n = 1 To Size
    porstart(n) = Sheets("por").Range("c" &amp; pormotionrow(n)).Value
  Next
  
  For n = 1 To Size
    porend(n) = Sheets("por").Range("d" &amp; pormotionrow(n)).Value
  Next
  '--------------------------------------------------------------
  'get data range date and apply pormotion conditions
  
  Dim daterangearr As Variant
  daterangearr = daterange.Value
  
  For n = 1 To daterange.Columns.Count
    For i = 1 To Size
      If daterangearr(1, n) &gt;= porstart(i) And daterangearr(1, n) &lt;= porend(i) Then
       daterangearr(1, n) = "por"
      End If
    Next
  Next
  
  '----------------------------------------------------------------
  'get sales values
  
  Dim salesvaluesarr As Variant
  salesvaluesarr = salesvalues.Value
  
  'check if the date range is the same size of sales values
  If salesvalues.Columns.Count &lt;&gt; daterange.Columns.Count Then
    MsgBox ("date range must be same number of sales values")
  End If
  
  'empty sales values that are in pormotion
  For n = 1 To salesvalues.Columns.Count
    If daterangearr(1, n) = "por" Then salesvaluesarr(1, n) = Empty
  Next
  promless = Application.WorksheetFunction.Average(salesvaluesarr)
  
   End Function
  </code></pre>
  
  <p>In then worksheet for which I have the promotion information: </p>
  
  <p><a href="https://i.stack.imgur.com/sW0yV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sW0yV.png" alt="pormotion sheet"></a></p>
  
  <p>and the original sheet contains the sales data: </p>
  
  <p><a href="https://i.stack.imgur.com/PqAx4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PqAx4.png" alt="data sheet"></a></p>