Jump Start to Build Functions using arrays!

Actuariojf

New Member
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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.

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

Juan Pablo,

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

We all live and learn!

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

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 ?

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

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

Replies
4
Views
419
Replies
24
Views
1K
Replies
1
Views
214
Replies
3
Views
222
Replies
13
Views
838

Threads
1,221,312
Messages
6,159,183
Members
451,543
Latest member
cesymcox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back