Receiving Error in Macro Function

Within my function ISODateToExcel I am receiving a error for some reason saying "Argument not optional" for the line below:

 theDate = DateSerial(Left(DTString, 5), Mid(DTString, 7, 2), Mid(DTString, , 2))

I've placed the code below as well.

Sub Select_File_Or_Files_Mac()
    Dim MyPath As String
    Dim MyScript As String
    Dim MyFiles As String
    Dim MySplit As Variant
    Dim N As Long
    Dim Fname As String
    Dim mybook As Workbook
    Dim filePath As Variant
    Dim dataRange As Range 'Range in the import sheet to get data from (excludes header row)
    Dim destRange As Range 'Range in the destination sheet to write to (end of usedrange.rows +1)
    
    'On Error Resume Next
    MyPath = MacScript("return (path to documents folder) as String")
    MyScript = _
    "set applescript's text item delimiters to "","" " & vbNewLine & _
               "set theFiles to (choose file of type " & _
             " {""com.microsoft.excel.xls"",""public.comma-separated-values-text"",""org.openxmlformats.spreadsheetml.sheet""}" & _
               "with prompt ""Please select a file or files"" default location alias """ & _
               MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
               "set applescript's text item delimiters to """" " & vbNewLine & _
               "return theFiles"

    MyFiles = MacScript(MyScript)
    On Error GoTo 0
   
   If MyFiles <> "" Then
        MySplit = Split(MyFiles, ",")
            Set destRange = ThisWorkbook.Sheets("Import").UsedRange
            'Clear content of Current sheet apart from headers
            destRange.Offset(1).Clear

        For Each filePath In MySplit
            Set mybook = Application.Workbooks.Open(filePath, , True, , , , , , , , , , False) 'open read-only. Don't add to MRU
            testval = mybook.Sheets(1).Cells(1, 2).Value 'Check the value of cell A2 to determine the version of the import sheet
            Debug.Print testval
            Set dataRange = mybook.Sheets(1).UsedRange.Offset(1)
            Set dataRange = dataRange.Resize(dataRange.Rows.Count - 1)
            Debug.Print dataRange.Address
            Set destRange = ThisWorkbook.Sheets("Import").UsedRange
            'Clear content of Current sheet apart from headers
'            If testval = "SR Number" Or testval = "ID" Then
'                destRange.Offset(1).Clear
'                Set destRange = destRange.Resize(1, 1)
'            End If
            If testval = "Subject" Or testval = "Problem Summary" Then
                destRange.Offset(2).Clear
                Set destRange = destRange.Resize(2, 2)
            End If
            Set destRange = destRange.Offset(destRange.Rows.Count).Resize(dataRange.Rows.Count)
            Debug.Print destRange.Address
            If testval = "Ticket Status" Then 'ge-ticket-history-view-2015-03-31' (Zendesk file type)
                destRange.Columns(1).Value = dataRange.Columns(1).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(3).Value 'Subject
                destRange.Columns(3).Value = dataRange.Columns(4).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(6).Value 'Assignee
                destRange.Columns(5).Value = dataRange.Columns(2).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(5).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(30).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(7).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(8).Value 'Request Date
                destRange.Columns(10).Value = dataRange.Columns(9).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(10).Value 'Channel
                destRange.Columns(12).Value = Now()
            ElseIf testval = "Problem Summary" Then 'SEARCH-SR-PRODUCT-GROUPS (EMC Support file type)
                destRange.Columns(1).Value = dataRange.Columns(1).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(2).Value 'Problem Summary
                destRange.Columns(3).Value = dataRange.Columns(3).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(4).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(5).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(7).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(9).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(8).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(10).Value 'Create Date
                destRange.Columns(10).Value = dataRange.Columns(11).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(27).Value 'Creation Channel
                For Each c In destRange.Columns(8).Cells
                    c.Value = ISODateToExcel(c.Value)
                Next
                destRange.Columns(12).Value = Now()
            ElseIf testval = "SR#" Then 'US Bank (Q Tool type)
                destRange.Columns(1).Value = dataRange.Columns(2).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(10).Value 'Subject
                destRange.Columns(3).Value = dataRange.Columns(6).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(14).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(13).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(8).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(30).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(4).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(16).Value 'Creation Date
                destRange.Columns(10).Value = dataRange.Columns(17).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(22).Value 'Creation Method
                destRange.Columns(12).Value = Now()
          ElseIf testval = "Create Date" Then 'Broadridge (EMC Service Center type)
                destRange.Columns(1).Value = dataRange.Columns(1).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(9).Value 'Problem Summary
                destRange.Columns(3).Value = dataRange.Columns(8).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(13).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(14).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(4).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(5).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(7).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(2).Value 'Creation Date
                destRange.Columns(10).Value = dataRange.Columns(15).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(22).Value 'Creation Method
                destRange.Columns(12).Value = Now()
       ElseIf testval = "Read?" Then 'Morgan Stanley (EMC Service Center type)
                destRange.Columns(1).Value = dataRange.Columns(3).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(4).Value 'Problem Summary
                destRange.Columns(3).Value = dataRange.Columns(5).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(21).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(6).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(8).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(10).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(7).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(11).Value 'Creation Date
                destRange.Columns(10).Value = dataRange.Columns(12).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(22).Value 'Creation Method
                destRange.Columns(12).Value = Now()
            ElseIf testval = "" Then 'Morgan Stanley (VMware type)
                destRange.Columns(1).Value = dataRange.Columns(2).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(3).Value 'Subject
                destRange.Columns(3).Value = dataRange.Columns(6).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(8).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(5).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(4).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(2).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(10).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(9).Value 'Creation Date
                destRange.Columns(10).Value = dataRange.Columns(16).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(24).Value 'Creation Method
                destRange.Columns(12).Value = Now()
            End If
            mybook.Close
        Next
   End If
End Sub

Function ISODateToExcel(DTString) As Date
    theDate = DateSerial(Left(DTString, 5), Mid(DTString, 7, 2), Mid(DTString, , 2))
    theTime = TimeSerial(Mid(DTString, 12, 2), Mid(DTString, 15, 2), Mid(DTString, 18, 2))
    ISODateToExcel = theDate + theTime
End Function

Open in new window

AckeemKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
You've missed out the starting position in the third Mid function
0
GrahamSkanRetiredCommented:
Sorry. The first is a Left, so it's the second Mid.
0
AckeemKAuthor Commented:
I've added a line to each of the if statements to include a column labeled 'Product ID'. Not sure how to correct the starting position now.
0
GrahamSkanRetiredCommented:
In this function,
Function ISODateToExcel(DTString) As Date
    theDate = DateSerial(Left(DTString, 5), Mid(DTString, 7, 2), Mid(DTString, , 2))
    theTime = TimeSerial(Mid(DTString, 12, 2), Mid(DTString, 15, 2), Mid(DTString, 18, 2))
    ISODateToExcel = theDate + theTime
End Functio

Open in new window

the second parameter in the function:
Mid(DTString, , 2)

Open in new window

is not specified.
The Mid function takes the source string as the first parameter, the starting position within that string as the second and the number of characters to be returned as the third parameter. You can omit the third parameter, in which case the function returns the string from the starting position the the end, but you can't omit the second parameter as we don't know where to start.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.