It’s hard to admit me, but recently I’ve discovered new possibilities in Excel. I saying about XLAM add-ins, which work in background, I’ve found that they are much fasters then .xlsm code, .xlam might be a database for .xlsx files and it is good solution for team using code when .xlam file is saved in network folder.
I’ve change a little bit original code to add code lines to VBA module automatically. I think code looks much better and is simpler.
On the beginning I would say it is not good solution. I’ve spent so many hours to improve my VBA code in the end chose simplest solution. Despite Excel will save data from array to sheet via drop down list and dynamic list will be removed is very likely list error will appear when workbook is opening.
In order to create dynamic variable – array need to be used. Through dynamic variable I understand variable what get numeric value for example to define row number.
This simple solution shows how to check if sheet with specific name exist in worksheet. Continue reading
I’ve searched solution for global error handling in internet – but I think there is no this kind of solution. Therefore I’ve made simply code with global error handling.
I think turning on error handling make sense when code lines are added to our code. Unfortunately VBA console not predict this kind of solution. Following code allowed add code lines automatically.
From some time I’ve tried to find solution for fast and simply database use in Excel. Some time ago I’ve made test with XLAM add-ins as VBA code collection and database too. My project has been saved on network folder with authorization to all users.
I think recording macro to create Pivot Table is not a good idea. First problem will appear when you will try to add second Pivot Table – is not possible with the same name.