Issue with arrays

[johnmasvou]

I have two functions.

Function A should return a matrix array (mxm) and at some point it calls
function B which has one agrument, a vector array (1xm). The output of
function B should be a vector array (1xm).

Using (probably) a loop, how can I assign each time the output of B as one
row on the matrix of function A?

Something like A(i,)=B(Zi) would work in other languages but what about in VB?

thx

 

[Joel]

You can pass the variable to a SUB using BYREF


Sub Main()

Dim a(10)
For i = 0 To 9
a(i) = i
Next i
Call arraya(a)

End Sub
Sub arraya(ByRef a As Variant)


Call arrayb(a)


End Sub
Sub arrayb(ByRef arraya As Variant)

arraya(5) = 100

End Sub

 

[johnmasvou]

My end goal is to develop a UDF that builds a certain probabilty distribution
which the user can call directly in the spreadsheet. Hence, I dont believe a
sub procedure is an option.

Could I do sth similar to what you suggest, using function procedures?

If this is not possible, as an alternative, is there any easy way to first
generate m individual vector arrays by runnig the loop for m times (I can do
this part) and then merge them to one mxm matrix (need some help with this
one) ?

thx

 

[Joel]

I put the following on a worksheet

=MAX(myArray())

Then created the following function

Function MyArray() As Variant

MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)

End Function


This worked fine. The worksheet function returned a 9

The problem with VBA is you have to be careful how you dimension arrays and
copy arrays.

Here are some examples of arry assignments. It is best to use variants and
not to hard code the size of the array with a dim MyArray(6). Instead use
Dim MyArray() and then use a REDIM like the code below.

Sub MyArray1()
Dim MyArray2()
Dim MyArray3(10)
Dim MyArray4()
Dim MyArray5()

/MyArray is a variant because it is not delared
MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
MyArray2 = MyArray

'This doesn't work because MyArray3 isn't an array.
'MyArray3 = MyArray

This works because MyArray4 is a variant and delared an Array.
MyArray4 = MyArray

ReDim MyArray5(6)
For i = 0 To 5
MyArray5(i) = i
Next i
End Sub

 

[Dave Peterson]

I'm not quite sure what you're doing, but you may be able to use
application.index() to slice through the matrix:

Option Explicit
Sub testme()
Dim myArr1 As Variant
Dim myArr2 As Variant
Dim myArr3 As Variant

'4x5 matrix
myArr1 = ActiveSheet.Range("a1:e4").Value

'second row of myarr1
myArr2 = Application.Index(myArr1, 2)

'third col of myarr1
myArr3 = Application.Index(myArr1, , 3)

End Sub

But you can loop:

Option Explicit
Sub testme2()

Dim myArr1 As Variant
Dim myArr2 As Variant
Dim rCtr As Long
Dim cCtr As Long

'4x5 matrix
myArr1 = ActiveSheet.Range("a1:e4").Value

'pick off row 2
ReDim myArr2(LBound(myArr1, 2) To UBound(myArr1, 2))
rCtr = 2 'row 2
For cCtr = LBound(myArr1, 2) To UBound(myArr1, 2)
myArr2(cCtr) = myArr1(rCtr, cCtr)
Next cCtr

'pick off column 3
ReDim myArr3(LBound(myArr1, 1) To UBound(myArr1, 1))
cCtr = 3
For rCtr = LBound(myArr1, 1) To UBound(myArr1, 1)
myArr3(rCtr) = myArr1(rCtr, cCtr)
Next rCtr

End Sub

If you're picking up the values from a range, you could do something like:

Option Explicit
Sub testme3()

Dim myRng As Range
Dim myArr2 As Variant
Dim myArr3 As Variant

'4x5 cells
Set myRng = ActiveSheet.Range("a1:e4")

'second row of range
myArr2 = myRng.Rows(2).Value

'third col of range
myArr3 = myRng.Columns(3).Value

End Sub

 

[johnmasvou]

Dave,

Thanks for the tip with the index function.

i found a way to fix my problem

Cheers,