VBA - Compile Error: Expected Function or Variable

Question

I'm trying to make a document that has an "invoice" number that increases on the document that goes up every time I print the document. I found a macro code online but I keep getting the

Compile error

I'll attach a snap shot of what I'm getting with the piece that keeps screwing up highlighted.

Sub SerialNumber() 
'
' SerialNumber Macro 
'
'
Dim Message As String, Title As String, Default As String, NumCopies As Long 
Dim Rng1 As Range 

' Set prompt. 
Message = "Enter the number of copies that you want to print" 
' Set title. 
Title = "Print" 
' Set default. 
Default = "1" 

' Display message, title, and default value. 
Numcopies = Val(InputBox(Message, Title, Default)) 
SerialNumber = System.PrivateProfileString("C:\Users\JShewchuk\Documents\Macro\Settings.Txt", _
"MacroSettings", "SerialNumber") 

If SerialNumber = "" Then 
   SerialNumber = 0
End If 

Set Rngl = ActiveDocument.Bookmarks("SerialNumber").Range
Counter = 0

While Counter < NumCopies 
    Rng1.Delete 
    Rng1.Text = Format(SerialNumber, "00#")
    ActiveDocument.PrintOut
    SerialNumber = SerialNumber + 1 
    Counter = Counter + 1 
Wend 

'Save the next number back to the Settings.txt file ready for the next use.
System.PrivateProfileString("C:\Users\JShewchuk\Documents\Macro\Settings.Txt", "MacroSettings", _
        "SerialNumber") = SerialNumber 

'Recreate the bookmark ready for the next use. 
With ActiveDocument.Bookmarks 
    .Add Name:="SerialNumber", Range:=Rng1
End With

enter image description here

Is there something I'm doing wrong?

you are trying to assign a value to the sub iteself (as you would with a function) — QHarr
– QHarr, Commented Jan 19, 2018 at 19:19
I've edited your post to include the screenshot, but please in the future paste the actual code in your question (indented 4 spaces - from the VBE select the code, hit TAB, copy, then paste in your question) — Mathieu Guindon
– Mathieu Guindon, Commented Jan 19, 2018 at 20:04

Mathieu Guindon · Accepted Answer · 2018-01-19 20:14:44Z

4

To expand on braX's answer...

That's the syntax for assigning the return value of a Function or Property Get member - namely, you are assigning to the procedure's identifier:

Public Function GetTotallyRandomNumber() As Long
    GetTotallyRandomNumber = 4
End Function

Seems you mean to have a local variable named SerialNumber, however VBA already knows this identifier as the name of a Sub procedure named SerialNumber, and because a Sub procedure doesn't return anything, it can't legally be assigned like this.

Declare a local variable inside the procedure's scope, before the illegal assignment:

Dim SerialNumber As String
SerialNumber = System.PrivateProfileString(...)

And then your code will work... however I wouldn't recommend using the exact same name as the procedure.

My recommendation would be to name the local variable SerialNumber, and to rename the Sub procedure so that its name starts with a verb. Procedures do something, they're actions: find a meaningful name that describes what it does, and go with that.

Naming is hard though - if you can't find a simple name that describes what your procedure does, it's probably because it's doing too many things. Split it up into smaller, more focused procedures.

Public Sub PrintActiveDocumentAndAddSerialNumberBookmark()

answered Jan 19, 2018 at 20:14

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

1 Comment

FreeSoftwareServers Over a year ago

Great answer to a bad question IMO... This is related to this webpage, which needed this extra bit of code added! wordmvp.com/FAQs/MacrosVBA/NumberCopiesOf1Doc.htm

braX · Accepted Answer · 2018-01-19 20:05:38Z

2

You are treating a Function like a Subroutine. Subroutines do not return values.

If you want the routine to return a value, then change Sub to Function (at the top)

If you are not wanting it to return anything, then choose a different variable name instead of SerialNumber, or change the name of the Subroutine.

You cannot use a variable name that is the same as the name of the Subroutine.

edited Jan 19, 2018 at 20:05

answered Jan 19, 2018 at 19:18

braX

11.9k5 gold badges24 silver badges38 bronze badges

1 Comment

Mathieu Guindon Over a year ago

You cannot use a variable name that is the same as the name of the Subroutine - actually VBA is pretty permissive with regards to member shadowing: you can declare a local SerialNumber variable within a Sub SerialNumber procedure's scope. Not that I'd recommend doing that though.

Collectives™ on Stack Overflow

VBA - Compile Error: Expected Function or Variable

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related