Microsoft KB Archive/115309

{|
 * width="100%"|

-

The information in this article applies to:


 * Microsoft Excel for Windows, version 5.0

-

SYMPTOMS
When you use a Visual Basic application to return the value of a cell on a Microsoft Excel worksheet, using the Value property of the Range object, you receive the following error message if the cell is empty:

OLE Automation Method Did Not Return A Value

CAUSE
This error message is displayed because Microsoft Visual Basic does not understand the value that is returned when you return the value of an empty cell on a Microsoft Excel worksheet.

Note that you do not receive an error message when an empty value is returned in a Microsoft Visual Basic Programming System, Applications Edition, procedure. This is because Visual Basic, Applications Edition, has a direct representation of the Empty variant type. You can also use the IsEmpty function in Visual Basic, Applications Edition, to check for an empty cell value.

WORKAROUND
To avoid receiving this error message when you use a Visual Basic application to return the value of a cell on a Microsoft Excel worksheet, use the On Error statement to check for empty cells on the worksheet as in the following example:

 ' Define xlobj variable as object type Dim xlobj as object ' Set value of xlobj to worksheet object, first worksheet in BOOK1.XLS Set xlobj = GetObject("C:\EXCEL\BOOK1.XLS","Excel.Sheet") ' Dimension variable v as variant type Dim v as Variant ' Dimension variables i and j as integer type Dim i as integer Dim j as integer ' Ignore the error message that appears on blank cells On Error Resume Next For I = 1 to 5 For j = 1 to 5 ' Set variable v equal to value contained in cell v = xlobj.Cells(i,j).Value ' Check for empty cell, vartype of zero indicates empty value If VarType(v) = 0 Then ' Enter commands here for case when cell is empty Else ' Enter commands here for case when cell has something in it. End If     Next Next Set xlobj = Nothing Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Additional query words: 3.00 err msg

Keywords : kbinterop kbprg

Version : WINDOWS:5.0

Platform : WINDOWS

Issue type :
 * }