Simple vba sub giving error Compile error: Expected: =

Question

I have a very simple sub in excel vba:

Private Sub AlignText(r As Integer, c As Integer)                                                 'A sub to align the data in the cell
With ActiveSheet
    .Cells(r, c).HorizontalAlignment = xlCenter
    .Cells(r, c).VerticalAlignment = xlCenter
End Sub

I just want to supply column and row numbers to align text. I have tried putting in the full path to the cells using full sheet references too, but that doesn't help.

When I try to use the sub:

AlignText(NextRow, 16)

for example, I get the error:

'Compile error: Expected: ='

I have written very very similar subs before, that aligned text as well as doing other things, and they have worked fine. This must be something simple, but I don't know what.

Thanks in advance for any help.

Thanks, so simple! I don't see why it doesn't need the parentheses, but I'm glad that it works! — cohara
Parenthesis are typically for Functions, not Subs. It was thinking that you were expecting it to return a value. — braX

Shai Rado Shai Rado · Accepted Answer · 2018-01-12T13:59:12

Use:

AlignText NextRow, 16

Note: You should try to avoid using ActiveSheet. I would add the Worksheet object to your Sub as well.

Private Sub AlignText(Sht As Worksheet, r As Integer, c As Integer)

'A sub to align the data in the cell
With Sht
    .Cells(r, c).HorizontalAlignment = xlCenter
    .Cells(r, c).VerticalAlignment = xlCenter
End With

End Sub

And call it by using:

AlignText ActiveSheet, NextRow, 16 ' <-- if you have to use ActiveSheet

Simple vba sub giving error Compile error: Expected: =

1 Answers