Dynamic Arrays as arguments
 

I have a function that calculates the standard deviation of the last X orders
for a particular day of the week (Sun-Sat). Some products have a longer
sales history than others, and new items could be added in the future. The
spreadsheet is arranged with a worksheet for each product as follows:

weekday in column A (int 1 to 7)
date in column C
order amount in column D
M2:Q8 is a matrix showing the average order level for each day of the week
for about the last 20 weeks

the for loop is supposed to go from the day before the current day back to
the first day used in calculating the average (so that my Stdev and average
are for the same data set).

I use a dynamic array because the data set may hold 20 values, or only a
couple.

When I run the macro I get an "Invalid Call" error. Before I used a static
array and it worked fine. The debugger highlights the last line of code
(where I call StDevP(arrQty)) with the error.

Function FindStdDev(ItemName As String, Day As Integer) As Single
Dim arrQty() As Integer
Dim CurrDateRow As Single
Dim i As Single
Dim k As Integer
Dim currdate As Single

k = 1
currdate = Date
With ThisWorkbook.Worksheets(ItemName)
CurrDateRow = Application.WorksheetFunction.Match(currdate, _
ThisWorkbook.Worksheets(ItemName).Range("C1:C1104" ), 0)
For i = CurrDateRow - 1 To CurrDateRow - _
(7 * .Cells(Day + 1, 16)) Step -1
If .Cells(i, 1) = Day Then
ReDim Preserve arrQty(k)
arrQty(k) = .Cells(i, 4)
k = k + 1
End If
Next
End With

FindStdDev = Application.WorksheetFunction.StDevP(arrQty)
End Function

Dynamic Arrays as arguments
 

I ran a simplified version of your function (below) and it worked okay. I
suspect the data in the array might not be right. I'd set a breakpoint at
the last line of the function and open the Locals windows to see what is in
the array.

Btw, unless you've set Option Base 1 at the top of the module the first
element of the array (element 0) will always be skipped by your code and
have a value of zero.

--
Jim Rech
Excel MVP

Function FindStdDev() As Single
Dim arrQty() As Integer
Dim Cell As Range, i As Integer
i = 1
For Each Cell In Range("A1:A8")
ReDim Preserve arrQty(i)
arrQty(i) = Cell.Value
i = i + 1
Next
FindStdDev = Application.WorksheetFunction.StDevP(arrQty)
End F

Dynamic Arrays as arguments
 

Rather than inefficiently running ReDim Preserve in each iteration of
the loop, you might want to consider something like:

Dim arrQty(1 to 20)
k = 1
.. . .
With etc.
.. . .
If .Cells(i, 1) = Day Then
arrQty(k) = .Cells(i,4)
.. . .
End With
Redim Preserve arrQty(1 to i + 1)

Alan Beban

Marcotte A wrote:
I have a function that calculates the standard deviation of the last X orders
for a particular day of the week (Sun-Sat). Some products have a longer
sales history than others, and new items could be added in the future. The
spreadsheet is arranged with a worksheet for each product as follows:

weekday in column A (int 1 to 7)
date in column C
order amount in column D
M2:Q8 is a matrix showing the average order level for each day of the week
for about the last 20 weeks

the for loop is supposed to go from the day before the current day back to
the first day used in calculating the average (so that my Stdev and average
are for the same data set).

I use a dynamic array because the data set may hold 20 values, or only a
couple.

When I run the macro I get an "Invalid Call" error. Before I used a static
array and it worked fine. The debugger highlights the last line of code
(where I call StDevP(arrQty)) with the error.

Function FindStdDev(ItemName As String, Day As Integer) As Single
Dim arrQty() As Integer
Dim CurrDateRow As Single
Dim i As Single
Dim k As Integer
Dim currdate As Single

k = 1
currdate = Date
With ThisWorkbook.Worksheets(ItemName)
CurrDateRow = Application.WorksheetFunction.Match(currdate, _
ThisWorkbook.Worksheets(ItemName).Range("C1:C1104" ), 0)
For i = CurrDateRow - 1 To CurrDateRow - _
(7 * .Cells(Day + 1, 16)) Step -1
If .Cells(i, 1) = Day Then
ReDim Preserve arrQty(k)
arrQty(k) = .Cells(i, 4)
k = k + 1
End If
Next
End With

FindStdDev = Application.WorksheetFunction.StDevP(arrQty)
End Function

Dynamic Arrays as arguments
 

Thanks for the help Jim and Alan. It was my data that was messing it up.
For one sales item I have less than a week of history, so ArrQty had a null
entry.

"Marcotte A" wrote:

I have a function that calculates the standard deviation of the last X orders
for a particular day of the week (Sun-Sat). Some products have a longer
sales history than others, and new items could be added in the future. The
spreadsheet is arranged with a worksheet for each product as follows:

weekday in column A (int 1 to 7)
date in column C
order amount in column D
M2:Q8 is a matrix showing the average order level for each day of the week
for about the last 20 weeks

the for loop is supposed to go from the day before the current day back to
the first day used in calculating the average (so that my Stdev and average
are for the same data set).

I use a dynamic array because the data set may hold 20 values, or only a
couple.

When I run the macro I get an "Invalid Call" error. Before I used a static
array and it worked fine. The debugger highlights the last line of code
(where I call StDevP(arrQty)) with the error.

Function FindStdDev(ItemName As String, Day As Integer) As Single
Dim arrQty() As Integer
Dim CurrDateRow As Single
Dim i As Single
Dim k As Integer
Dim currdate As Single

k = 1
currdate = Date
With ThisWorkbook.Worksheets(ItemName)
CurrDateRow = Application.WorksheetFunction.Match(currdate, _
ThisWorkbook.Worksheets(ItemName).Range("C1:C1104" ), 0)
For i = CurrDateRow - 1 To CurrDateRow - _
(7 * .Cells(Day + 1, 16)) Step -1
If .Cells(i, 1) = Day Then
ReDim Preserve arrQty(k)
arrQty(k) = .Cells(i, 4)
k = k + 1
End If
Next
End With

FindStdDev = Application.WorksheetFunction.StDevP(arrQty)
End Function

Dynamic Arrays as arguments
 

My comment was not directed at solving the problem you originally posted
about. Now that you have solved that problem, you might want to
consider my comment.

Alan Beban

Marcotte A wrote:
Thanks for the help Jim and Alan. It was my data that was messing it up.
For one sales item I have less than a week of history, so ArrQty had a null
entry.

"Marcotte A" wrote:


I have a function that calculates the standard deviation of the last X orders
for a particular day of the week (Sun-Sat). Some products have a longer
sales history than others, and new items could be added in the future. The
spreadsheet is arranged with a worksheet for each product as follows:

weekday in column A (int 1 to 7)
date in column C
order amount in column D
M2:Q8 is a matrix showing the average order level for each day of the week
for about the last 20 weeks

the for loop is supposed to go from the day before the current day back to
the first day used in calculating the average (so that my Stdev and average
are for the same data set).

I use a dynamic array because the data set may hold 20 values, or only a
couple.

When I run the macro I get an "Invalid Call" error. Before I used a static
array and it worked fine. The debugger highlights the last line of code
(where I call StDevP(arrQty)) with the error.

Function FindStdDev(ItemName As String, Day As Integer) As Single
Dim arrQty() As Integer
Dim CurrDateRow As Single
Dim i As Single
Dim k As Integer
Dim currdate As Single

k = 1
currdate = Date
With ThisWorkbook.Worksheets(ItemName)
CurrDateRow = Application.WorksheetFunction.Match(currdate, _
ThisWorkbook.Worksheets(ItemName).Range("C1:C1104" ), 0)
For i = CurrDateRow - 1 To CurrDateRow - _
(7 * .Cells(Day + 1, 16)) Step -1
If .Cells(i, 1) = Day Then
ReDim Preserve arrQty(k)
arrQty(k) = .Cells(i, 4)
k = k + 1
End If
Next
End With

FindStdDev = Application.WorksheetFunction.StDevP(arrQty)
End Function