Why does Excel Give a "Compile error: Syntax error" When I run the macro

Question

I am trying to create a print to PDF Macro for a worksheet that uses a drop down list. I want each option in the drop down list to be saved as its own PDF.

This is my code:

Sub Print_To_PDF()
Sheets("MS Wall Summary Daily View").Activate
    Dim vRws As Long, vRng As Range
    Dim d As Range, d8 As Range, Wst As Worksheet
    Dim fPathFile As String

fPathFile = [NewStoreRollout]
Set Wst = Worksheets("MS Wall Summary Daily View")
Set d8 = Wst.Range("D8")

With Wst
    vRws = .Cells(Rows.Count, "A").End(x1Up).Row
    Set vRng = Range(.Cells(2, "A"), .Cells(vRws, "A"))
    .PageSetup.PrintArea = "$C$2:$M$116"

End With
For Each d In vRng.Cells

d8 = d
Wst.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPathFile,
Quality:=xlQualityStandard, _IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Next d
End Sub

My Drop down starts in A1, though I want the PDFs starting in A2. The cell with the dropdown list is d8

The Cell named "NewStoreRollout" contains my filepath that I want the PDfs saved to.

Thanks

_IncludeDocProperties:=True is the problem; might that be IncludeDocProperties:=True? — rskar
– rskar, Commented Apr 8, 2019 at 19:53
Maybe that was one problem, but not the only one. Not Excel is Highlighting the ":=" next to xlqualitystandard. It says "compile error: Expected Expression" — austinmb
– austinmb, Commented Apr 8, 2019 at 20:08
I guess you just need an underscore to extend a code line to the next line: Filename:=fPathFile, _ — Asger
– Asger, Commented Apr 8, 2019 at 20:35
@GMalc the missing line continuation is the problem. Deleting one named argument will only make VBA complain about the next named argument. — Mathieu Guindon
– Mathieu Guindon, Commented Apr 8, 2019 at 20:36
@MathieuGuindon, yes i saw the underscore after, so I deleted my comment. — GMalc
– GMalc, Commented Apr 8, 2019 at 20:39

Mathieu Guindon · Accepted Answer · 2019-04-08 20:52:10Z

2

Executable instructions in VBA are terminated by one of two tokens:

An instruction-separator token :
An end-of-line token \r\n (i.e. vbNewLine / Chr(10)+Chr(13))

That would make this instruction incomplete...

Wst.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPathFile,

...if the 3rd parameter wasn't optional. Because it's optional, a trailing comma is perfectly acceptable, and is interpreted as passing a Variant/Empty value as the 3rd argument.

So VBA calls it a valid statement, and moves on to the next one:

Quality:=xlQualityStandard, _IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Uh-oh. This looks like an argument list, but nothing is being called here, so VBA is confused and doesn't know what procedure to pass this argument list to.

What you want, is for the two physical lines to be counted as one single logical line. You can do that using a line continuation token _ (a whitespace, followed by an underscore).

Wst.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPathFile, _
    Quality:=xlQualityStandard, _IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

That way when the compiler reaches the end of the first line, it knows the instruction is continued on the next line.

Looks like you had a line continuation after the Quality argument, then deleted the line break but not the underscore - that _IncludeDocProperties argument should be IncludeDocProperties. Feel free to add as many as you need (no more than 20 though) to make the code easier to process visually:

Wst.ExportAsFixedFormat Type:=xlTypePDF, _
                        Filename:=fPathFile, _
                        Quality:=xlQualityStandard, _
                        IncludeDocProperties:=True, _
                        IgnorePrintAreas:=False, _
                        OpenAfterPublish:=False

answered Apr 8, 2019 at 20:52

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rohrl77 Over a year ago

plus one for an indepth explanation that goes above and beyond!

austinmb Over a year ago

Thank you so much! I think it fixed that part. However, Excel is now highlighting "vRws = .Cells(Rows.Count,"A")…… that entire line.... calling it an application defined or object defined error? It's supposed to be Long right? I have a Goal Seek Macro that does something similar.

austinmb Over a year ago

Update to my previous comment. When I hover over the vRws line with my cursor. it says "VRws=0" and also that "x1Up=Empty". I have data populated from A2 to A21, so why is it saying this?

Mathieu Guindon Over a year ago

Add Option Explicit at the top of the module (always do that) ... x1Up doesn't exist, did you mean xlUp?

Collectives™ on Stack Overflow

Why does Excel Give a "Compile error: Syntax error" When I run the macro

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related