excel code

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

Quote from dtrader98:

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
More...
 
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?
 
Quote from dtrader98:

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?
More...

Since Im having a tuf morning :confused: 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.
 
Quote from dtrader98:

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.
More...

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?
 
Quote from dtrader98:

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?
More...

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.
 
You must log in or register to reply here.
Back
Top