Crazy issue with string formatting

Question

I am running into an issue that is driving me crazy. I have two FOR loops in my macro that each have a counter to keep track of how many times a certain process was performed. The counters work great and at the end of the loop contain the correct numbers. The next thing I have to do is to format the counts into a five digit number with leading zeros. I have tried this using two different approaches (see below).

cCount = String(5 - Len(cTemp), "0") & cTemp
mCount = String(5 - Len(mTemp), "0") & mTemp

or

cCount = Format(cTemp, "00000")
mCount = Format(mTemp, "00000")

The problem is with the second counter. As I step through it, the first format formula works, but the second line does not, regardless of which version above that I use. Now here is the thing, if, while I am still in the macro, I go and change the name of mCount to anything else, for example mCnt, and then move the macro step back up to reprocess that line, it will correctly format the variable. But it isn't the name, because if I then run the macro again using mCnt, it will do the same thing. I can change it back to mCount and it will work.

All variables are dimmed as Integers. An example of what I am looking for would be if mTemp is 15, then mCount would be 00015. However, mCount is just coming back as 15. cCount is working fine.

The fact that everything is correct and that I can make it work if I pause the macro, change the variable name, and reprocess the line, has got me completely at a loss as to what the issue is.

Sub MakePay()

Dim strFileToOpen As String
Dim payDate, payTab, payCheckTemp, payCheck, payAccTemp As String
Dim payAcc, payAmount, payTotalC, payTotalM As String
Dim savePath As String
Dim payFileNameCLP, payFileNameMF As String
Dim payString1, payString2, payString3, payString4, payString5, payString6 As String
Dim payString7, payString8, payString9 As String
Dim rCnt, i, j, cTemp, cCount, mTemp, mCount As Integer
Dim payTotalMTemp, payAmountTemp, payTotalCTemp As Double


' Set date
payDate = Format(Now(), "yyyymmddhhmmss")
' Ask for check number and format to field length
payCheckTemp = InputBox("Please enter the check number.")
payCheck = payCheckTemp & String(15 - Len(payCheckTemp), " ")
' Create file names and open text files for writing
payFileNameCLP = "CLP_" & payDate & "_01.txt"
payFileNameMF = "MDF_" & payDate & "_01.txt"
savePath = Environ("USERPROFILE") & "\Desktop\"
Open savePath & payFileNameCLP For Output As #1
Open savePath & payFileNameMF For Output As #2

' Build header rows and print them
payString1 = "100"
payString2 = "200          C"
Print #1, payString1
Print #1, payString2
Print #2, payString1
Print #2, payString2

' reset counters for number of claims and total dollar amounts in files
cTemp = 0
mTemp = 0
payTotalCTemp = 0
payTotalMTemp = 0

For i = 1 To Sheets.Count
    ' Process the Clearpoint tab
    If Left(Sheets(i).Name, 3) = "CLE" Then
        Sheets(i).Activate
        rCnt = Cells(Rows.Count, 1).End(xlUp).Row
        For j = 3 To (rCnt - 1)
            ' Read accession # and format it for field length
            payAccTemp = Cells(j, 3).Value
            payAcc = payAccTemp & String(17 - Len(payAccTemp), " ")
            ' Read payment amount, if $0, skip
            payAmountTemp = Format(Cells(j, 5).Value2, "#,###.00")
            If payAmountTemp = "" Then
                GoTo SkipCDL
            End If
            ' Add payment to total Clearpoint payments
            payTotalCTemp = payTotalCTemp + payAmountTemp
            ' Format payment by deleting decimal and then format to field length
            payAmount = Format(payAmountTemp * 100, "0000000;-000000")

            ' Build payment strings and print them
            payString3 = "400" & String(10, " ") & payAcc & payCheck
            payString4 = "450" & String(10, " ") & payAcc & String(150, " ") & payAmount
            payString5 = "500" & String(10, " ") & payAcc & String(73, " ") & payAmount
            Print #1, payString3
            Print #1, payString4
            Print #1, payString5
            ' Increase Clearpoint patient count
            cTemp = cTemp + 1
SkipCDL:
        Next j
    ' Process Medfusion tab
    ElseIf Left(Sheets(i).Name, 3) = "MED" Then
        Sheets(i).Activate
        rCnt = Cells(Rows.Count, 1).End(xlUp).Row
        For j = 3 To (rCnt - 1)
            ' Read accession # and format it for field length
            payAccTemp = Cells(j, 3).Value
            payAcc = payAccTemp & String(17 - Len(payAccTemp), " ")
            ' Read payment amount, if $0, skip
            payAmountTemp = Format(Cells(j, 5).Value2, "#,###.00")
            If payAmountTemp = "" Then
                GoTo SkipMDF
            End If
            ' Add payment to total Medfusion payments
            payTotalMTemp = payTotalMTemp + payAmountTemp
            ' Format payment by deleting decimal and then format to field length
            payAmount = Format(payAmountTemp * 100, "0000000;-000000")

            ' Build payment strings and print them
            payString3 = "400" & String(10, " ") & payAcc & payCheck
            payString4 = "450" & String(10, " ") & payAcc & String(150, " ") & payAmount
            payString5 = "500" & String(10, " ") & payAcc & String(73, " ") & payAmount
            Print #2, payString3
            Print #2, payString4
            Print #2, payString5
            ' Increase Medfusion count
            mTemp = mTemp + 1
SkipMDF:
        Next j
    End If
Next i

' Format patient counter and total payment to field length

cCount = Format(cTemp, "00000")
mCount = Format(mTemp, "00000")
payTotalC = Format(payTotalCTemp * 100, "000000000;-00000000")
payTotalM = Format(payTotalMTemp * 100, "000000000;-00000000")


' Build footer strings and print them
payString6 = "800" & String(26, " ") & "9999" & cCount & String(131, " ") & payTotalC
payString7 = "800" & String(26, " ") & "9999" & mCount & String(131, " ") & payTotalM
payString8 = "900" & String(57, " ") & "099990" & cCount & String(154, " ") & String(2, "0") & payTotalC
payString9 = "900" & String(57, " ") & "099990" & mCount & String(154, " ") & String(2, "0") & payTotalM
Print #1, payString6
Print #2, payString7
Print #1, payString8
Print #2, payString9
' Close all files
Application.DisplayAlerts = False

    Close #1
    Close #2
Application.DisplayAlerts = True

End Sub

Answer 1

The issue is with how the variables are declared.

In VBA/classic vb, all declarations should be on their own line OR have the correct data type specified, otherwise you risk accidentally creating a Variant data type, which can masquerade as any other data type, which the VB engine has rules for determining the type.

See https://msdn.microsoft.com/en-us/library/56ht941f(v=vs.90).aspx

Also, whenever coding in VBA make sure Option Explicit is declared at the top of any new code module. It will save you loads of pain in the future.

Also, you are trying to push String formatting into an Integer, which cannot happen.

So...

   Option  Explicit
    .....

    'Dim i, j as Integer 'BAD i is a variant, j is an integer
    Dim i As Integer
    Dim j As Integer 'GOOD both are Integers
    'OR
    Dim x As Integer, y as Integer 'I believe this will work too

    dim displayI as String
    i = 23
    displayI = Format(i, "00000")

In your code why not just format inline?

payString6 = "800" & String(26, " ") & "9999" & Format(cCount,"00000") & String(131, " ") & payTotalC