Thread: Creating An Array to Be Used In a User Defined Function

Hey Everyone,

I'm not sure if its possible to do what I am asking but here goes.

I've created a function that does a "Cash Calculation" on one set of data.

Function CashCalc(ByVal CurSignal As Integer, ByVal PrevSignal As Integer, ByVal CurShares As Integer, ByVal PrevShares As Integer, ByVal ClosePrice As Double, ByVal PrevCash As Double)

Dim ReturnValue As Double
ReturnValue = PrevCash

If CurSignal = 0 And PrevSignal = 0 Then
    ReturnValue = PrevCash

ElseIf CurSignal = PrevSignal Then
    ReturnValue = PrevCash

ElseIf CurSignal <> PrevSignal And CurSignal > 0 Then
    ReturnValue = ReturnValue - ClosePrice * (CurShares - PrevShares)

ElseIf CurSignal <> PrevSignal And CurSignal = 0 Then
    ReturnValue = ReturnValue + ClosePrice * PrevShares

End If

CashCalc = ReturnValue

End Function

Eventually though, I will need to do this calculation for 30+ sets of data. For each row in the spread sheet there would be numerous times when I would need to either add or subtract from the return value.

So my question is this. Can I use an array to do this calculation across the 30+ sets of data. For example, instead of the "CurSignal" being one variable it would be an array. The code would then go through a loop taking the first value of each array, doing the calculations of the "ReturnValue", then move on to the second value of each array and looping over and over until all data sets are analyzed and returning a final value.

I'm thinking of it similar to how the SUM Function works in excel, it doesn't care how many cells I use... it adjusts for the calculation, once it has received the array.

I've attached a sample spread sheet to explain. Currently the function has been used with Data Set 2, imagine if you will I wanted to use this calculation with both data sets, then maybe 5 data sets, then maybe 25.

Thoughts anyone? Am I in over my head?

Any idea how I might implement this? I haven't used arrays before?