Recently, I created a really nifty excel file that allows staff to easily look up and visualise data that previously had really complicated automatic file names. Then I came to finesse the beast. I wanted it locked down, read only and singing like a bird. So my VB macro grew – remembering to keep up my good habits, (See previous post about ways to speed up your VB Code).
But in my aim for brilliance, I forgot that excel has some evil little niggles and my excel spreadsheet stumbled and fell.
Problem: Error pop-up box followed by error report
<removedRecords summary=”Following is a list of removed records:”><removedRecord>Removed Records: Sorting from /xl/tables/table1.xml part (Table)</removedRecord>
The problem is that my macro sorts a table in the file – easy enough. Weird when you consider that this is a standard VB function. For some reason Microsoft doesn’t have a solution to this yet, not even listing it as an issue on their site. I’ve found a good number of questions about it online, but the only suggested solution I’ve found is to use a filter instead of a sort – which only really works in a few instances (not for mine). I managed to stop it trowing up errors by disabling the sort. All well and good except I need to sort my data!
Microsoft has released a number of hotfix patches for the same error with different causes, fingers crossed they get round to understanding the bug in the system and fix it for the next release.
What’s the biggest issue you’re having with vba at the moment? Have you found any problems microsoft haven’t fixed?