Parsing excel cells containing line feed characters

This is a direct follow up to Parsing cells containing Line Feed Characters.

Link to sanitized xls on dropbox if test data is needed

Essentially the reports I work with aren't bad -

The issue is the way it exports to excel -

With the problem being that these cells are filled with LF characters breaking apart the data entries in the cells (usually a listing of employees in format empID / emp name. There's really no rhyme or reason as to where it places the LFs - sometimes there are three in a row.

A lot of the time for analysis I need to use this data but first I need each person to have their own data (the reports get a lot bigger).

I implemented most changes in the answers from last time, but I've never worked with Enum before or custom error handling. Also, this was initially developed in 2010, but this time I wrote it in 2016, if that matters.

Code

All one module. Two procedures - parse into columns and parse into rows.

```
Private Enum ParseError
InputRangeIsNothing = vbObjectError + 42
MultipleColumnsSelected = vbObjectError + 43
ProcessCancelled = vbObjectError + 44
NoOverwrite = vbObjectError + 45
NoData = vbObjectError + 46
End Enum

Public Sub ParseIntoColumns()
'Parse column to the right (text to columns)
On Error GoTo ErrHandler
Dim confirmOverwrite As String
confirmOverwrite = MsgBox("Do you want to overwrite all data to the right of your selection?", vbYesNo)
If confirmOverwrite = vbNo Then Err.Raise ParseError.NoOverwrite

Dim lastRow As Long
lastRow = 1

Dim workingRange As Range
Set workingRange = UserSelectRange(lastRow)
If workingRange Is Nothing Then Err.Raise ParseError.InputRangeIsNothing
Dim workingSheet As Worksheet
Set workingSheet = workingRange.Parent
Dim workingColumn As Long
workingColumn = workingRange.Column

Application.ScreenUpdating = False
Application.DisplayAlerts = False
workingRange.TextToColumns _
Destination:=workingRange, _

Your error-handling is going to cause more errors

The relevant principle here is Single Point of Exit.

In essence, there should only ever be one place where execution of any method stops.

Your problem is here:

Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    Select Case Err.Number
        Case ParseError.InputRangeIsNothing
            MsgBox "Process cancelled: You have not selected a range.", vbExclamation
        Case ParseError.MultipleColumnsSelected
            MsgBox "Process cancelled: You may not select more than 1 column at a time", vbExclamation
        Case ParseError.ProcessCancelled
            MsgBox "Process cancelled", vbExclamation
        Case ParseError.NoOverwrite
            MsgBox "Process cancelled: Please alter your data structure to allow overwriting cells to the right of your selection.", vbExclamation
        Case ParseError.NoData
            MsgBox "Process cancelled: your selection does not have data to parse", vbExclamation
        Case Else
            MsgBox "An error has occured: " & Err.Number & "- " & Err.Description, vbCritical
    End Select
End Sub

If your sub runs without errors, these operations will happen:

Application.CutCopyMode = False
    Application.ScreenUpdating = True

But, if you goto ErrHandler, then you will get a messagebox, and then the Sub will exit without running those lines.

Now, here it's only CutCopyMode that will persist. Not the end of the world. But it could easily have been EnableEvents or Calculation.

Personally, I prefer the following Structure for these situations:

Sub DoThing()

        On Error Goto CleanFail

        [Code]

        [Code]

        [Code]

        [Code]

CleanExit:

        [Clean Up]
        Exit Sub

CleanFail:

        [Error Handling]
        [Error Handling]
        Resume CleanExit

    End Sub

This way, we can always guarantee that the code will exit the procedure via CleanExit and so any clean-up code we put there will always be called.

StackExchange Code Review Q#137854, answer score: 3