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]

Using that function for the SMA is a very hard way of getting a SMA, I always use very simple formula where I can alter the length of the SMA just by changing a value in a cell.
If you put the value 5 into C1 of book 3, then put this equation into C6 and copy it down

=AVERAGE(OFFSET(A7,-C$1,0,C$1))

Note you get the identical values that you have in column B without using and array formula. If you change the value in C1 to 10 you get a ref error until you get the full 10 values. You can get round this with iferror logic if you need to

 

[GeeWhiz7]

Using that function for the SMA is a very hard way of getting a SMA, I always use very simple formula where I can alter the length of the SMA just by changing a value in a cell.
If you put the value 5 into C1 of book 3, then put this equation into C6 and copy it down

=AVERAGE(OFFSET(A7,-C$1,0,C$1))

Note you get the identical values that you have in column B without using and array formula. If you change the value in C1 to 10 you get a ref error until you get the full 10 values. You can get round this with iferror logic if you need to

Totally agree with you that it is complicated. I actually used to use this formula in a worksheet to do pretty much what you are talking about,

=AVERAGE(OFFSET(D4,0,0,-(MIN(ROW()-ROW($D$3)+1,$E$1)),1))

But, I cannot figure out how to do the same thing in vba array / memory only before writing out to sheet.

I would like to be able to pass a series of columns one at a time in vba to array, compute the moving average and then write out to sheet but I haven’t been successful yet so was looking for code to do it.

 

[offthelip]

try this code:

Sub test()
Dim outarr()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
ReDim outarr(1 To lastrow, 1 To 1)
Call smacalc(inarr, outarr, 10)
 Range(Cells(1, 2), Cells(lastrow, 2)) = outarr

End Sub

Sub smacalc(inpur As Variant, outpur As Variant, tm As Variant)
For i = tm To UBound(inpur)
   sumv = 0
   For j = i - tm + 1 To i
    sumv = sumv + inpur(j, 1)
   Next j
   outpur(i, 1) = sumv / tm
Next i

End Sub

You can changethe sub smacalc to a function if that is whatyou need just by changing the name to function and assigning the output to it

 

[GeeWhiz7]

try this code:

Sub test()
Dim outarr()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
ReDim outarr(1 To lastrow, 1 To 1)
Call smacalc(inarr, outarr, 10)
 Range(Cells(1, 2), Cells(lastrow, 2)) = outarr

End Sub

Sub smacalc(inpur As Variant, outpur As Variant, tm As Variant)
For i = tm To UBound(inpur)
   sumv = 0
   For j = i - tm + 1 To i
    sumv = sumv + inpur(j, 1)
   Next j
   outpur(i, 1) = sumv / tm
Next i

End Sub

You can changethe sub smacalc to a function if that is whatyou need just by changing the name to function and assigning the output to it

you've rescued me a couple times now...not sure my coding ability will every repay, but I do appreciate your help.
This is quite compact and does just what I need. I guess the reason I was fixated on the longer solution was the ability to give an average when the SMA value was less than N by simply using whatever count was there until reaching N. I think I can figure out how to modify what you've given me here to do that and if so will repost it.

Thanks again and I am trying to do as you say and NEVER access the worksheet in a loop. Pretty challenging for me at this point.

 

[GeeWhiz7]

you've rescued me a couple times now...not sure my coding ability will every repay, but I do appreciate your help.
This is quite compact and does just what I need. I guess the reason I was fixated on the longer solution was the ability to give an average when the SMA value was less than N by simply using whatever count was there until reaching N. I think I can figure out how to modify what you've given me here to do that and if so will repost it.

Thanks again and I am trying to do as you say and NEVER access the worksheet in a loop. Pretty challenging for me at this point.

Okay, I'm stuck a little bit again. I'm trying to make this work for a variant array with multiple columns where I want to loop through each column and do the average with your code.
I can figure out the SLOW way by doing

Sub SlowWayAvg()
   
Dim outarr()
LastRow = 25

For i = 1 To 2

inarr = Range(Cells(2, i), Cells(LastRow, i))
ReDim outarr(1 To LastRow, 1 To 1)
Call smacalc(inarr, outarr, 4) 'SMA changed to 4
 Range(Cells(28, i), Cells(51, i)) = outarr 'output in same column, but below input
Next i
End Sub

But I can't quite get a faster way.
Presumably this would be by reading the entire input data into inarr in one go and then sending only one column at a time from inarr to the smacalc procedure.
This is what I'm thinking at the moment...gives me a wonky result as I'm still shaky on looping through the array indicies.

Sub FasterWay()
    Dim inarr()
    Dim outarr()
    Dim temparr()
    Dim x As Long

LastRow = 25

    inarr = Range(Cells(2, 1), Cells(LastRow, 2))
       
    ReDim outarr(1 To LastRow, 1 To 2)
    ReDim temparr(1 To LastRow, 1 To 1)
   
    For x = 1 To 2
        For i = 1 To 24
        temparr(i, 1) = inarr(i, x)
       
        Call smacalc(temparr, outarr, 4) 'changed SMA to 4 for example
        Range(Cells(28, i), Cells(51, i)) = outarr 'output in same column, but below input
       
        Next i
    Next x
       
End Sub

mini-sheet with two input data columns...trying to do column 1, then 2 in the code above and write out the results below the input columns.

Book3.xlsm
	A	B
1	Input
2	8	8
3	8	16
4	41	123
5	58	58
6	61.75	61.75
7	62.6	187.8
8	65.8	131.6
9	51.6	154.8
10	35.4	70.8
11	23.4	23.4
12	17.2	17.2
13	27.6	55.2
14	39.8	79.6
15	45.8	91.6
16	56	56
17	49.4	98.8
18	49	49
19	45	135
20	44.6	89.2
21	42.8	85.6
22	58.6	117.2
23	60.2	180.6
24	63.4	126.8
25	57.6	172.8
Sheet1

 

[offthelip]

I am not at a pc at the moment so can’t write any code. However the way I would do multiple columns is to create another smacalc subroutine that cater for 2 dimensions and do the loop through the columns there. This saves the multiple read and writes to the worksheet

 

[GeeWhiz7]

I am not at a pc at the moment so can’t write any code. However the way I would do multiple columns is to create another smacalc subroutine that cater for 2 dimensions and do the loop through the columns there. This saves the multiple read and writes to the worksheet

No worries and thank you for the reply. That makes sense, let me see if I can figure it out.

 

[GeeWhiz7]

I am not at a pc at the moment so can’t write any code. However the way I would do multiple columns is to create another smacalc subroutine that cater for 2 dimensions and do the loop through the columns there. This saves the multiple read and writes to the worksheet

That works perfectly and fast! Thank you

Sub test2d()
    
    Dim inarr, outaar As Variant
        
    inarr = Range(Cells(2, 1), Cells(25, 2))
    ReDim outarr(1 To 24, 1 To 2)
    
    Call smacalc2d(inarr, outarr, 2)
    Range(Cells(28, 1), Cells(51, 2)) = outarr
    
End Sub

Sub smacalc2d(inpur As Variant, outpur As Variant, tm As Variant) '#passed inarr, outarr and sma value N# in
    
Dim h, i, j As Long
Dim sumv As Double

    For h = 1 To 2
        For i = tm To UBound(inpur)
            sumv = 0
            
            For j = i - tm + 1 To i '
                sumv = sumv + inpur(j, h)
            Next j
            outpur(i, h) = sumv / tm
        Next i
    Next h
    
End Sub

 

[offthelip]

Well done, My only suggestion is to change the loop
for h =1 to 2
Into
For h=1 to unbound(input,2)
Which will then automatically deal with any number of columns
Sent from IPhone apologies for format