View Full Version : Solved: Help Using VBA arrays.

orcas

11-19-2012, 06:05 AM

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

11-19-2012, 07:00 AM

attach a sample file with data input and desired output

mikerickson

11-19-2012, 08:15 AM

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

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

11-19-2012, 09:35 AM

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.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.