CODEWITHSUNDEEP
arraysexcelworksheetvba
lukieleetronic
lukieleetronic

Reputation: 623

Dumping Excel VBA Array into worksheet not working,

I've got the following code extract which suppose to dump the content of the array back to the worksheet, but it doesn't seems to work and I'm stuck..could someone please help, thanks. 

Dim aggressiveDriving(7000) As Variant

For i = 3000 To 7000
    'simple kinematic equation    
    aggressiveDriving(i) = Math.Sqr((((aggressiveDriving(i - 1) / 3.6) ^ 2) + (2 * aggressive_decel))) * 3.6

Next
'at this stage, when I watch the array, it is populated with the expected values in double.
'now writing back to worksheet

With ActiveWorkbook.Worksheets("Current_Driving")
    'if I replace 'aggressiveDriving with '0' then the dumping works with
    'filling 0s
    .Range(.Cells(2, "F"), .Cells(7002, "F")).value = aggressiveDriving
End With

Upvotes: 1

Views: 266

Answers (2)

user4039065
user4039065

Reputation:

The orientation of the one-dimensional array is currently 7000 'columns' by 1 'row', not 7000 'rows' by 1 'column' when compared to the worksheet. Use the worksheet's TRANSPOSE function to reorient it

With ActiveWorkbook.Worksheets("Current_Driving")
    'if I replace 'aggressiveDriving with '0' then the dumping works with
    'filling 0s
    .Range(.Cells(2, "F"), .Cells(7002, "F")).value = _
        Application.Transpose(aggressiveDriving)
    'I prefer this method of resizing (+1 because it is currently a zero-based index array)
    '.Range("F2").Resize(UBound(aggressiveDriving) + 1, 1) = _
        Application.Transpose(aggressiveDriving)

End With

There are limits to the TRANSPOSE function; typically along the lines of an old XLS worksheet's dimensions.

Upvotes: 2

Paidjo
Paidjo

Reputation: 111

Looping

For i = 0 To 7000
'simple kinematic equation    
aggressiveDriving(i) = Math.Sqr((((aggressiveDriving(i - 1) / 3.6) ^ 2) + (2 * aggressive_decel))) * 3.6 
Next

start from 0 to 7000, then in equation 

aggressiveDriving(i) = Math.Sqr((((aggressiveDriving(i - 1) / 3.6) ^ 2) + (2 * aggressive_decel))) * 3.6

at i = 0, you will access array in index -1

aggressiveDriving(i - 1)

Or might be the problem exist in array size. Array size is 7000, but you try to write from F2:F7002 => 7001 (size)

Upvotes: 0

Related Questions