Imported Class to Excel, now "Sub not defined"?

SarahHall

Registered User.
Local time
Today, 20:05
Joined
Jan 15, 2011
Messages
32
Hi All,
I have just imported a class with an Error Handling sub in it from an Access Database into Excel. I tweaked the code a little so that it works in Excel, then tried to use that Error Handling sub and now am getting a comile error "Sub or Function not defined". I have checked for silly spelling mistakes, etc.
Any ideas?
 
Impossible to answer without seeing the code.
 
Sorry, I thought it was maybe something pretty critical I was missing.
I have the below procedure in my excel class:

Code:
[FONT=Calibri][SIZE=3]Option Explicit[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]Private myDesc As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Private myNumber As Long[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Private mySource As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Private myMethod As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Private myUser As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Private myTime As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Private Const msgtitle As String = "Generic Title"[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]Public Sub LogErr(method As String, _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]             Optional showmessage As Boolean = False)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  Dim db As DAO.Database[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  Dim sql As String[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]  Set db = OpenDatabase("J:\TheFolder\TheFile.mdb")[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]  '//assign error object values to variables[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  myDesc = Nz(Err.Description, "N/A")[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  myNumber = Nz(Err.Number, 0)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  mySource = Nz(Err.Source, "N/A")[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  myMethod = Nz(method, "N/A")[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  myUser = Nz(Environ("username"), "N/A")[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  myTime = CStr(Format(Now, "yyyy/mm/dd hh:mm:ss"))[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]  On Error GoTo ErrHandle[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]  '//create sql string[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  sql = "INSERT INTO ErrorLog (" & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]      "[ErrNumber], [ErrDescription], [ErrSource], " & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]      "[ErrMethod], [ErrUser], [ErrTime]) VALUES (" & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]      myNumber & ", " & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]      "'" & Replace(myDesc, "'", "''") & "', " & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]      "'" & Replace(mySource, "'", "''") & "', " & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]      "'" & myMethod & "', " & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]      "'" & myUser & "', " & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]      "#" & myTime & "#" & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]      ");"[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]  '//run sql string[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  db.Execute sql[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]  '//generate error message[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  If showmessage Then GenErrMessage[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]SubExit:[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  On Error Resume Next[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  Err.Clear[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  Set db = Nothing[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  Exit Sub[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]ErrHandle:[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]   Resume SubExit[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

Its completely unrecognised in the whole project! I don't understand!

Thanks in advance...
 
You need a reference to the Microsoft DAO Library.
From the Project menu, click References, and then select the Microsoft DAO 3.5 Library check box.

If you don't want to manually add a reference on each machine you use the file, you can use late binding, see http://support.microsoft.com/kb/152400 for detailed instructions.
 
I thought had already added a reference to DAO 3.6, but I've just double checked it and the reference is definitely there. Code still bugs at the compile stage when I try to call the function...
 
Which line of the code is causing trouble? Also the Error handling you have is not really a great one.. you have On Error Resume Next.. for proper Handling errors look into this site..
 
Hi,
Just to be clear, this IS my error handling procedure. My error handling looks something like this:

Code:
On error goto Err_Handle
 
....
procedure code
...
 
Err_Exit:
 
Exit sub
 
Err_Handle:
 
LogErr "Procedure", True
resume Err_Exit

As you can see, I am not concerned with the error handling within my procedure, my procedure IS the error handling. It logs details of the error in a database and then displays an appropriate message to the user.

The point at which the project bugs, is when I try to call the LogErr procedure as part of error handling a procedure elsewhere in the project. If I do not call LogErr then the project compiles fine.

I hope that makes sense.
 
Okay got you.. So I hope this Sub is placed in a Module and not along side of the Form's Code?? Check to make sure that you do not have the Module's name as similar to the name of the Sub..
 
Excel doesn't have the NZ function. Either add a reference to MS Access library, or create your own:

Code:
Public Function NZ(ByVal Value As Variant, Optional ByVal ValueIfNull As Variant) As Variant

' Same as NZ Access function. Returns Empty if the parameter Value is empty or
' null, or returns the value in the parameter ValueIfNull if specified. The
' result of returning Empty is that Empty is treated as either a zero or a null
' string depending on the context.
   
   Dim Result As Variant
   
   If IsNull(Value) Then
      If IsMissing(ValueIfNull) Then
         Result = Empty
      Else
         Result = ValueIfNull
      End If
   Else
      Result = Value
   End If
   
   NZ = Result

End Function
 
Doh yes. I added my own NZ function and also moved the LogErr procedure out of class module and into a regular module as I started to wonder whether it needed to be in a class at all. I think that was what was driving the overall problem as it seems to compile fine now.

Thanks all for help.
 

Users who are viewing this thread

Back
Top Bottom