Although I try and avoid the use of VBA now where I can. It can still come in really handy for the occasional app.
I created a reporting tool that collects stats from various web pages that we have and then displays them in a way that is nice and client friendly. Apart from a couple of niggles it works really well. The biggest problem I find when programming Excel with VBA is that when something changes it can be a bit of a pain to make the changes in VBA to cope with it (such as a design change in the spreadsheet, moving of columns ect.. ).
We are moving over to a new project management / development tool at the moment and as a result my report has had to change what data it collects and where from. Whilst making the change I realised that I had made a foolish error and was not properley cleaning up my data connections when finishing the update (not to mention a couple of other problems). I had been deleting the connections but not the named ranges when doing the tidy up. Anyway, in case anybody else has had this problem something like this should do the trick in the future...
First you delete the connections to the various sites / databases that you have, then you delete the named ranges you have. Obviously if there are named ranges you have that you don't want to be deleted you would need to put in the for next an if statement to step over the ones you want to keep.
Comments