Excel processing OLE objects causes runtime error '91'
I maintain an application written in Visual Basic 6.0 and uses multiple OLE controls with class objects Excel.Sheet.8
. Some users are getting the following error when they reach a point in their code that tries to manipulate excel objects.
Runtime error '91': object variable or with locked block variable
Below are examples of the code causing this error. I believe the problem is happening at:
Set oExcel = oleXl.object
Here are the points in the code where this 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 will be generated from this line of code, or how can I ensure that the object is always available at this point in the code?
The error only occurs occasionally and I cannot reproduce it on my dev machine at all. I also don't have access to the machines on which this happens, but it looks like they are encountered when there is an instance of the EXCEL.EXE process.
source to share
When you get runtime error 91, you can put an uninitialized object in there somewhere in the statement. In other words, you are trying to use properties or methods of the / object variable with the value Nothing.
In your examples, oleXl, oleXlFx and oleExcel are probably nothing. So when you reference your .object property, you run the RTE.
Somewhere in your code, these variables must be initialized with something. Find expressions like Set oleXl = CreateObject("Excel.Application")
orSet oleXl = New Excel.Application
One sentence; when you find the instructions that actually initialize these OLE objects, check 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 created
On Error Resume Next
Set oleXl = CreateObject(...
If oleXl Is Nothing Then
MsgBox "Hey, my object is Nothing!"
End If
Microsoft suggests fixing error 91 by creating a new registry key. To create a new key, follow these steps.
- Click on the Windows Start Menu
- Enter
Regedit
in the search box - Hit enter
- Find the following entry in the registry.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Transaction Server
- Now select the transaction server and right click it
- Select "New" and then select "Key"
- Name the key like
Debug
- Right click on the Debug button and select New
- Now select "Key" and name it like
RunWithoutContext
source to share