Best Practice for Public Variables for Column Headings

[Connie]

I have a spreadsheet that consists of 2 tabs with around 35 columns and 20 columns respectively. Throughout the code I am updating fields in the column. The developer that originally created the spreadsheet uses the actual column number in the code reference when updating a field which makes it difficult to know which field you're updating when you're looking at the code.. Is there a best practice around this? What I would like to do is create public variables that contain meaningful names with the column number indicated there and use the variable name throughout the code when a field is updated. For example:

Declare the following as public variables:
SHEET1_FirstName = 1 'column number for FirstName column on SHEET1
SHEET1_LastName = 2 'column number for LastName column on SHEET1

I can then use the variables when I need to make an update:

Sheets("SHEET1").Cells(l_Row, SHEET1_FirstName)

Is there a best practice around this and how much overhead is added by using this convention? This makes it easier to add or remove columns as well.

Thanks.

 

[GS]

Couple of points to consider...

1. I don't prepend sheetnames to fieldnames since sheetnames can be
easily changed.

2. I use Enums so they assist making code self-explanatory. For
example:

Enum DataFields
ID = 1
Firstname
Lastname
Addr1
Addr2
Town
StateOrProv
ZipCode
Phone1
Phone2
Fax
Cell
Email
End Enum

The structure follows the same order as the fieldname headings appear
in the data table. I specified the start point of this enum as '1' so
it follows the same order as column index. (If not specified, enums
start at zero)

3. I use a variant to 'dump' the data into a (also 1 based by default)
2D array. This is where editing occurs for existing data, after which
the array gets 'dumped' back into the worksheet to update the table.

4. I insert/delete directly in the worksheet and reload the array when
done. This keeps the data in memory sync'd with the data in the
worksheet.

5. I reload the array every time changes occur in the worksheet.

6. I ref fields in the array by their position in the enum. I ref
records in the array by their position (row) in the worksheet. For
example, to work with a user-selected record I ref the row/col like
so...

vData(lstData.ListIndex + 1, cboFields.ListIndex + 1) = 1234560001

...where the data is displayed in a multi-column listbox, and the data
field to edit is selected from a combobox dropdown of the userform used
to manage the worksheet data. Note that the enums display via
intellisense as I'm coding, meaning after typing datafields. I get a
popup list of every item in the DataFields Enum.

So, if the user selected record 'ID4' and wanted to update field
'Phone1' the enum syntax would be...

txtFax.Text = vData(cboData.ListIndex + 1, DataFields.Fax)
txtEmail.Text = vData(cboData.ListIndex + 1, DataFields.Email)
txtPhone1.Text = vData(cboData.ListIndex + 1, DataFields.Phone1)
...and so on

...is what gets put into the controls if I was using textboxes for field
data.

If I was editing the data in the array...

vData(cboData.ListIndex + 1, DataFields.Fax) = txtFax.Text
vData(cboData.ListIndex + 1, DataFields.Email) = txtEmail.Text
vData(cboData.ListIndex + 1, DataFields.Phone1) = txtPhone1.Text
...and so on

7. I design the worksheet to include (separate) dynamic defined name
ranges for the fieldnames and data records so these can be used to
populate userform controls so editing updates can be immediately
displayed in the userform.

8. I design the userform to implement a 'button state' concept so only
appropriate user action choices are available in context to what's
being done with the data.

I hope this helps you! It may seem quite complex but it's not really. I
have a XLS template for using either textbox fields or the listbox
approach. All that's required is setting up the data table fieldnames,
and deciding whether to use auto-generated record IDs or user-defined
ones. This is controlled by setting a global boolean (gbUseAutoID). If
set 'True' record IDs are sequential and record deletion blanks the
data but keeps the ID in the table, making it available for new record
data. If set 'False', deletted records are remove from the table
entirely. New records are always appended to the next row following the
last record.

In the case of the textbox fields form, records are navigated via a
combobox containing record IDs

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

For clarity...

The Enum approach addresses your issue with identifying fields.

The array approach combined with the combobox index for record ID
and/or fieldname, along with direct action with the worksheet for
add/delete is a very reliable and safe way to manage table structure.
Since the fieldnames and data table defined range names are dynamic,
they'll each adjust to reflect structural changes in the data
worksheet. Once changed, reload the array and continue working with it
until updating the table is needed next.

HTH

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion