XL5 Err Msg: "OLE Automation Method Did Not Return a Value"

• 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