PDA

View Full Version : Solved: Fill an array an easier way



skulakowski
12-16-2005, 03:22 PM
I know the answer is simple but I just don't see it at the moment.

' I want to fill an array with single statement that looks something like

Array(0, 0 to 49) = (1, 2, 3, 4, ...50) 'when I try this, VBA chokes on the "=" sign

'rather than

Array(0,0) = (1)
Array(0,1) = (2)
...
Array(0,49) = (50) 'which works but isn't very elegant

What am I miscoding? Thanks.

malik641
12-16-2005, 03:40 PM
Give this a shot:
Dim myArray(0, 0 To 49) As Variant
Dim i As Long
For i = 0 To 49
myArray(0, i) = (i + 1)
Next i
:thumb

skulakowski
12-16-2005, 04:10 PM
Sorry, bad example on my part because it appeared that I want a tidy sequence of numbers.

My array will actually contain a sequence of fractions [ (0, 0, 0.00119, 0.00202, 0.003, ...) ] that cannot be described with an equation. In some fashion, I must name them all.

BlueCactus
12-16-2005, 04:37 PM
One simple option would be to put them on a sheet and then use:
Dim myArray as Variant
myArray = Sheets(1).Cells(1,1).Resize(10,50)
This would create myArray(1 to 10, 1 to 50) ... note the lower bound. First index is the row, second the column.

Alternatively, wait for xld to post. I know he has some crafty ways of defining arrays like this. Just don't feel like doing the search myself right now.

Bob Phillips
12-16-2005, 04:43 PM
SIngle dimension

Sub LoadArray()
Dim ary

ary = [ {0, 0, 0.00119, 0.00202, 0.003} ]
End Sub


Two dimension


Sub LoadArray()
Dim ary

ary = [ {0,1; 0,2; 0.00119,3; 0.00202,4; 0.003,5} ]
End Sub

malik641
12-16-2005, 05:38 PM
Sorry, bad example on my part because it appeared that I want a tidy sequence of numbers.

My array will actually contain a sequence of fractions [ (0, 0, 0.00119, 0.00202, 0.003, ...) ] that cannot be described with an equation. In some fashion, I must name them all.
Awe :mkay that would have been fun to find a mathematical solution (especially in VBA) for that.

What exactly are the numbers? What do they represent?

skulakowski
12-16-2005, 09:40 PM
The correct syntax is open square brackets, open squiggly brackets (what are these called?!), data separated out by commas, close brackets, close brackets?

Thanks. I never would have figured that out on my own.

For those interested, the sequence is a monthly default expectation. I started with data, fitted some lines, did some averaging and smoothing and rounding, set some upper and lower limits, shifted equations at inflection points, and ended up with the series. So the series resembles the result of an equation but it can't be simply reproduced by an equation any longer. (Yup, I have too much fun at work!)

johnske
12-17-2005, 01:40 AM
SIngle dimension

Sub LoadArray()
Dim ary

ary = [ {0, 0, 0.00119, 0.00202, 0.003} ]
End Sub


Two dimension


Sub LoadArray()
Dim ary

ary = [ {0,1; 0,2; 0.00119,3; 0.00202,4; 0.003,5} ]
End Sub
Hmmm, very interesting, only have to remember the syntax...

More conventionally, you'll find something like this used...Dim MyArray
MyArray = Array(0, 0, 0.00119, 0.00202, 0.003)

Bob Phillips
12-17-2005, 04:11 AM
Hmmm, very interesting, only have to remember the syntax...

More conventionally, you'll find something like this used...Dim MyArray
MyArray = Array(0, 0, 0.00119, 0.00202, 0.003)

The other method comes into its own in two dimensions.

Bob Phillips
12-17-2005, 04:12 AM
The correct syntax is open square brackets, open squiggly brackets (what are these called?!)

Braces

BlueCactus
12-17-2005, 11:30 AM
My question is why are both braces and square brackets needed? What is the role of each?

Cyberdude
12-17-2005, 02:09 PM
Braces?? Hmmmm ... I thought they were curly brackets.
Oh, well.

skulakowski
12-19-2005, 09:20 AM
Correct form is
dim MyArray
MyArray = ARRAY(1,2,3)

One incorrect form (among many others) is
dim MyArray()
MyArray = (1,2,3)

, which probably explains why the compiler choked.:beerchug: