VBA Syntax error on creating table

Question

I'm getting syntax error when trying to create simple table and i don't know why. It works when i write .name after it and it also works when i write only ws.ListObjects.Add. So can somebody please explain what is the correct syntax? Thank you.

Sub CreateTable()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range

    Set wb = Workbooks("?????")
    Set ws = wb.Worksheets("owssvr-úpravené")
    Set rng = ws.Range("A1").CurrentRegion

    rng.Find(What:="Next_Review_Date", LookAt:=xlWhole).Name = "Next_Review_Date"
    With ws.Range("Next_Review_Date").Offset(0, 1)
         .EntireColumn.Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
         .EntireColumn.Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
         .EntireColumn.Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    End With

    With ws.Range("Next_Review_Date")
        .Offset(0, 1).Value = "Rozdiel dátumov"
        .Offset(0, 2).Value = "Overdue"
        .Offset(0, 3).Value = "Review year"
    End With

    ws.ListObjects.Add(xlSrcRange, rng, , xlYes)

End Sub

Have you tried Googling the correct syntax? (It would have taken less time than typing the question!) — ashleedawg
– ashleedawg, Commented Feb 15, 2018 at 7:59
@ashleedawg While looking into the docs is always a good idea, I think it is not about syntax of this specific function but more a thing about when do you use brackets and when not. Therefore I think your link is not helping at this question. — Pᴇʜ
– Pᴇʜ, Commented Feb 15, 2018 at 8:04
@ashleedawg Yeah i was on that site but like Peh mentioned it was about that brackets and i wanted to olso know why it ist working like that. — TooMacho BA
– TooMacho BA, Commented Feb 15, 2018 at 8:08
@Pᴇʜ - The question clearly says what is the correct syntax? ...so that's what I posted a link to. — ashleedawg
– ashleedawg, Commented Feb 15, 2018 at 8:08
@ashleedawg i wrote wthat is the correct syntax because VBA was marking it red like it was syntax problem so i thought i was wrting it in bad syntax. I visited that site but the brackets were the problem. I'm novice in VBA and sorry if you were annoyed with my question. — TooMacho BA
– TooMacho BA, Commented Feb 15, 2018 at 8:13

Pᴇʜ · Accepted Answer · 2018-02-15 08:00:35Z

2

You need to remove the brackets from

ws.ListObjects.Add(xlSrcRange, rng, , xlYes)

Why?
If you call a procedure/function without returning something into a variable then use no brackets:

ws.ListObjects.Add xlSrcRange, rng, , xlYes

if you return something use brackets:

MyVariable = ws.ListObjects.Add(xlSrcRange, rng, , xlYes)

This is valid for all other functions/procedures too.

answered Feb 15, 2018 at 8:00

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

1 Comment

TooMacho BA Over a year ago

Thank you very much

Collectives™ on Stack Overflow

VBA Syntax error on creating table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related