Excel VBA Macro Compile error: Expected End Sub

Question

Trying to assign my code to a button which works stand-alone in VBA however but I'm receiving the following error when adding it to a button;

Compile error: Expected End Sub

My code is as follows;

Sub Button1_Click()

Private Sub filename_cellvalue()
    Dim Path As String
    Dim filename As String
    Path = Environ("userprofile") & "\Desktop\Invoices\"
    filename = Range("K13")
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & filename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

VBA is new to me so any help would be greatly appreciated. Thanks.

If you indent your code properly you see that your first procedure Sub Button1_Click() has no End Sub. That is what the error message tells you. — Pᴇʜ
– Pᴇʜ, Commented Sep 28, 2018 at 11:18

Pᴇʜ · Accepted Answer · 2018-09-28 11:19:28Z

4

You're declaring a procedure twice: Sub Button1_Click() and Sub filename_cellvalue().

You should delete one. If you're putting the code in your button event, delete Sub filename_cellvalue()

edited Sep 28, 2018 at 11:19

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

answered Sep 28, 2018 at 11:13

Wouter

3831 silver badge12 bronze badges

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

Comments

Mathieu Guindon · Accepted Answer · 2018-09-28 14:01:24Z

Sub Button1_Click()

Private Sub filename_cellvalue()

Sub statements declare a procedure scope. The syntax goes:

{accessibility} Sub {name} ({args})
    {statements}
End Sub

Where the {accessibility} modifier, {args} parameter list, and {statements} in the body, are optional (note, without an access modifier, a procedure is implicitly Public - whereas in many other languages [including VB.NET] the implicit default is Private; best be explicit here).

Such a syntax makes nested Sub statements illegal:

Sub DoSomething()
    Sub Illegal()
    End Sub
End Sub

Therefore, when the compiler encounters a Sub statement, everything that follows is considered as part of the procedure's body, up to the next End Sub token.

If the Button1_Click handler is legitimate, it needs to be properly terminated before Sub filename_cellvalue begins:

Sub Button1_Click()
End Sub

Private Sub filename_cellvalue()
    '...
End Sub

Otherwise, the Sub Button1_Click() incomplete statement needs to be removed.

That said, filename_cellvalue is a bad identifier for a procedure: Sub procedures do something, their names should start with a verb. ExportAsPDF would be more appropriate, for example. Note the PascalCase name, as opposed to lower_snake_case: the underscore character has a special meaning in VBA, as evidenced by Button1_Click.

The underscore separates the interface/event source from the interface member/event name: Button1_Click is invoked when the Button1 object raises its Click event, and naming that procedure anything different would break the link between the object and the event handler: the procedure would never run in response to a Click on Button1. Using underscore in non-handler procedures, especially if public, will cause problems (read: more compile errors) when you get into more advanced, object-oriented programming concepts involving interfaces and polymorphism.

Collectives™ on Stack Overflow

Excel VBA Macro Compile error: Expected End Sub

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related