#### Actuariojf

##### New Member

- Joined
- Aug 29, 2002

- Messages
- 15

Let’s say, I have two arrays and two single numbers as inputs and I would like to build a function in VBA with this name;

Test( arr1 as range, arr2 as range, x as Integer, y as Integer)

‘Body

End Function

Assuming that I need to do a number of calculations with these two arrays and I must create two intermediate arrays to do so, and the function just need to return one value named Test. The body as I see it in Qbasic is:

‘N is the number of elements in arr1 and arr2

For I = 1 to N

Arr3(I) = ( arr1(I) + x )^I

Next I

For I =1 to N

For j =1 to N

Arr4(I,j) = Arr3(j) * (arr2(j) + I)^J

Next j

Next I

If x > N then x = N

If y > N then y = 1

For k=1 to x

For p=y to k

Test = Test + Arr4(k,p)*x*y

Next p

Next k

The actual code is not that easy. This made-up function would be just a way for me to understand how to deal with arrays inside a VBA function. So, anybody who can help me to do this will be very much appreciated. If I can get from the Excel Gurus Community different approaches to do the same, but maintaining the calculation body in some way “unchanged” will be great.

Thanks a lot!

ActuaryJF