ole excel object manipulation causes run-time error '91'

Question

I am maintaining an application that was written in VB6 and makes use of the several OLE controls with Excel.Sheet.8 class objects. Several users are getting the "Run-time error '91': Object variable or With block variable not set" error when they reach a point in code that attempts to manipulate the excel objects. Below are examples of the code that trigger this error. I believe that the issue happens at:

Set oExcel = oleXl.object

Here are the points in the code where it happens:

Private Sub Form_Load()
    Dim i As Integer
    Dim j As Integer
    Dim sTempStringA As String
    Dim sTempStringB As String

    'Set up excel sheet
    centerform Me

    Set oOutGrid = oleXlOutput.object
...


Private Sub Form_Load()

centerform Me
Set oOtherFx = oleXlFx.object
...

Private Sub Form_Load()
Dim iRet As Integer
Dim i As Integer

On Error GoTo Err_Handler

centerform Me

Call InitArray

Me.Caption = "TJUJ | Version " & version & " | Enter Custom Fx"
Set oBook = oleExcel.object
...

Is there a specific situation or environment in which this error would be generated from this line of code OR a way that I can ensure the object will always be accessible at this point in the code? The error only happens occasionally and I can't reproduce it on my developer machine at all. I also do not have access to the machines that it is happening on but it seems to be encountered when there is an instance of the EXCEL.EXE process running.

Answer 1

When you get runtime-error 91, you can bet there's an uninitialized object somewhere in the statement. In other words, you are trying to use the properties or methods of a variable/object with a value of Nothing.

In your examples, oleXl, oleXlFx, and oleExcel are probably Nothing. So when you refer to their .object property, you trigger the RTE.

Somewhere in your code these variables have to be initialized to something. Look for statements like Set oleXl = CreateObject("Excel.Application") or Set oleXl = New Excel.Application

One suggestion; when you find the statements that actually initialize those OLE objects, check to see how the error-handling is coded. If you see things like this:

On Error Resume Next
Set oleXl = CreateObject(...

add a test to make sure the object was instantiated

On Error Resume Next
Set oleXl = CreateObject(...
If oleXl Is Nothing Then
   MsgBox "Hey, my object is Nothing!"
End If