The last post was about Excel 2007 losing certain formulas when saved in compatibility mode.
This post is across all file formats – from titme to time, for no apparent reason Excel 2007 will lose name ranges (this is SP1).
Its not file or user specific, not even name specific – just sometimes the odd one or two names disappear.
One workaround we have been doing is to define them in the workbook_open event – but this of course destroys the main benefit of names – that they adapt to edits. If you hardcode the address in VBA, you might as well just use the hardcoded ref and accept to risks and problems.
One approach I have used in the past is to put a ref in a cell as in =A1:D100. that will probably return some sort of error, but it will adpat with inserts and deletes within that range. Then on open define the name to refer to the formula of that cell.
Anyone else seen this disappearing name issue and got a reproducable case?
Or a solution?