I know the answer is simple but I just don't see it at the moment.
[VBA] ' 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[/VBA]
What am I miscoding? Thanks.
Give this a shot:
[vba]Dim myArray(0, 0 To 49) As Variant
Dim i As Long
For i = 0 To 49
myArray(0, i) = (i + 1)
Next i[/vba]
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
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.
One simple option would be to put them on a sheet and then use:
[vba]Dim myArray as Variant
myArray = Sheets(1).Cells(1,1).Resize(10,50)[/vba]
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.
SIngle dimension
[VBA]Sub LoadArray()
Dim ary
ary = [ {0, 0, 0.00119, 0.00202, 0.003} ]
End Sub
[/VBA]
Two dimension
[VBA]
Sub LoadArray()
Dim ary
ary = [ {0,1; 0,2; 0.00119,3; 0.00202,4; 0.003,5} ]
End Sub
[/VBA]
AweOriginally Posted by skulakowski
that would have been fun to find a mathematical solution (especially in VBA) for that.
What exactly are the numbers? What do they represent?
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
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!)
Hmmm, very interesting, only have to remember the syntax...
More conventionally, you'll find something like this used...[vba]Dim MyArray
MyArray = Array(0, 0, 0.00119, 0.00202, 0.003)[/vba]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
The other method comes into its own in two dimensions.
Braces
My question is why are both braces and square brackets needed? What is the role of each?
Braces?? Hmmmm ... I thought they were curly brackets.
Oh, well.
Correct form is
[VBA]dim MyArray
MyArray = ARRAY(1,2,3) [/VBA]
One incorrect form (among many others) is
[VBA]dim MyArray()
MyArray = (1,2,3) [/VBA]
, which probably explains why the compiler choked.
Posting Permissions
Reply With Quote
