[RESOLVED] object variable not set

I'm creating an array P & want to dump it's elements into an Excel worksheet :

VB Code:

Set oXLApp = New Excel.Application
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
 
While param < Val(Text3.Text)
param = Val(Text2.Text) + Val(Text4.Text) * m
...
oXLSheet.Cells(m + 1, 1).Value = param
...
m = m+ 1
Wend
...
End Sub

I get a 'object variable or with block variable not set' error on statement :
oXLSheet.Cells(m + 1, 1).Value = param
Why?
I thought I created the oXLSheet object in the statement before the While loop : Set oXLSheet = oXLBook.Worksheets(1)
Does this not carry over into a loop ?!

The "Set" line is right, and it would be fine in the loop.

I cannot see any issues with what you posted, can you show us more code?

Yes - here it is. Vals is a function I wrote. I can share that too if needed; I checked it out earlier & it seems to output the PP array correctly :

VB Code:

Public m, param
Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
Dim oXLChart_Rec, oXLChart_ID, oXLChart_all As Excel.Chart
 
 
 
Private Sub button_Click()
Dim kout, return1 As Integer
Dim PP() As Single
 
Set oXLApp = New Excel.Application
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
 
param = 0
m = 0
While param < Val(Text3.Text)
   param = Val(Text2.Text) + Val(Text4.Text) * m
   RichTextBox1.Find (List1.Text)
   RichTextBox1.UpTo ("=")
   RichTextBox1.UpTo ("1234567890.")
   RichTextBox1.Span ("1234567890.")
   RichTextBox1.SelRTF = param
   RichTextBox1.SaveFile "C:\test.txt", rtfText
   k = 0
   Call DLL_app(kout)
   If Str(k) = 0 Then
   Text1.Text = "app ran OK"
   Else
   Text1.Text = "app problem!"
   End If
   RichTextBox2.LoadFile ("C:\starter.txt")
   Name "C:\starter.txt" As "C:\" & Text5.Text & "_" & Str(param) & ".txt"
   PP = Vals("C:\" & Text5.Text & "_" & Str(param) & ".out", Val(Text6.Text))
 
   oXLSheet.Cells(m + 1, 1).Value = param     '<---error flags this statement
   oXLSheet.Cells(m + 1, 2).Value = PP(1)
   oXLSheet.Cells(m + 1, 3).Value = PP(2)
   oXLSheet.Cells(m + 1, 4).Value = PP(3)
 
   m = m + 1
 
Wend
 
oXLApp.Visible = True
 
Set oXLSheet = Nothing
oXLBook.SaveAs "C:\" & List1.Text & ".xls"
oXLBook.Close SaveChanges:=False
Set oXLBook = Nothing
oXLApp.Quit
Set oXLApp = Nothing
 
return1 = MsgBox("processing finished", vbOKOnly, "alert")
End Sub

Your code is not ideal (see comments below), but for what you have shown all of the Excel code is fine.

Do either Vals or DLL_app work with your Excel objects at all? (either way, it would probably help if we could see the code)


On to the comments:

The first thing I saw was that your variables are not declared properly. It's an easy mistake, but one that can cause problems. Lets take this line as an example:

VB Code:

Dim kout, return1 As Integer

return1 is an Integer, but kout is not - it is a Variant instead. This makes your code slower, use more memory, and more prone to errors. Here's how it should be:

VB Code:

Dim kout as Integer, return1 As Integer

Your variables m and param do not have a data type at all - but they should (I would assume Integer), and your variable k has not even been defined (all variables should be defined, and have a data type).


When you call functions (such a MsgBox) and dont care about the return value, just ignore it - there is no need to create/use a variable. Instead of this:

VB Code:

return1 = MsgBox("processing finished", vbOKOnly, "alert")

use this:

VB Code:

MsgBox "processing finished", vbOKOnly, "alert"

This line: If Str(k) = 0 Then
..has two problems. First of all, k is a number, so converting it to a string (with a space in front of the value) to compare it with a number (0) is pointless. The second issue is that k will always be 0, as that is what you have just set it to.

The DLL does works fine. Thank you for the comments; I've cleaned up the code a bit.

I think I have a guess as to what is wrong. It 's the

VB Code:

PP = Vals("C:\" & Text5.Text & "_" & Str(param) & ".out", Val(Text6.Text))

that's causing the problem.


Here is the Vals function :

VB Code:

Function Vals(File As String, Prob As Integer) As Variant
 
  Set oXLApp = New Excel.Application
  oXLApp.Visible = False
 
  oXLApp.Workbooks.OpenText FileName:=File, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False,      Semicolon:=False, _
        Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
        TrailingMinusNumbers:=True
  
   Set oXLBook = oXLApp.Workbooks(1)
   Set oXLSheet = oXLBook.Worksheets(1)
   .....
   .....
   Set oXLSheet = Nothing            
   oXLBook.Close SaveChanges:=True    
   Set oXLBook = Nothing
   oXLApp.Quit                    
   Set oXLApp = Nothing
  
End Function

After Vals returns, the Excel app has already quit & so it doesn't recognize oXLSheet anymore. What I've done that works is to :
not quit oXLApp inside the Val function. I've also renamed oXLSheet & OXLBook to oXLSheetA & oXLBookA inside the button click subroutine.
This keeps excel open so that the oXLSheetA object is not lost.
I guess that's as good as solution as any ?

Ah... that would definitely cause it!

Your solution is certianly valid, however I would recommend something a little different. Rather than have different variable names, you can use the same ones - but with a tiny (and yet huge) change - instead of having the variables declared at the top of the form, have them declared in each sub where they are used (in both button_Click and Vals).

As it was previously, you were using the same variables - and overwriting them. Even with normal variables this is a bad thing, but with Object variables (Excel.Anything, ADODB.Anything, etc) this can be a nightmare, as you are losing your connection to the object (Excel), but it is still open using memory/CPU/... (check your Task Manager, or just wait until you shut your computer down - Excel will probably ask if you want to save!)

If you declare the variables just where they are used, you dont get any issues with them being overwritten by other code - which is easy to do accidentally as you add more parts to the application. Where variables are declared determines their Scope (where they can be used), and you should always try to keep the Scope as small as possible.