Excel 2013 VBA Runtime Error 5 with listobjects.add

Question

I have a main sub that I am using to set up my worksheets and data for analysis in other subroutines. As part of this I am turning some imported data into a table(data was imported from a CSV file) and placed into "IncidentsData" worksheet. The code for the entire module compiles. However, when I step through the module I get "Run-Time Error '5': Invalid procedure call or argument" on the line where I format the imported data into a table at:

Worksheets("IncidentsData").ListObjects.Add(xlSrcRange, ISDRange, , xlYes).Name   = "IncidentsDataTable"

Here is the overall subroutine:

Public Sub Categorise_Incidents()
Dim incidentsData1 As ListObject
Set Commodity = Worksheets("CommoditySheet").ListObjects("Table1")
Set QEListByGenus = Worksheets("QEListByGenus")
Set Results = Worksheets("Results")
Set IncidentsDataSheet = Worksheets("IncidentsData")
ISDRange = IncidentsDataSheet.UsedRange
Worksheets("IncidentsData").ListObjects.Add(xlSrcRange, ISDRange, , xlYes).Name   = "IncidentsDataTable"
Set incidentsData1 = IncidentsDataSheet.ListObjects("IncidentsDataTable")
initialiseDictionaries
MainSearchLoop QEListByGenus, incidentsData1, dictQENSGenus
End Sub

I have gone to a number of sites to work out how to do this action including: 1. the stack overflow pages on runtime error 5 in excel vba - most of these seem to be specific to pivot tables and didn't tell me what I was doing wrong 2. https://msdn.microsoft.com/en-us/vba/excel-vba/articles/listobjects-object-excel 3. stack overflow questions on creating a table in VBA How to create a table using vba? 4. analysistabs on creating a table in excel vba https://analysistabs.com/excel-vba/tables-examples/ Plus a bunch of others. Unfortunately, they haven't helped me work out what I am doing wrong. Any help would be greatly appreciated. Cheers, S26

Edited: to include .Name as that also does not work and throws a runtime error and to fix typos/errors.

Thanks, yeah I know. It still doesn't work and throws the same error even though syntax appears to be correct now and I've removed the typos. (don't code late at night should be the moral of that story) — S26
– S26, Commented Jun 22, 2018 at 0:54
@BigBen, thanks! I really appreciate your help. That solved it. — S26
– S26, Commented Jun 22, 2018 at 1:12

BigBen · Accepted Answer · 2018-06-22 00:38:50Z

0

First, change xlSrcERange and lYes to xlSrcRange and xlYes.

You're missing the .Name at the end of Worksheets("IncidentsData").ListObjects.Add(xlSrcRange, ISDRange, , xlYes). You haven't specified which property of the newly created table you want to be "IncidentsDataTable".

Alternately, you can do this:

Set incidentsData1 = Worksheets("IncidentsData").ListObjects.Add(xlSrcRange, ISDRange, , xlYes)
incidentsData1.Name = "IncidentsDataTable"

If you take that approach, it looks like you don't need to change the Name at all.

edited Jun 22, 2018 at 0:38

answered Jun 22, 2018 at 0:01

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

4 Comments

S26 Over a year ago

thanks very much for pointing that out. I had already tried that format with '.Name' and ended up playing around with it as I couldn't get it to work, Unfortunately, neither does your second solution. Even though I know theoretically both should work, it still throws a runtime error5, which is weird. Do you have any idea why it would do that?

BigBen Over a year ago

That should be xlSrcRange and xlYes maybe? Edited.

S26 Over a year ago

Dim ISDRange as Range and then Set ISDRange = ... – BigBen 14 mins ago

BigBen Over a year ago

Moral of the story is don't answer questions at night either. And use Option Explicit.

Collectives™ on Stack Overflow

Excel 2013 VBA Runtime Error 5 with listobjects.add

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related