Diane has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Effect of adding a column on a worksheet to all column refs in V
I was about to add a column to my worksheet when it occurred to me any referencing in vb to column numbers e.g. in my arrays; in my offsets etc will now be incorrect. I could of course add the column on the right, but maybe it makes more logical sense for the column to be adjacent to another mid range. I'm thinking this must be quite a common requirement, what would be the recommended way to write vb to cope with this?
RE: Effect of adding a column on a worksheet to all column refs
Hi Diane
You're quite right it is a common problem when amending spreadsheets with macro's attached.
Best practice emphases the importance of a change log, to keep track of changes and the impact they will have. This will help you quickly identify areas and take the appropriate steps when coding.
Additionally,identify something other than the column letter which will make it easier for Excel to find e.g. the Heading of the table "Employee Name" or "Invoice Amount" this way if the heading is moved Excel will still find it and move the relevant section.
Kind regards
Dennis
RE: Effect of adding a column on a worksheet to all column refs
Thanks Dennis, I will change my references to column name wherever it is possible. There is no way similar around this for arrays, is there?.
Di
RE: Effect of adding a column on a worksheet to all column refs
Hi Diane
The principle applies for arrays too. You could consider using dynamic named ranges to name the range on the spreadsheet and then assign the range to an array in VBA.
Link to a tutorial on dynamic named ranges https://www.excel-easy.com/examples/dynamic-named-range.html
Regards
Dennis
