Thread: Array function question

Hi, I am trying to create a function that takes an array, an optional cap and an optional floor and returns an array with each value less than the floor replaced by the floor value, and each value higher than the cap replaced by the cap value. Test the function by calling it from an array formula on a sheet and by writing to a range from a VBA array.

I have written this which I think is correct:

Public Function ArrayCapFloor(myarray As Variant, floor As Long, cap As Long)

For n = LBound(myarray) To UBound(myarray)
If myarray(n) < floor Then ' replace value below floor with floor
myarray(n) = floor
End If
If myarray(n) > cap Then ' replace value above cap with cap
myarray(n) = cap
End If
Next n


I do not know how to do the last bit asking to test the function. Can anyone help?
I even tried to write a simple macro to post it to a sheet but know that something is wrong:

Sub TestArrayCapFloorFunction()
Dim myarray(1 To 20) As Variant 'declare array
Dim newarray
Dim floor As Long, cap As Long

floor = 33 'some arbitrary number
cap = 107

'Fill array from 10 to 200 arbitrarily
For n = LBound(myarray) To UBound(myarray)
myarray(n) = n * 10
Next n

'outupt array to excel to see results
Range("a1").Select
For n = LBound(myarray) To UBound(myarray)
ActiveCell.Formula = myarray(n)
ActiveCell.Offset(1, 0).Select
Next n

newarray = ArrayCapFloor(myarray, floor, cap)

'replace array contents according to floor and cap USING CREATED FUNCTION
Range("B1").Select

For n = LBound(newarray) To UBound(newarray)
' 'output array to excel to see results
ActiveCell.Formula = newarray(n)
ActiveCell.Offset(1, 0).Select
Next n

End Sub


If anyone can shed any light, I would be very grateful.

THANKS

Ah sorry I am new to this:

Public Function ArrayCapFloor(myarray As Variant, floor As Long, cap As Long)

For n = LBound(myarray) To UBound(myarray)
    If myarray(n) < floor Then ' replace value below floor with floor
        myarray(n) = floor
    End If
    If myarray(n) > cap Then ' replace value above cap with cap
        myarray(n) = cap
    End If
    'output array to excel to see results
'    ActiveCell.Formula = robarray(n)
'    ActiveCell.Offset(1, 0).Select
Next n

End Function

That is my function code....

My test code which I know is slightly wrong is:

Sub TestArrayCapFloorFunction()
Dim myarray(1 To 20) As Variant 'declare array
Dim newarray
Dim floor As Long, cap As Long

floor = 33 'some arbitrary number
cap = 107

'Fill array from 10 to 200 arbitrarily
For n = LBound(myarray) To UBound(myarray)
        myarray(n) = n * 10
Next n

'outupt array to excel to see results
Range("a1").Select
For n = LBound(myarray) To UBound(myarray)
        ActiveCell.Formula = myarray(n)
        ActiveCell.Offset(1, 0).Select
Next n

 newarray = ArrayCapFloor(myarray, floor, cap)

'replace array contents according to floor and cap USING CREATED FUNCTION
Range("B1").Select

For n = LBound(newarray) To UBound(newarray)
'    'output array to excel to see results
    ActiveCell.Formula = newarray(n)
    ActiveCell.Offset(1, 0).Select
Next n

End Sub

Many thanks in advance

Wow, thanks so much. Such a simple solution to my problem.

One more request, do you have a simple solution to the final part of the task:

"Test the function by calling it from an array formula on a sheet and by writing to a range from a VBA array."

If I'm honest, I don't totally understand what this is asking.

Many thanks

Kind of.... but I really have done most of the work. I just haven't come across this type of scenario before. If I can understand how to do it once, I'm sure I'll understand it for the future. Much appreciated all help.