Argument Not Optional

Hi

I am wondering if anyone can help.
I have the following code (this is amended from someone elses code that has now left the company, however, I cannot see where it is going wrong)

All the variables are set as Private Const at the start of the module.

When trying to step through I am getting an Argument Not Optional error.

Can anyone spot if theres anything obvious mistake in the code from just looking at the below? It is erroring on the top line of the Call AddJournalLine line.

Cheers

McC

Private Sub CreateJournalFile()

    Dim lngRow          As Long
    Dim lngLastRow      As Long
    
    On Error GoTo Err_Handler

    mintFileNum = FreeFile
    Open strJournalFile For Output As #mintFileNum
   
    lngLastRow = Sheets(strName).Range("A65536").End(xlUp).Row
          
        For lngRow = 2 To lngLastRow
        
            Call AddJournalLine(Sheets(strName).Cells(lngRow, PC_POSTING_COL_TYPE), Sheets(strName).Cells(lngRow, PC_POSTING_COL_CCY), Sheets(strName).Cells(lngRow, PC_POSTING_COL_TDATE), Sheets(strName).Cells(lngRow, PC_POSTING_COL_SDATE), _
                                Sheets(strName).Cells(lngRow, PC_POSTING_COL_PRICE), Sheets(strName).Cells(lngRow, PC_POSTING_COL_QTY), Sheets(strName).Cells(lngRow, PC_POSTING_COL_ACCRUED), , Sheets(strName).Cells(lngRow, PC_POSTING_COL_NET), _
                                , Sheets(strName).Cells(lngRow, PC_POSTING_COL_GROSS), Sheets(strName).Cells(lngRow, PC_POSTING_COL_BROKER), Sheets(strName).Cells(lngRow, PC_POSTING_COL_TRADEREF), Sheets(strName).Cells(lngRow, PC_POSTING_COL_FUND), _
                                , Sheets(strName).Cells(lngRow, PC_POSTING_COL_SEDOL))
        
                
        Next

    Close #mintFileNum

Exit_Sub:
    Exit Sub
Err_Handler:
    RaiseError "CreateJournalFile", Err.Number, Err.Description
    Resume Exit_Sub

End Sub

Private Sub AddJournalLine(strType As String, strCcy As String, strTDate As String, strSDate As String, dblPrice As Double, dblQty As Double, dblAccrued As Double, dblNet As Double, _
dblGross As Double, strBroker As String, strTRef As String, strFund As String, strSedol As String)
                                
    Dim strLine         As String
    
    On Error GoTo Err_Handler

    'add line to file
    strLine = strType
    strLine = strLine & Space(PC_FILE_FILLER1)
    strLine = strLine & strCcy & strCcy & strCcy
    strLine = strLine & Space(PC_FILE_FILLER2)
    strLine = strLine & Format(strTDate, PC_DATE_FORMAT_JRNL)
    strLine = strLine & Format(strSDate, PC_DATE_FORMAT_JRNL)
    strLine = strLine & Space(PC_FILE_FILLER3)
    strLine = strLine & Abs(Round(dblPrice * 1000000, 2)) & Space(14 - Len(Abs(Round(dblPrice * 1000000, 2))))
    strLine = strLine & Space(PC_FILE_FILLER4)
    strLine = strLine & Abs(Round(dblQty * 1000000, 2)) & Space(19 - Len(Abs(Round(dblQty * 1000000, 2))))
    strLine = strLine & Space(PC_FILE_FILLER5)
    strLine = strLine & Abs(Round(dblAccrued * 100, 0)) & Space(19 - Len(Abs(Round(dblAccrued * 100, 2))))
    strLine = strLine & Abs(Round(dblNet * 100, 2)) & Space(19 - Len(Abs(Round(dblNet * 100, 2))))
    strLine = strLine & Abs(Round(dblGross * 100, 2)) & Space(19 - Len(Abs(Round(dblGross * 100, 2))))
    strLine = strLine & Abs(Round(dblNet * 100, 2)) & Space(19 - Len(Abs(Round(dblNet * 100, 2))))
    strLine = strLine & Space(PC_FILE_FILLER6)
    strLine = strLine & strBroker & Space(20 - Len(strBroker))
    strLine = strLine & Space(PC_FILE_FILLER7)
    strLine = strLine & strTRef & Space(20 - Len(strTRef))
    strLine = strLine & Space(PC_FILE_FILLER8)
    strLine = strLine & strFund & Space(20 - Len(strFund))
    strLine = strLine & Space(PC_FILE_FILLER9)
    strLine = strLine & "03"
    strLine = strLine & Space(PC_FILE_FILLER10)
    strLine = strLine & strSedol & Space(20 - Len(strSedol))
    strLine = strLine & Space(PC_FILE_FILLER11)
    
    Print #mintFileNum, strLine
    
Exit_Sub:
    Exit Sub
Err_Handler:
    RaiseError "AddJournalLine", Err.Number, Err.Description
    Resume Exit_Sub

End Sub

The first thing I would chek is whether the number of vairables in the Call statement is different from the number of variables in the macro being called. I.e.
Call Macro(A,B,C)
Macro(A,B,C,D)
wouldn't work unless delacred as Macro(A,B,C,Optional D)

Don't let that person come back ...

        With Worksheets(strName)
            Call AddJournalLine(.Cells(lngRow, PC_POSTING_COL_TYPE), _
                                .Cells(lngRow, PC_POSTING_COL_CCY), _
                                .Cells(lngRow, PC_POSTING_COL_TDATE), _
                                .Cells(lngRow, PC_POSTING_COL_SDATE), _
                                .Cells(lngRow, PC_POSTING_COL_PRICE), _
                                .Cells(lngRow, PC_POSTING_COL_QTY), _
                                .Cells(lngRow, PC_POSTING_COL_ACCRUED), _
                                .Cells(lngRow, PC_POSTING_COL_NET), _
                                .Cells(lngRow, PC_POSTING_COL_GROSS), _
                                .Cells(lngRow, PC_POSTING_COL_BROKER), _
                                .Cells(lngRow, PC_POSTING_COL_TRADEREF), _
                                .Cells(lngRow, PC_POSTING_COL_FUND), _
                                .Cells(lngRow, PC_POSTING_COL_SEDOL))

        End With

Originally Posted by incjourn

The first thing I would chek is whether the number of vairables in the Call statement is different from the number of variables in the macro being called. I.e.
Call Macro(A,B,C)
Macro(A,B,C,D)
wouldn't work unless delacred as Macro(A,B,C,Optional D)

They always say two sets of eyes are better than one.
Had additional commas within the code.

Cheers for posting.

There does appear to be more arguments than required, e.g

'note the , ,
Cells(lngRow, PC_POSTING_COL_ACCRUED), , .Cell

Check this

Private Sub CreateJournalFile()

    Dim lngRow          As Long
    Dim lngLastRow      As Long
    
    On Error GoTo Err_Handler

    mintFileNum = FreeFile
    Open strJournalFile For Output As #mintFileNum
   
    lngLastRow = Sheets(strName).Range("A65536").End(xlUp).Row
          
        For lngRow = 2 To lngLastRow
        With Sheets(strName)
            Call AddJournalLine(.Cells(lngRow, PC_POSTING_COL_TYPE), .Cells(lngRow, PC_POSTING_COL_CCY), .Cells(lngRow, PC_POSTING_COL_TDATE), .Cells(lngRow, PC_POSTING_COL_SDATE), _
                                .Cells(lngRow, PC_POSTING_COL_PRICE), .Cells(lngRow, PC_POSTING_COL_QTY), .Cells(lngRow, PC_POSTING_COL_ACCRUED), .Cells(lngRow, PC_POSTING_COL_NET), _
                                , .Cells(lngRow, PC_POSTING_COL_GROSS), .Cells(lngRow, PC_POSTING_COL_BROKER), .Cells(lngRow, PC_POSTING_COL_TRADEREF), .Cells(lngRow, PC_POSTING_COL_FUND), _
                                , .Cells(lngRow, PC_POSTING_COL_SEDOL))
        End With
                
        Next

    Close #mintFileNum

Exit_Sub:
    Exit Sub
Err_Handler:
    RaiseError "CreateJournalFile", Err.Number, Err.Description
    Resume Exit_Sub

End Sub