CODEWITHSUNDEEP
vbaexcel
qwerty_face
qwerty_face

Reputation: 231

VBA - Why is this giving me an error?

The following code is the beginning of a sub to remove duplicate lines from a log file, hence the name. However, after testing what I have so far I am unable to understand why this is giving me an error. Here's the code:

Sub cleanUpLogFile()

Dim logFileStr As String

Dim newBook As Workbook
Dim fd1 As FileDialog

MsgBox "Select your log file.", vbInformation, "Important Info"
Set fd1 = Application.FileDialog(msoFileDialogFilePicker)
With fd1
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "*.xl* Files", "*.xl*", 1
    'if user selects a file then
    If .Show Then
        'assign selection to variable
        logFileStr = fd1.SelectedItems.Item(1)
        Else 'display prompt and exit sub
            MsgBox "You didn't select your indexation file. Exiting...", _
                vbCritical, "Important Info"
            Exit Sub
    End If
End With

Set newBook = Workbooks.Open(logFileStr, 0)
newBook.Close (0)
Set newBook = Nothing

MsgBox "finished"

errHandler:
MsgBox "Encountered an error: " & Err.Number & " -> " & Err.Description, _
        vbExclamation, "Error! - from cleanUpLogFile() sub"
Err.Clear
Exit Sub
End Sub

The error message box also doesn't give me much information; err.Number displays as "0" while the corresponding description from err.Description is absent.

Any ideas?

Thanks, QF.

Upvotes: 1

Views: 192

Answers (2)

tpn
tpn

Reputation: 33

You could troubleshoot by initially trying to run something that works. If it doesn't run with an empty With...End With then the error is outside. Otherwise you will know it is inside. Then add a line inside the With...End With at a time and see when the error pops up, then try to modify that line.

Hope this helps.

Upvotes: 0

GTG
GTG

Reputation: 4954

You are missing an Exit Sub statement before your errHandler: label.

A label in VB is really just a bookmark for a position in the code, so if you want your function to exit before reacing the code beneath the label you need to tell it to do so.

In your case, even though there is no error the code beneath the errHandler: label will run and the output really is saying "there was no error".

So, change your code to:

... more code
Set newBook = Nothing

MsgBox "finished"
Exit Sub
errHandler:
MsgBox "Encountered an error: " & Err.Number & " -> " & Err.Description, _
        vbExclamation, "Error! - from cleanUpLogFile() sub"
Err.Clear
Exit Sub
End Sub

Upvotes: 2

Related Questions