Storing Cell values from Excel Sheet in an Array in VB.NET

[MKP]

When i am trying to store the values from excel sheets cells in an array (VB.NET) an error is shown.

 

Can you please comment such that the program runs successfully.

 

Thanx in Advance.

 

-------------------------------------------------------------------------

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click 
Dim Cell1, Cell2, Distance As Double 
Dim XC(), YC(), Dist() As Double 
Dim Row As Integer 

xlsheet = xlBook.ActiveSheet() 

'For reading the cells (coordinates) from the excel sheet 
'and calculating the distance ... 

'This is without using Arrays and it is giving the 
'Distance successfully... 

Cell1 = xlsheet.Cells(StRow, 6).value 
Cell2 = xlsheet.Cells(StRow, 7).value 
distance = Math.Sqrt(Cell1 * Cell1 + Cell2 * Cell2) 
Msgbox(Distance) 

'Now i run a counter and read 10 cells from excel and storing 
'in an array and then calculating distance and 
'also storing that in an array... (It gives an error) 

For row = 0 to 9 

XC(Row) = xlsheet.Cells(Row, 6).value 

'The error is: Exception from HRESULT: 0x800A03EC 

YC(Row) = xlsheet.Cells(Row, 7).value 

Dist(Row) = Math.Sqrt(XC(Row) * XC(Row) + YC(Row) * YC(Row)) 

MsgBox(Dist(Row)) 

 

Next

 

End Sub

-----------------------------------------------------------------------------

Edited February 15, 2003 by divil

[Derek Stone]

One of the largest problems I see is the lack of array redimensioning.

 

YC(Row) = xlsheet.Cells(Row, 7).value

 

In the above, you're making an assignment to an array element that doesn't exist. You'll need to use Visual Basic's ReDim statement to add (or remove) elements to and from an undimensioned array.

 

You're also assuming that the Value property of a cell object will return data in the double format. Make to use Double.Parse() instead of assuming that .NET can make that leap.