title: My VBA Excel function works when called by a test function but consitently fails when called from sheet another function that is a embedded in a cell
body: <pre><code>Public Sub addtoMA(dbPrice As Double, dbRow As Double, sh As Worksheet)
  Dim s As Long   'for bitshifting the array
  Const colstosave = 50
  Dim rn As Range, intPrice() As Variant
  deActsheet  'stop events and other annoyance
  On Error GoTo catch
  If dbRow = 0 Then
      'MsgBox "row number missing in addtoma"
      GoTo finally
  End If
  Set rn = sh.Range("At" &amp; dbRow &amp; ":cQ" &amp; dbRow) 'the row
  intPrice() = rn 'the  array
     ' shift elements one position right- e.g. arr 99 moves to arr 100
     For s = colstosave To 2 Step -1
          If intPrice(1, s - 1) &lt;&gt; "" Then
              intPrice(1, s) = intPrice(1, s - 1)
          Else
              intPrice(1, s) = 0
          End If
     Next s
  intPrice(1, 1) = dbPrice 'current price
  
      rn = intPrice() 'store the array
  
  finally:
  Set rn = Nothing
  
  
    actSheet 'allow events and other annoyance
   Exit Sub
  catch:
  
    'MsgBox Err.Description
    Debug.Print ""
    GoTo finally
  End Sub
  </code></pre>
  
  <p>The code above runs perfectly when I call form the immediate window with:
  addtoMA 5,9,sheetpointer</p>
  
  <p>in integration it is called by a function that is embedded as a formula.
  The parameters the are receibed are identical I double checked this.
  <code>rn.rows</code> and <code>rn.columns.count</code> are exactly the same dimensions as
  <code>ubound(intprice,1)</code> and <code>ubound(intprice,2</code>)</p>
  
  <p>Yet every time it is called from the sheet it fails with</p>
  
  <blockquote>
    <p>Application-defined or object-defined error</p>
  </blockquote>
  
  <p>I could just use a database, but I cant be beaten by this.</p>
  
  <p>Any ideas?
  It just generates a few moving averages for a bot</p>