Compile Error on Macro

Hi Guys, I have been running this macro for months but for some reason I am getting  "Compile error - Wrong Number of arguments or invalid property assignment" on the word "Format". Anyone any ideas why? Below is code:

Dim target As Range, target1 As Range, target2 As Range, target3 As Range, target4 As Range, target5 As Range, target6 As Range, target7 As Range, target8 As Range, target9 As Range, target10 As Range, target11 As Range
Dim Prevday As String

Prevday = WorksheetFunction.WorkDay(Date, -1)

Prevday = Format(Prevday, "DDMMYY")

    Workbooks.OpenText Filename:= _
        "V:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\StructNotesBSRec_Daily_" & "*.txt" _
        , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 1), Array(17, 1), Array(18, 1)), TrailingMinusNumbers:=True
Who is Participating?
I just tested it in 2007 and no problem.
Can you post the whole code maybe the variable is getting changed somewhow ...

Your problem is in this code

Sub test()
Dim Prevday As String

Prevday = WorksheetFunction.workday(Date, -1)

Prevday = Format(Prevday, "DDMMYY")
End Sub

Open in new window

function workday is available from Excel 2007 onward you must have been probably using this now in Excel earlier version Excel 2003 or earlier this is where you will get
invalid property assignment

IS this possible ? what version of Excel your using ?
JustincutAuthor Commented:
I am  using Excel 2007, I have run the same Macro for months without problem and have not changed the Code
Can you double check that you aren't missing a library on the VBA side?  I have run into issues with code giving errors like the one that you mention when the application was used in a high version and the Library for the new version is being referenced.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.