Fixing Medical Claim Files through Text File Read/Write

With thanks to @Mat'sMug and @Comintern for their encouragement, here is a program I wrote to help my team fix medical claim files.

The goal of this program is to make the process of emergency changes to medical claim files as human-proof and rapid as possible, and deliver all the corrected files the analysts need with very easy steps. For this program, the steps are:

• Start the program

• Change the 10 options for file correction on the settings sheet to "On" or "Off" as appropriate.

• Start the macro

• Select the files to fix

In addition to a fixed text file, the program also produces a sheet for each file with the read code in Column A and the write code in Column B, with differences highlighted for easy filtering and comparison.

Procedure

```
Option Explicit

Sub M1837Fixes()
'
' M1FixNew277URefFJ Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'

'--- Fixed Purpose Variables ---
Dim strText As String 'The entire original 837 as one string
Dim strLines() As String 'The original 837 as an array where each line is an array item
Dim strLines2() As String 'The revised 837 as an array where each line is an array item
Dim strLinesTransposed() As String 'Arrays of more than 65536 rows are truncated with #N/As
Dim strLines2Transposed() As String 'Arrays of more than 65536 rows are truncated with #N/As
Dim strText2 As String 'The entire revised 837 as one string
Dim strNewLineCharacter As String
Dim dictCorrectionOptions As Object
Dim UserSettings As classUserSettings
Dim strFileName As String 'Currently ICD9 --> ICD10 Conversion and 2

'HL Segment Numbering
Dim lngHLCount As Long
Dim lngLastHLBilling As Long

'Claim Removal
Dim boolHLSeenSinceLastClm As Boolean
Dim boolWriteClaim As Boolean
Dim rngClaimRemoval As Range
Dim lngClaimStartLoop As Long

'Ref*F8 Update
Dim dictRefF8LookupClaims As Object
Dim rngRefF8LookupClaims As Range

'Invalid Zip Code
Dim rngZipCodeLookupCities As Range

'ICD9 --> ICD10 Conversion
Dim dictICDDiags As Object
Dim dictICDProcs As Obje

I'll kick us off with some general observations -

You probably haven't counted, but you have a procedure that's 696 lines long.

Your macro is too long! You can probably extract some functions or other procedures out and refactor the code to utilize functions instead of repeating code, but also to break up the code. Like you mentioned, you were thinking in "code blocks" and whatnot - there's no need to put yourself through that!

In terms of how long the code is and how difficult it may be to maintain, I see a lot of comments. Comments - "code tell you how, comments tell you why". The code should speak for itself, if it needs a comment, it might need to be made more clear. If not, the comment should describe why you're doing something rather than how you're doing it. Here are a few reasons to avoid comments all together.

For every Integer you dimensioned, change it to a Long - Integers - integers are obsolete. According to msdn VBA silently converts all integers to long.

Variables

Let me take a moment to tell you great job declaring all of your variables. I mean it.

And great job sticking to Standard VBA naming conventions.

But, this Hungarian notation isn't adding anything to the code. With variable names - give your variables meaningful names.

Dim strText

I know that Text is a string - you don't need to tell me that. What you do need to tell me is what the heck text it is. Is it a search string? A temporary string for swapping? I don't know, the first time I encounter it is here

strText = Space$(LOF(Int1))

Which, as you can see, the other names would require me to track back all the way up to their definitions before I could take a guess at what text we're talking about.

'--- Reusable Variables ---
Dim Long1 As Long
Dim Long2 As Long
Dim Loop1 As Long
Dim Loop2 As Long
Dim Loop3 As Long
Dim Variant1 As Variant
Dim Int1 As Integer 
Dim Sheet1 As Worksheet
Dim Range1 As Range
Dim Cell1 As Range
Dim String1 As String
Dim Bool1 As Boolean

I see, you tried naming them as generically as you could so you could reuse them, but that only hurts you down the line. Right,

For Loop1 = LBound(Variant1) To UBound(Variant1)

Without that name, I know you're doing a loop by the For and I also know it's going to be Long You could use something descriptive here. I'd give an example, but I'd have to go figure out what Variant1 is. I guess for example, it could be variant1Index, but only after you tell me what Variant1 is; see the vicious cycle you'll run into? You want to make it easy to jump back in and tweak it. Right now, say 7 months from now, you'll come back to this and say "wait, what's in that variant" whereas if you called it something like patientIDs or even patientArray you'd know what it is.

Similar to

Bool1 = False

I mean, I know it's boolean and it will be 1 or 0, but what are we possibly checking here? No idea.

And I'm not being mean here, I promise, but this line of code -

strDiagProcBeingFixed = Mid(strLines2(Loop2), Long1 + 1, Long2 - Long1 - 1)

it's my favorite line of code ever. I couldn't even begin to guess what's happening in that Mid - it's incredible.

Arrows

Arrow code is something I see you're struggling with here - we all have!

GOTO

If dictCorrectionOptions("Claim Removal - Have Wanted Claims").strOnOffSwitch = "On" And dictCorrectionOptions("Claim Removal - Have Unwanted Claims").strOnOffSwitch = "On" Then
    MsgBox "Both " & """" & "Claim Removal" & """" & " correction options on the " & """" & "Correction Type Options" & """" & " are On. Please turn at least one Off."
    GoTo Cancel
End If

Watch out!

Handle those errors. In this case your Cancel label is is 579 lines away from your GoTo. You catch my drift.

Speaking of that piece of code,

MsgBox "Both " & """" & "Claim Removal" & """" & " correction options on the " & """" & "Correction Type Options" & """" & " are On. Please turn at least one Off."

What's going on here? To me, that looks like a case for a constant:

Const OPTION_TURN_OFF_MESSAGE As String = "Both " & """" & "Claim Removal" & """" & " correction options on the " & """" & "Correction Type Options" & """" & " are On. Please turn at least one Off."

Now all you need to go is Msgbox OPTION_TURN_OFF_MESSAGE and since it's a constant, you can change it at the constant declaration instead of in the code. And if you reuse it - bonus!

You can see it would be more useful in cases like this-

strLines2(Loop2) = "NM1*PR*2*MMEDSCMS*****PI*H0022"

Dim Sheet1 As Worksheet

Oh, so worksheets have a CodeName property - View Properties window (F4) and the (Name) field (the one at the top) can be used as the worksheet name. This way you can avoid Sheets("mySheet") and instead just use mySheet. In this case, Sheet1 is a default codename, so when I see that, I'm certainly not expecting it to be the object of a For loop!

StackExchange Code Review Q#156651, answer score: 4