Excel VBA简单Sub调用报错‘Compile error: Expected: =’求助

That compile error is a classic VBA syntax gotcha— let's get this sorted quickly!

The Root Cause #

When you call a Sub procedure in VBA, parentheses around arguments are only allowed if you use the Call keyword. Your current call AlignText(NextRow, 16) uses parentheses without Call, so VBA misinterprets it as an attempt to assign a value (hence the "Expected: =" message).

Two Simple Fixes #

Pick either approach to resolve the error:

1. Remove the parentheses when calling the Sub:

   AlignText NextRow, 16
   

2. Use the Call keyword with parentheses:

   Call AlignText(NextRow, 16)
   

Bonus: Make Your Code More Robust #

Your current code relies on ActiveSheet, which can cause bugs if the user switches sheets mid-execution. Let's tweak the Sub to accept a worksheet as a parameter— this makes it more flexible and reliable:

Private Sub AlignText(targetSheet As Worksheet, r As Integer, c As Integer)
    '用于对齐单元格数据的Sub
    With targetSheet
        .Cells(r, c).HorizontalAlignment = xlCenter
        .Cells(r, c).VerticalAlignment = xlCenter
    End With
End Sub

Call it like this (replace "YourSheetName" with your actual sheet name):

AlignText ThisWorkbook.Worksheets("YourSheetName"), NextRow, 16
' Or with Call:
Call AlignText(ThisWorkbook.Worksheets("YourSheetName"), NextRow, 16)

Why Your Earlier Similar Sub Worked #

Chances are you either called it without parentheses or used Call unconsciously— those tiny syntax details are easy to overlook when you're focused on functionality!

内容的提问来源于stack exchange，提问作者user3340858