Array variables

[xavi garriga]

Dears;

I'm making a macro using arrays for first time, and in a sub procedure I'd
like to create a multidimensional array with different types of variables.
One column should have a string of characters and the rest of the columns
should have numeric values. How can I define this array?

When this array is created, how can I call it in a function?

Please, if I haven't clarified enough the question, let me know...

Thanks for your help!

 

[Jim Cone]

The xl97 help file (for instance) has several topics dealing with arrays.
Suggest you start your quest there. In help search for...

Using Arrays
Declaring Arrays
Array Function
ReDim Statement
Variant Data Type
Writing a Function Procedure
--
Jim Cone
Portland, Oregon USA



"xavi garriga"
<[email protected]>
wrote in message
Dears;
I'm making a macro using arrays for first time, and in a sub procedure I'd
like to create a multidimensional array with different types of variables.
One column should have a string of characters and the rest of the columns
should have numeric values. How can I define this array?

When this array is created, how can I call it in a function?

Please, if I haven't clarified enough the question, let me know...

Thanks for your help!

 

[Tom Hutchins]

You might want to define a structure, then declare an array variable of that
structure type. Here is a simple example:

'Define a structure to hold the data
Type NewType
Descr As String
Dol_Val As Double
Exp_Pct As Double
End Type

'Declare an array of NewType
Dim TestData() As NewType

Sub AAAAA()
Dim x As Integer
'Load the array
For x = 1 To 5
ReDim Preserve TestData(x)
TestData(x).Descr = ActiveSheet.Cells(x + 1, 1).Value
TestData(x).Dol_Val = ActiveSheet.Cells(x + 1, 2).Value
TestData(x).Exp_Pct = ActiveSheet.Cells(x + 1, 3).Value
Next x
'Cycle through the array
For x = 1 To UBound(TestData)
MsgBox TestData(x).Descr & " , " & TestData(x).Dol_Val
Next x
End Sub

Hope this helps,

Hutch

 

[Dave Peterson]

I'd use something like:

Dim myArr(1 to 10, 1 to 5) as variant
(10 rows by 5 columns)

But if I were picking up the values from a range on a worksheet, I'd use:

Dim myRng as range
dim myArr as variant

with worksheets("Somesheetnamehere")
set myrng = .range("a1:e" & .cells(.rows.count,"A").end(xlup).row)
end with

myArr = myrng.value

It would have as many rows as I found in column A and 5 columns (A:E).