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

[SarahHall]

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?

 

[moishy]

Impossible to answer without seeing the code.

 

[SarahHall]

Sorry, I thought it was maybe something pretty critical I was missing.
I have the below procedure in my excel class:

[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...

 

[moishy]

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.

 

[SarahHall]

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...

 

[pr2-eugin]

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..

 

[SarahHall]

Hi,
Just to be clear, this IS my error handling procedure. My error handling looks something like this:

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.

 

[pr2-eugin]

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..

 

[moishy]

Excel doesn't have the NZ function. Either add a reference to MS Access library, or create your own:

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

 

[SarahHall]

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.

 

[moishy]

No problemo.