CODEWITHSUNDEEP
excelvbaerror-handling
user1328819
user1328819

Reputation: 113

Error Messages Without Line Numbers

I am writing a small program (macro) in Visual Basic for Applications (6.5) and I am using the standard editor which is integrated into Excel 2007.

My problem is that I get error messages without any line number information. I am not referring to my error handling or user defined messages.

Example:

German Error Message '1004'

The error message says that I have an runtime error '1004'. I am not able to click on the 'debug' button, only 'end' and 'help' are available. Thus, I do not see in which line the error occurs. This is very frustrating.

How can I resolve this issue?

Upvotes: 4

Views: 4144

Answers (6)

Walter Rauschenberger
Walter Rauschenberger

Reputation: 101

I have found several clever solutions working without line numbers. The one which was called a godsend when implemented triggered my implementation of it which I described in Straight to the Error Line

Upvotes: 0

Alpha
Alpha

Reputation: 2442

Sometimes this kind of error occurs within the password protected part of the codes - especially within the add-ins - since VBA cannot move its debug cursor there. That's why it does not give you the line number.

Upvotes: 0

Steve Rindsberg
Steve Rindsberg

Reputation: 3528

You can create error handler "templates" in MZTools and very quickly apply them to each sub/function in your project. A template like this will give you a message box when an error is encountered, tell you what module/sub the error's in and the Stop will cause the code to break so you can hit F8 and step on to the line of code after the one that triggered the error.

   On Error GoTo {PROCEDURE_NAME}_ErrorHandler

    {PROCEDURE_BODY}

NormalExit:
    Exit {PROCEDURE_TYPE}
{PROCEDURE_NAME}_ErrorHandler:
    MsgBox "Error " & Err.Number & " (" _ 
    & Err.Description & ") in {PROJECT_NAME}:{MODULE_NAME}:{PROCEDURE_NAME}"
   Stop 
   Resume Next

Upvotes: 0

Steve Mallory
Steve Mallory

Reputation: 4283

You need to manually add line numbers and trap your errors. In the trap, use Erl to get the line number. I don't have Excel on me now to check my syntax, but your code should look something like this:

public sub Test()

on error goto TestError

10  Dim i as Integer
20  Dim j as Integer

30  i = 1
40  j = 0

50  debug.Print i/j

TestExit:
    exit sub
TestError:
    debug.Print Err.Number, Err.Description, Erl
    goto TestExit
end sub

Upvotes: 1

Charles Williams
Charles Williams

Reputation: 23520

One reason for not being able to enter Debug mode is that the VBA is protected - VBA Project Properties - Lock Project for Viewing.

Upvotes: 1

Jon Egerton
Jon Egerton

Reputation: 41539

This error in a VBA macro normally indicates a misuse of the excel VBA functions. You'll not be getting debug info as it'll be thrown from internal office code that isn't accessible to the debugger.

The best way to isolate the line that fails is to put a breakpoint towards the top of the Macro that fails and follow it through line-by-line. You may also be able to find the line of your code in a stack-trace (although I can't recall how much of this is available in VBA).

If you know that there the fail occurs in certain section of code you could add it to your question and let us take a look.

Upvotes: 1

Related Questions