Jump Start to Build Functions using arrays!

[Actuariojf]

I used to be a kind of programmer in Qbasic, I know that the applied logic is “almost” the same, but the way to define the variables in VBA for Excel to build a function is driving me crazy, so I decided to develop a small make-up code in basic as an example for me or jump start in VBA and ask to a good Samaritan to translate it into a working VBA function. Here it goes:

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

 

[Andrew Poulsom]

For a start arr1 is not an array but a Range object. So you cannot refer to it as arr1(I).
You will have to use something like:

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

This syntax of the Cells property counts across then down the Range.

The other syntax is Cells(RowIndex,ColumnIndex) - the arguments speak for themselves.

To find the number of cells (elements) in the Range object arr1 use:

N = arr1.Count

Hope this helps a bit.

 

[Juan Pablo González]

Andrew, you're going to start to hate me, but, you can refer to a range like that, it's part of the Item property.

?Range("A1").Item(1).Address
$A$1
?Range("A1").Item(2).Address
$A$2
?Range("A1")(1).Address
$A$1
?Range("A1")(2).Address
$A$2

 

[Andrew Poulsom]

Juan Pablo,

Not at all! I am happy to be corrected as it improves my knowledge.

We all live and learn!

 

[Juan Pablo González]

Ok, here is the "same" UDF

<pre>Function Test(Arr1 As Range, Arr2 As Range, x As Integer, y As Integer) As Double
Dim N As Integer
Dim Arr3() As Double
Dim Arr4() As Double
Dim i As Integer
Dim j As Integer
N = Arr1.Count
ReDim Arr3(1 To N)
ReDim Arr4(1 To N, 1 To N)

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 = N

For i = 1 To x
For j = y To i
Test = Test + Arr4(i, j) * x * y
Next j
Next i
End Function</pre>Hope this helps you

 

[Juan Pablo González]

On 2002-09-12 07:45, Andrew Poulsom wrote:
Juan Pablo,

Not at all! I am happy to be corrected as it improves my knowledge.

We all live and learn!

Well, I'm glad you think that way ! Giving more on this, with this Sub for example, you can loop through a 2D range very easily:
<pre>Sub TestItem()
Dim Rng As Range
Dim i As Integer

Set Rng = Range("A1:C2")
For i = 1 To Rng.Count
Debug.Print Rng(i).Address
Next i
End Sub</pre> Which would return:
$A$1
$B$1
$C$1
$A$2
$B$2
$C$2

As you can see, Excel starts from left to right, and then up to down.

But, one bad (Or good depending on the Point of view) thing, is that this for example

Debug.Print Rng(8).Address

would work ok ! even though Rng only has 6 cells !

that would give

$B$3

that means that Excel follows the previous "pattern" and takes the 8 cell... weird, huh ?

 

[Andrew Poulsom]

And the same with Cells(8). With this syntax the cells property is not limited to the number of cells in the range.

Personally I would find it difficult to remember where the hell I was looping through a 2D range this way!

So I'll stick with Cells(Row,Column).

 

[Actuariojf]

Thanks a lot! Now I understand!! This is my starting point for a better use of excel!!!.