CODEWITHSUNDEEP
vbaexcel
Jared
Jared

Reputation: 9

Excel VBA Macro Compile error: Expected End Sub

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.

Upvotes: 0

Views: 1109

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

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.

Upvotes: 0

Wouter
Wouter

Reputation: 383

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()

Upvotes: 4

Related Questions