Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practice for Public Variables for Column Headings
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practice for Public Variables for Column Headings
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best Practice for Public Variables for Column Headings
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Public Variables | Excel Programming | |||
Public Variables | Excel Programming | |||
Public variables | Excel Programming | |||
Public Variables | Excel Programming | |||
Public Variables | Excel Programming |