Why does this function work in one sheet, but not another

[GeeWhiz7]

Little bit complicated code I found somewhere here on the forum, but cant seem to re-find.

That said, I want to use the very useful SMA function below (written by Bobby if I recall correctly?) and port it to work in the array calculations in vba, not in the sheet.
My first hiccup is that it works in a new worksheet, see second mini-sheet, but not in another sheet I have (first mini-sheet).

Goal: 1) Make work in any workbook and 2) only in vba array/memory by passing a array to it using a For Loop

TestSMA.xlsb
	A	B
1	Input Array	SMA 10
2	61	#NAME?
3	31	#NAME?
4	24	#NAME?
5	21	#NAME?
6	4	#NAME?
7	70	#NAME?
8	68	#NAME?
9	23	#NAME?
10	43	#NAME?
11	85	#NAME?
12	34	#NAME?
13	86	#NAME?
14	82	#NAME?
15	79	#NAME?
16	50	#NAME?
17	95	#NAME?
18	79	#NAME?
19	72	#NAME?
20	10	#NAME?
21	5	#NAME?
22	15	#NAME?
23	16	#NAME?
24	79	#NAME?
25	12	#NAME?
26	16	#NAME?
27	33	#NAME?
28	71	#NAME?
29	82	#NAME?
30	44	#NAME?
31	95	#NAME?
32	23	#NAME?
33	84	#NAME?
34	58	#NAME?
35	44	#NAME?
36	34	#NAME?
37	15	#NAME?
38	85	#NAME?
39	84	#NAME?
40	47	#NAME?
41	83	#NAME?
42	9	#NAME?
43	46	#NAME?
44	9	#NAME?
45	30	#NAME?
46	34	#NAME?
47	29	#NAME?
48	84	#NAME?
49	95	#NAME?
50	62	#NAME?
51	71	#NAME?
Sheet2

Cell Formulas
Range	Formula
A2:A51	A2	=RANDBETWEEN(2,100)
B2:B51	B2	=SMA(A2:A51,10)
Press CTRL+SHIFT+ENTER to enter array formulas.

Book3
	A	B
1	Input Array	SMA 5
2	8	8
3	74	41
4	92	58
5	73	61.75
6	66	62.6
7	24	65.8
8	3	51.6
9	11	35.4
10	13	23.4
11	35	17.2
12	76	27.6
13	64	39.8
14	41	45.8
15	64	56
16	2	49.4
17	74	49
18	44	45
19	39	44.6
20	55	42.8
21	81	58.6
22	82	60.2
23	60	63.4
24	10	57.6
25	64	59.4
26	89	61
27	61	56.8
28	26	50
29	53	58.6
30	58	57.4
31	40	47.6
32	46	44.6
33	56	50.6
34	73	54.6
35	90	61
36	47	62.4
37	74	68
38	70	70.8
39	18	59.8
40	80	57.8
41	58	60
42	20	49.2
43	78	50.8
44	60	59.2
45	25	48.2
46	25	41.6
47	94	56.4
48	76	56
49	89	61.8
50	49	66.6
51	21	65.8
Sheet1

Cell Formulas
Range	Formula
B2:B51	B2	=SMA(A2:A51,5)
Press CTRL+SHIFT+ENTER to enter array formulas.

Code

Function SMA(DataValues As Range, NumPeriods As Long, Optional ReturnElement As Long)
    Dim arrData, _
        arrSMA, _
        arrCumulative, _
        i As Long, _
        j As Long, _
        tempsum As Double
    
    NumPeriods = Abs(NumPeriods)
    
    arrData = Range_to_1D_Array(DataValues)
    arrData = ResizeArrayToAllNumeric(arrData, True)
    
    ReDim arrCumulative(1 To UBound(arrData)) As Double
    ReDim arrSMA(1 To UBound(arrData)) As Double
    
    For i = LBound(arrData) To UBound(arrData)
        tempsum = 0
        If i = 1 Then
            arrCumulative(i) = arrData(i)
        Else
            arrCumulative(i) = arrData(i) + arrCumulative(i - 1)
        End If
        If i < NumPeriods Then
            arrSMA(i) = arrCumulative(i) / i
        Else
            For j = i - NumPeriods + 1 To i
                tempsum = tempsum + arrData(j)
            Next j
            arrSMA(i) = tempsum / NumPeriods
        End If
    Next i
    
    If ReturnElement Then
        If ReturnElement > UBound(arrSMA) Then
            SMA = CVErr(xlErrNA)
        Else
            SMA = arrSMA(ReturnElement)
        End If
    Else
        If Application.Caller.Rows.Count > 1 Then
            SMA = Excel.Application.Transpose(arrSMA)
        Else
            SMA = arrSMA
        End If
    End If
    End Function


Function Range_to_1D_Array(ByVal Rng As Range)

    Dim ReturnArray, _
        arrTemp, _
        i As Long, _
        j As Long, _
        k As Long
    
    If Rng.Cells.Count = 1 Then
        ReDim ReturnArray(1 To 1)
        ReturnArray(1) = Rng
    Else
        If Rng.Columns.Count > 1 And Rng.Rows.Count > 1 Then
            arrTemp = Rng.Value
            ReDim ReturnArray(1 To UBound(arrTemp, 1) * UBound(arrTemp, 2))
            k = 0
            For i = 1 To UBound(arrTemp, 1)
                For j = 1 To UBound(arrTemp, 2)
                    k = k + 1
                    ReturnArray(k) = arrTemp(i, j)
                Next j
            Next i
            Erase arrTemp
        Else
            With Excel.Application
                ReturnArray = .Transpose(Rng)
                If Rng.Rows.Count = 1 Then ReturnArray = .Transpose(ReturnArray)
            End With
        End If
    End If
    
    Range_to_1D_Array = ReturnArray
    Erase ReturnArray
    End Function

Function ResizeArrayToAllNumeric(vArr, Optional ZeroMissingandText As Boolean = False)
    Dim vOut, _
        i As Long, _
        Counter As Long
        
    ReDim vOut(LBound(vArr) To UBound(vArr)) As Double
        For i = LBound(vArr) To UBound(vArr)
            If ZeroMissingandText Then
                Counter = Counter + 1
                If IsNumeric(vArr(i)) Then
                    vOut(i) = vArr(i)
                Else
                    vOut(i) = 0
                End If
            Else
                If IsNumeric(vArr(i)) Then
                    Counter = Counter + 1
                    vOut(Counter) = vArr(i)
                End If
            End If
        Next i
    ReDim Preserve vOut(1 To Counter) As Double
    ResizeArrayToAllNumeric = vOut
    Erase vOut
    End Function
Function ArrayDimensions(InputArray As Variant) As Long
    Dim n As Long

    If Not IsArray(InputArray) Or IsObject(InputArray) Then
        n = 0
        Exit Function
    Else
        n = 1
    End If

    On Error Resume Next
        Do
            n = n + 1
        Loop While (LBound(InputArray, n) <= UBound(InputArray, n))
        ArrayDimensions = n - 1
    Err.Clear
    On Error GoTo 0
    End Function

 

[offthelip]

That should be ubound and inpur, blinking autocorrect