Nice job osorico. That was very efficient. Ok, so I've got one I'm stumped on. It seems very simple, but I just can't seem to get it. I have an index of say 100 values, call it data(100). I want to step through the index with a sliding window and find the maximum of prior 10 values. i.e. max(10)=maximum of index 1-10 max(11)=maximum of index 2-11 . max(99)= maximum of index 89-99 max(100)=maximum of index 90-100 This is so easy to do with ranges. But, when I use array elements, it seems difficult. Can you or anyone else come up with a loop to do this? Thanks. btw max(1) to max(9) can just be 0
I'm not sure you've explained what your desired result is but here's an XL specific "trick"... Arrays can be used in *certain* cases in place of a range. Here, WorsheetFunction.Max can be used like this... Code: Option Explicit Dim ary(100) As Long Sub arraymax() Dim i As Long For i = 1 To UBound(ary) ary(i) = Int((i * Rnd) + 1) Next i MsgBox Application.WorksheetFunction.Max(ary) End Sub Just split your array into different temporary segments (a temp array) and pass it. As alternative, *sometimes*, mostly due to ever unknown/changing amounts of data or featuritis, dumping contents onto a temporary hidden worksheet and performing the task is the simplest. Personally, I almost always set up a hidden worksheet. Reduced need for File i/o and things like ary sorts, etc. Good luck, Osorico
I'm not sure you've explained what your desired result is but here's an XL specific "trick"... Arrays can be used in *certain* cases in place of a range. Here, WorsheetFunction.Max can be used like this... code: Option Explicit Dim ary(100) As Long Sub arraymax() Dim i As Long For i = 1 To UBound(ary) ary(i) = Int((i * Rnd) + 1) Next i MsgBox Application.WorksheetFunction.Max(ary) End Sub Just split your array into different temporary segments (a temp array) and pass it. As alternative, *sometimes*, mostly due to ever unknown/changing amounts of data or featuritis, dumping contents onto a temporary hidden worksheet and performing the task is the simplest. Personally, I almost always set up a hidden worksheet. Reduced need for File i/o and things like ary sorts, etc. Good luck, Osorico Thanks Osorico. Although this works, I was able to get that far. The problem I'm having is splitting up the segments. I want to have a nested loop that 1st looks at the 1st 10 elements of the array and spits out the max of those 10 elements into another array. Then the next 10 and so on. So, if I call the output array max_val(100), it should have the following outputs as mentioned on earlier post: max_val(10)= maximum of data (1) to (10) max_val(11) = maximum of data (2) to (11) and so on until max_val(100)=maximum of data (90) to (100) So the system reads in a 100 element data array called data, and outputs a 100 element array called max_val. The 1st 10 elements can just be zero, or a more sophisticated version would have element 1 give max of 1, element 2 max of (1) and (2)... up to element (10) It would be so much simpler if I could just say worksheetfunction.max(i:i+10), then loop that 100 times, but I can't since it isn't a range. Can you extend this to do what I'm describiing?
Since Im having a tuf morning I took a moment and wrote a simple function for you. Analyze your array in however many segments you want. Make different versions for different analysis. Heed the comment NOTE. Code: Function ArraySegmentMax(theArray() As Long, bidx As Long, eidx As Long) As Variant 'NOTE: theArray() argument must be typed same as passed in array. I think is an XL bug. Dim i As Long Dim retval As Variant 'Gotta handle potential array bounds errors and possibly type mismatch on compare. 'It's up to architect to pass an array that contains comparable comtent. On Error GoTo ERR_PROC 'def return value. Use "Not IsNull" to validate. Good reason to return a variant! retval = vbNull 'Find the MAX number within the supplied bounds of the supplied array For i = bidx To eidx If theArray(i) > retval Then retval = theArray(i) End If Next i EXIT_PROC: 'return found value, or vbNull ArraySegmentMax = retval Exit Function ERR_PROC: Debug.Assert False retval = vbNull Resume EXIT_PROC End Function Osorico
Osorico, appreciate your help, but it's still not what i'm looking for. ------------------------------------------- What I'm looking for sub max_value() dim data(100) as variant dim max_val(100) as variant dim max_val_len as integer max_val_len = 10 For i= 1 to 100 data(i) = Cells(i,"A") next i For j = 1 to 100 'max_val(j) returns maximum 'of a fixed length window of data(100) 'array next j end sub -------------------------------------------- It should follow the above format. Not a function. If I set max_val_len = 10, then each time j loops, it returns the maximum of 10 bits of data. j= 1 returns max_val(1) which is the maximum of data(1) to data(10) j=2 returns max_val(2) which is the maximum of data (2) to data(11) and so on until say j=90 which returns max_val(90) or maximum of data (90) to data(100) remaining 10 bits can be zero.
Wrong. My previous post DOES do what you want. Maybe YOUR format needs to adjust. dim j as variant j = ArraySegmentMax(max_val, 1, 10) if Not IsNull(j) then ... j = ArraySegmentMax(max_val, 2, 11) if Not IsNull(j) then ... j = ArraySegmentMax(max_val, 3 12) if Not IsNull(j) then ... Not the most compact solution, but it DOES do what you want, and does it somewhat generically (ie reusable). Based on what you're doing, you should look into the temp hidden worksheet concept... Create a new worksheet, hidden variablize the sheet dump array contents into column A or whatever variablize the range write Range-based code to process clear the range for reuse, if needed delete the sheet when finished
I figured out a way to do it in the format i mentioned. Just not as efficient as I was looking for. --------------------------------------------------- I tried the array function you wrote, and unless I'm missing something, it returns #value! as a result. I just took a range of cells, then set a cell=ArraySegmentMax(F10:F17,1,10) for example, with F10 to F17 containing data. It returned #value! Is there something I'm not doing right?
It accepts an an array, not a range. And the array argument must be typed the same as the passed in array. Did you read the code and the comments?
"And the array argument must be typed the same as the passed in array. Did you read the code and the comments?" I don't understand what it means to type something the same as the "passed in array". The good news is i put it in a loop and after some slight changes it works! For some reason it had problems if I entered Function ArraySegmentMax(theArray() As long, bidx As long, eidx As long) As variant It kept complaining that i was entering the wrong array argument type. In my loop I passed the following to the function: max(j) = ArraySegmentMax(data(), j, j + 9) I guess what you meant by typing in the same as passed in array, was that the data type declarations had to be identical (both arrays must be declared long or variant)? My data array , data(100) was declared as variant. I then changed all the Function's long declarations to variants and it accepted them. Function ArraySegmentMax(theArray() As variant, bidx As variant, eidx As variant) As variant The only other line is it complained about was Debug.Assert False as a syntax error, so i just commented it out as it worked ok without it. But this is great, thanks very much for helping on this.