Keeping code dynamic when adding columns to a list of records

[cwrm4]

Looking for advice on a "best practice" with Excel VBA:

I have a spreadsheet for customer master data records with about 30
columns. I have some simple userforms that enable the user to add/edit/
view records from the worksheet with the master data. The user does
not have access to the master data worksheet as I need to force the
data they enter to go through several validations prior to writing/
updating a record.

Right now to add/update a record (and fill in fields on the userform)
I have the column numbers hardcoded via public constants. Works great
for now...but, if in the future I want to add a column (say, insert a
country column after state), my column numbers will get "out-of-
whack".

What would be the best way to keep the spreadsheet and code dynamic in
terms of adding future columns (in between existing columns)? Should I
just, as an initialization event, search for the heading (e.g.
"phone") for each column and then assign the column numbers to global
variables? Do I just stick a bunch of "future use" columns in the
spreadsheet? Any guidance would be appreciated.

 

[Trevor Shuttleworth]

Two ways spring to mind: one is to define named ranges for the columns and
two is to have a "control sheet" where you define the columns. In the
second case, you'd read the control sheet and tailor your code.

So, for example:

Col Description
A Name
C Staff Number
etc

Match the descriptions to the column headings.

You can set the control sheet to be hidden or very hidden to stop users
accessing it.

Regards

Trevor

 

[OssieMac]

By naming ranges in a column on the worksheet, the name always refers to the
same cells irrespective of their actual address after inserting/deleting
columns.

However, if you delete or insert rows accross a named range in a column then
the named range will adjust in size. Likewise if you insert/delete columns
across a named range in rows.

The following example macro demonstrates the naming of a range in a column.
Copy it to a blank workbook and run it.

Then on the worksheet insert some columns before column E and then comment
out the 3 lines of code as per the comments and re run the code and observe
the range which is now selected.

Note that the named range is saved with the workbook.

If you haven't used named ranges before than in xl2007, select a range
->Formulas Ribbon-> Define Name. In earlier versions I think it is
Edit->Define name.

To anyone else who reads this if you have any other ideas then I am also
interested.

Sub test_Col_Name()

Dim stateName As Range

'Run macro once and then comment out the
'following 3 lines after inserting additional
'columns before column E and run the macro again.
Range("E1") = "State"
Range("E1:E20").Select
ActiveWorkbook.Names.Add Name:="State", RefersToR1C1:=Selection

Set stateName = Range("State")
stateName.Select

End Sub

Regards,

OssieMac