Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with arrays as arguments | Excel Worksheet Functions | |||
Dynamic arrays | New Users to Excel | |||
Dynamic Arrays | Excel Worksheet Functions | |||
Dynamic Arrays | Excel Programming | |||
Arrays as Arguments in Functions | Excel Programming |