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

ID: Q115309

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 :

Last Reviewed: October 15, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use.