VBA Error: "Compile error: Expected End Sub"

Question

Trying to pass "GetFullNamePDF()" to the Filename attribute, but getting the following error: "Compile error: Expected End Sub"

Sub PrintPDF()

    Function GetFullNamePDF() As String
        GetFullNameCSV = Replace(ThisWorkbook.FullName, ".xlsm", ".pdf")
    End Function

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "GetFullNamePDF()", Quality:=xlQualityStandard, IncludeDocProperties _
        :=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

I know nothing about VBA, and got the above code from a question I asked yesterday, but was unable to test at the time. Guessing the error has to do with the function, since the code works without the function added and the filepath/name hard coded.

Idea of the code is to dynamically use the filename of itself to name the path and file for the PDF. If you have any questions, just comment -- thanks!

kgiannakakis · Accepted Answer · 2010-12-17 15:44:49Z

6

You can't nest a function inside a procedure. You need to move it above:

Function GetFullNamePDF() As String
    GetFullNameCSV = Replace(ThisWorkbook.FullName, ".xlsm", ".pdf")
    'This should be
    GetFullNamePDF = Replace(ThisWorkbook.FullName, ".xlsm", ".pdf")
End Function

Sub PrintPDF()

     'Remove the quotes from GetFullNamePDF
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        GetFullNamePDF(), Quality:=xlQualityStandard, IncludeDocProperties _
        :=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

answered Dec 17, 2010 at 15:44

kgiannakakis

104k28 gold badges163 silver badges197 bronze badges

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

1 Comment

blunders Over a year ago

Thanks for taking the time to read the code and make sure everything is right; wise I code say my code was better, but never used Excel's VBA.

user325117 · Accepted Answer · 2010-12-17 15:42:27Z

1

It is illegal to declare a function within a sub. It should look like this:

Function GetFullNamePDF() As String 
    GetFullNamePDF = Replace(ThisWorkbook.FullName, ".xlsm", ".pdf") 
End Function 


Sub PrintPDF() 
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ 
        "GetFullNamePDF()", Quality:=xlQualityStandard, IncludeDocProperties _ 
        :=True, IgnorePrintAreas:=False, OpenAfterPublish:=False 
End Sub

answered Dec 17, 2010 at 15:42

user325117

Comments

Fionnuala · Accepted Answer · 2010-12-17 19:13:10Z

1

Like this:

Function GetFullNamePDF() As String
    GetFullNamePDF = Replace(ThisWorkbook.FullName, ".xlsm", ".pdf")
End Function

Sub PrintPDF()
    Dim sFileName As Variable

    sFileName=GetFullNamePDF()

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

End Sub

edited Dec 17, 2010 at 19:13

answered Dec 17, 2010 at 15:42

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

8 Comments

blunders Over a year ago

@Remou: There's no reference to "sFilename" in the code, don't think that'd work.

Fionnuala Over a year ago

@Blunder Do you forget that I wrote this function? I am very, very familiar with VBA (check the users on the VBA tag). It is by no means uncommon to set a variable to the result of a function, it makes debugging much easier. There is an error in your rewriting of the function, which I will correct.

blunders Over a year ago

@Remou: You're correct, it would work -- what would be the reason to set a a variable to the result of a function?

Fionnuala Over a year ago

As I said, it makes debugging much easier, it can make code easier to read as well.

Fionnuala Over a year ago

@RolandTumble If you are getting into coding problems at that level, consider ActiveSheet, which may or may not return an object.

|

Collectives™ on Stack Overflow

VBA Error: "Compile error: Expected End Sub"

3 Answers 3

1 Comment

Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related