Solved: Help Using VBA arrays.

[orcas]

Hello,

I'm trying to find some help using VBA arrays to create a function.

I want to create a function called myarray that squares the value of my selected data which is a matrix without fixed dimension. I then want to be able to return a result with my squared matrix.

Here's what i have done so far, any help will be much appreciated.


Function myarray(data As Range) As Variant
Dim i As Integer, j As Integer, cCol As Integer, rRow As Integer, jCole() As Double
cCol = myarray.Columns.Count
rRow = myarray.Rows.Count
ReDim jCole(1 To rRow, 1 To cCol)

For i = 1 To rRow
For j = 1 To cCol
jCole(i, j) = CDbl(data(i, j).Value) ^ 2

Next j
Next i

myarray = jCole






End Function

[patel]

attach a sample file with data input and desired output

[mikerickson]

I'd avoid a UDF all together and just put a formula like

=A1*A1 in a cell and drag it to the appropriate size.

But it sounds like the myArray UDF is just an intermediate step to some other goal. IF that is the case, what is that eventual goal.

Oh, I just noticed in the UDF, the size is being determined by the wrong variables.

Also, since the last row in a spreadsheet is larger than the largest data type Integer, its best to dimension variables as type Long.

Function myarray(data As Range) As Variant
Dim i As Long, j As Long, cCol As Long, rRow As Long, jCole() As Double

cCol = data.Columns.Count : rem <<<<<
rRow = data.Rows.Count

ReDim jCole(1 To rRow, 1 To cCol)

For i = 1 To rRow
For j = 1 To cCol
jCole(i, j) = CDbl(data.Cells(i, j).Value) ^ 2
Next j
Next i

myarray = jCole

End Function

[snb]

3 methods:

1 using an array
2 using ranges


Sub M_snb()
Dim sn(3, 4)

For j = 0 To (UBound(sn) + 1) * (UBound(sn, 2) + 1) - 1
sn(j \ (UBound(sn, 2) + 1), j Mod (UBound(sn, 2) + 1)) = j
Next

Cells(1).Resize(UBound(sn) + 1, UBound(sn, 2) + 1) = square(sn)
End Sub

Sub M_snb_002()
Cells(1).CurrentRegion.Offset(, Cells(1).CurrentRegion.Columns.Count + 4) = square(Cells(1).CurrentRegion.Value)
End Sub

Sub M_snb_003()
[a20:E23] = [index((A1:E4)*(A1:E4),)]
End Sub

Function square(sp)
x = LBound(sp)
y = 1 - x

For j = LBound(sp) To (UBound(sp) + y) * (UBound(sp, 2) + y) - y
sp((j - x) \ (UBound(sp, 2) + y) + x, (j - x) Mod (UBound(sp, 2) + y) + x) = sp((j - x) \ (UBound(sp, 2) + y) + x, (j - x) Mod (UBound(sp, 2) + y) + x) ^ 2
Next

square = sp
End Function

[orcas]

Thank you all very much for such swift and helpful response.

mickerickson, your modification works well for what I am try to achieve, thank you!!!!!

Snb, thank you for providing the routines, you wouldn't believe how excited i am to have something that is doing what i needed it to do.