OLE Excel object manipulation causes run-time error '91'
I am maintaining an application that was written in Visual Basic 6.0 and makes use of the several OLE controls with Excel.Sheet.8 class objects. Several users are getting the following error when they reach a point in code that attempts to manipulate the excel objects.
Run-time error '91': Object variable or With block variable not set
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.
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
Microsoft suggests that we can fix error 91 by creating a new registry key. To create a new key follow the steps below.
- Click on the Windows Start menu
- Type Regedit in the search box
- Press Enter
- Locate the following entry in the registry. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Transaction Server
- Now select the transaction server and right click on it
- Select New and then choose Key
- Name the key as Debug
- Right click on the Debug key and choose New
- Now select Key and name the key as RunWithoutContext