Excel VBA simple array problem

[westma]

I am using the following code in a macro to simply write data to an array and then output the results to a worksheet.

Sub test()

Dim Arr(0 To 5) As Double, row As Integer
Dim i As Double
row = 1

For i = 0 To 5 Step 0.5
Arr(i) = i
Next i

For i = 0 To 5 Step 0.5
Cells(row, 1).Value = Arr(i)
row = row + 1
Next i
End Sub

The array seems to take in the data correctly, but instead of outputting 0, .5, 1, 1.5, etc., the result is:
0.5
0.5
1
2.5
2.5
2.5
3
4.5
4.5
4.5
5

Can anyone tell me why this is happening? Thank you so much!

 

[CautionMP]

westma,
Array ordinals are integer values (whole numbers). Since you are stepping 0.5 VBA is rounding this to a whole number when dealing with items in the array.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[firefytr]

I think we need to make the distinction that Arrays are only houses (to the dimension we specify) and you can store almost anything you want in that house. The problem is the Step.

-----------
Regards,
Zack Barresse

 

[xlbo]

you may also apply an array directly to a range:

Range(cells(1,1),cells(ItemsInArray,1)) = theArray

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Zathras]

Geoff, I think that requires a two-dimension array.

westma, try this:

Sub FillHalfStepValues()
Dim arr(12, 1)
Dim i As Integer
  For i = 0 To 11
    arr(i, 0) = i / 2
  Next i
  Range(Cells(1, 1), Cells(12, 1)) = arr
End Sub

 

[xlbo]

apologies - yes - 2d array is required for direct output to a range

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[westma]

Thanks all for the feedback!

 

[firefytr]

No, you don't need a two-dimension array.. one will suffice.

    Dim theArray(1 To 10), ItemsInTheArray As Long
    For ItemsInTheArray = 1 To UBound(theArray)
        theArray(ItemsInTheArray) = ItemsInTheArray / 2
    Next
    Range(Cells(1, 2), Cells(1, ItemsInTheArray)).Value = theArray

.. just go the other way (if you're using the default array size/dimension).

-----------
Regards,
Zack Barresse

 

[Zathras]

Zack, very interesting. It seems to be able to work with one-dimension arrays when going horizontally, but not vertically. (Never tried going horizontally before.)

At least, I can't make it go vertical. Can you provide sample code to populate a vertical range with a one-dimension array?

 

[firefytr]

Think of arrays like a cell. They are more physically two-dimensional, but you don't always have to specify it. It's like the Offset method, you specify the row and can leave out the column, yet it will still work (as it assumes zero). The same is with the array. You don't necessarily have to specify a row, as it will assume it's all on the same row (it's backwards from the example of the Offset, just by design). Some people get into trouble (which I've gotten myself into as well) without making that distinction, or just plain forgetting. Kinda like when somebody uses Range("A1") and doesn't specify the sheet. You shouldn't, but that doesn't mean you can't. ;-)

-----------
Regards,
Zack Barresse