Unexplainable failure: unable to add row to ListObject table (Excel 2021)

Hi, I’m using Excel 2021 and am running into a strange and for me an unexpected and strange error.
EXPLANATORY INTRODUCTION
I wrote an App as an xlsm (could have been an xlsm as well) the works perfectly.
The is a worksheet named ‘DATA’ and in this worksheet a LISTOBJECT table named ‘Tabel1’ (not given it a ‘real name’ yet but that’s not the problem.
The table has 25 columns and about 404 rows of data in the DataBodyRange
There is a user form in which there’s a Frame with about 10 fields to be completed and a LISTBOX with the same 25 columns; this works as designed, no issues there.
The form is displayed as modeless (but if modal the same issue occurs)
When the user selects any record in the LISTBOX the 10 fields are populated
There is a button to EDIT the record and there is button to ADD a new record
All this is quite straight forward.
When the user clicks the EDIT button the fields are unlocked and the user edits the entries, when finished the user presses the EDIT button again (Caption has changed to ‘Save Modifications’ and the record is correctly updated.
Works like designed. Perfect
When the user presses ADD the 10 fields are cleared and unlocked and the user can fill in the fields
When finished, the user presses the ADD button (Caption is now ‘Save New Record’ and a new row is added to the LISTBOX at the bottom copying the format of the above row and the record is saved in this new row. Again, no problems.
The macro for this routine

Please Login or Register  to view this content.

The section to populate the different columns is the same as saving the modified record but using the new table row returned by the function

WHAT I CHANGED AND WHAT HAPPENS NOW
Because I did not want the file with the Table to be macro-embedded to avoid having to update the table every time something needs to be changed , I removed the DATA sheet from the xlsb file.
This file I saved as an xlam file (AddIn extension) so it only and you access the Userform pressing Crl + Shift + M
The user form opens looks for the file with the data and opens it, and populates the Userform. All works as designed.
When I edit a record and save it, it saves it and you can continue editing until you finish
The problem is only when ADDING a new record.

Of course; the macros have all been set to refer to the now ‘external’ file with the data but if I use the Add_Tablerow_to_Bottom or any other method of adding a row to the table Excel either throws an error or it just closes and nothing is added.

When I try it manually and invoke the macro form the direct window with al, the parameters the same thing happens and debug does tell me to End or Debug but Excel just closes.
Anybody any ideas?

The only thing I need is to be able to add a new table row to an existing LISTOBJECT in another file from the calling file.
I’ve also tried it this way

Please Login or Register  to view this content.

And I’ve also tried it this way

Please Login or Register  to view this content.

Simply adding a row the table before saving also gives an error and the file is closed

IMHO, you can't add, insert, modify ranges within a function, you have to do it with a macro in a normal module
something like this

Please Login or Register  to view this content.

so it's a macro vs a function

here it's about a formula in a cell, is it the same with VBA ?
https://support.microsoft.com/en-au/...c-79d36192b7a1

The strange thing is if the data AND the file is in one it does work, and in other files with the same contstruction no problems, it only occurs when I have the macro's in one file and the data in a separate file.
Even just adding a new row to the table fails.

you add that row with a function/macro in a UF or in a regular module ?
When do you crash ? When you add the row or when you add the data in the new row ?

I created some dummy files.
All three files are in a zipped, must be unpacked in the same folder.
The xlam file is the 'AddIn' where you can open the data file and edit or add a new record, this uses the externa UDF to add a new row to the table before saving and saves the record.

The other one is an xlb file where the data table is in the same file.
Now, all at once it seems to work but in my production file with a little more extra functionality it gives me an error hen I us the Add_Row_To_Borrom UDF
So maybe there's something wrong with the data file.
But the attached files are the idea I've developed, that data in the xlsx file is fake data generated using fake generator

sorry, i have no clue

Thanks,
I had tried something similar and even placed the code in the user form save record code to avoid calling an external UDF and still the problem remains.
The error message is always the same one.

The stange thing is that the sample files I attached seems to work

I literally used your code but the moment the resize is invoked it throws the error (as it did with my insert)

The other error is the same error number but the 'Method Add is not supported

Have you tried replacing the sheet and/or table? There may actually be some devil sitting in them.
Is it possible that the source workbook contains event routines (or application-level events programmed in XLAM) that could affect the error?

Artik

Yeah, the same thought. I reset the table to range. copied the worksheet to a new file made sure everything was 'clean' then converted the range to table again and ..... the same problem.
409 rows by 25 columns.
When edtiting a record and saving the changes or even deleting a specific record, it all works, only adding a new row no matter which way I try it the error happens
Thnaks for thinking with me but I'm almost out of ideas

Sorry, I meant that after the table was converted to range I copied the data to a new workbook and then recreated the table.

I just did a thorough cleaning, removed all the data validation rules, conditional formatting, cell formats you name it all created a new workbook.
The data exported as text and then copied it to this new file and ... boom FAIL AGAIN !!!
It's past midnight here (like at your place) so I'm off to bed.
Thanks for thinking with me.
Good night

True, stubborn as H
I wrote the following code

Please Login or Register  to view this content.

As a test I just opened the data file adn ran the macro from the project, the table row was added without any problems.
Repeated it several times and no problems.

Then I replaced the old funcion cal with this one and when I ran that it gave me an entirely new error message

The Object invoked was disconnected

and crashed, so the error occurs in combination withe the userform and the routine when it's called from there while the saving a mdofied record and deteling a record work, so it's not the references it's the add row that causes this

I think I found a possible reason but ... haven't been able to check it but every time the data table is opened a new instance of Excel is opened.
I set an instance counter and it starts with 3 which surprises me.
Will continu...

I did some more testing and the instances count is misleading; yes, there number of instances increase with every file that's opened.
I edited the instances counter routine to show the actual number of workbooks opened in each instance and it's only one in every instance and that's the PERSONAL.xlsb
Now I also understand why sometimes I get the notification that the Personal.xlsb is opened read only and can click Notify or ignore
Still the error only arises when adding a row to the ListObject table, in the meantime I've been completing the project and updating a modified record er deleting a particular record works like a charm, only the adding a new row and saving the record goes wrong.
If I open the data table and manually add a new row the it works and also if I run the macro from the direct window the row is added so the table is not bad, it's something in the combination of all the routines.
Haven't been able to create the dummy data table but hope to get it done, 'till then it's but most of all keep
Thanks again Artik for thinking with me.

I repeat the questions

Originally Posted by Artik

- Did you check the behavior of the original set of files on another computer?
- Will anything change if you check the checkbox "Ignore other applications using dynamic data exchange (DDE)" in Excel options on the Advanced tab, in the General section?

I don't know what, but something is causing the code to malfunction. In my opinion, this code should not open a new instance of Excel. Is it possible that the Office installation is broken? Unless there is something in the original file that creates a new instance of Excel.

Artik

I don't have another system at my disposal at this moment, I've asked the person who I'm helping to test it and see what happens on his side.
About the Excel instances, that was an incorrect module that kept recounting and incremented the count.
I found the module I had used before and now it now returns 1 instance which is normal. As a test I opened another instance and the count went up one and back to one when I closed it, so that's cleared.
The DDE checkbox does not change anything so now I'm waiting for an answer from my friend.

Hans...Cannot replicate your error...However, this method has never failed me before...
Implement into problematic file and try...

Please Login or Register  to view this content.

Thank you sintek, I'll give this one a try and let you know

Hi sintek, I tried it but it did not correcly add a row to the table and furthermore, but that's solvabale I have 25 table columns.
The strange this is if you look at the files I posten in my Post #5, the add row macro there is the same one I use here and it works in the unexplanable files.
As a test I copied the entire workbook with the table I'm having issues with and renamed it unexplainable_failure.xlsx and it works with the macro there, adds the new row and appends the data.
I'm now going to take apart the xlam file I'm using and rebuild all the modules because it must be something there.
Thanks for the tip, I like the idea but need to figure out why it did not add a new row

it did not correcly add a row to the table

That is strange...The code does all in one go...worked as it should with your zipped files...xlam opening database too...

I'll try it again later, you did however gave me an idea though.
And, BTW, the firned I'm helping is from Cape Town

I'm helping is from Cape Town

Awesome...Yeah, I've noticed that there are not many developers that create Tailor-made automated business solutions...
I've been blessed with an abundance...
Good luck with your project...

PS...As you are making use of a database and userform interface I would just convert table to range...

I'll keep you posted. I've been helping him since well, I think about 5-6 years

The code below when tested directly form the VBAProject work:

Please Login or Register  to view this content.

When addressesd inside the process these same macro's as well as the Resiez you opted for fail

Cannot find the resaon why.

Also if I separately call the Add_Tablerow_to_Bottom function outside the entire routine it fails too

Fails with what file?
Why using a list object anyway...That data does not justify it's use...Just convert to range and that will solve all your problems...

That’s the entire issue we’ve been busy with all these posts, the data is an Xmas file with a 407 row 25 column listobject, the xlam is the macro file with which opens the data file and is used to process the records, modifying and deleting a row no problem only when adding a new row to the bottom using the macro’s mentioned throws the error

I'm not too familiar with VBA and tables but one thing I came across in a book on Excel tables (by Zack Barresse and Kevin Jones) might be relevant: if the table has total rows enabled, you should temporarily disable the totals (MyTable.ShowTotals=False) before using the resize method. Then reenable ShowTotals (MyTable.ShowTotals=True).

Apparently, not doing this causes the table to not recognise the new rows and it won't expand.

I'd like to thank everyone that participated and offered solutions and ideas. The thing of this all is that it only fails on my system and only with this particular file
OS: W11-64-bit
Microsoft® Excel® 2021 MSO (Version 2311 Build 16.0.17029.20028) 64-bit
I copied the code to a new xlam file with just the table parts and all that as in the attached zip in Post#5 and in that file it works
Last night I copied the non-working file I've been using to a Windows 10-64 bit system using Office 2010 and no issues it works.
The entire thing just fails with only one file and the same table on my system so I'm going to have to accept that fact that there is no specific tangible reason why it fails.
The person I have been helping has no issues with the file and he's uisng Office 2016.
This issue I'll register it as an irritating bug but only with this one file; the same code in all my other files, xlsb, xlsm and xlam work perfectly
I'll mark this post as SOLVED after posting this reply
Once again, thank you all for participating and thinking with me.