You can put anything you want on the first row, but in tables of data (I use the term "tables" loosely here), it is usually desireable to have column headers that describe what data is in each column. Excel has features that use column headers if they exist. For example, sorting and filtering.
So, a default? Not in general, but it is a useful convention that Excel caters to. Actually, Excel caters to having the headers on the row above your data. It does not assume that your data starts on the 2nd row of the worksheet (even though that is common). Unless you select a table range manually (in the case of sorting and filtering), Excel assumes that the table is seperated from the rest of the worksheet by blank rows and columns, and if the first row of that separate area looks like it could be headers, it assumes it is. Sometimes people put summary information or descriptive text above a table of data, perhaps starting in row 1. Then, a blank row, then the headers, then the data. Even the blank row is not REQUIRED (Excel is very flexible), but it allows Excel to guess the range that constitutes your table and it's headers more easily. Asa -----Original Message----- From: Domain Admin [mailto:domainqu...@gmail.com] Sent: Monday, March 19, 2012 6:13 PM To: Asa Rossoff Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping And I forgot one question (not sure how to get all this back to the forum but will try to stay there in future). Is it a default in Excel that the first row names are considered column headers and not part of the data set? I see it act that way, but find nothing that says so or explains it. On Mon, Mar 19, 2012 at 6:07 PM, Domain Admin <domainqu...@gmail.com> wrote: > I can see my C programming background (yes I am old) is not a big help > here. I need some object oriented experience to get this faster (or > be younger). > You final solution is perfect even if not adding a column while > running as no other code has to change if new columns are added as > long as all operations > in the code are using the named references. Nowhere in the book does > it use the Set command. It would do the same thing as you but without > the Set keyword in front. If I understand correctly your way creates > a reference by name and not using Set creates an object with that > name? > > On Mon, Mar 19, 2012 at 4:21 PM, Asa Rossoff <a...@lovetour.info> wrote: >> Hi Howard, >> >> No worries :) Ask as many questions as needed. Best to send your replies >> to the list though, so you get the benefit of other replies (if only because >> I may not have time to reply myself). This also allows other VBA learners >> can benefit from the conversation. >> >> >> >> A defined name (either via range.name="mycolumn" or names.add) that refers >> to a range sticks to the same data/cells even when rows and cells are >> inserted and deleted because Excel updates those name references >> automatically whenever those events occur. This is just as Excel does the >> same for references in cell formulae when insert and deletes occur. >> >> >> >> Usually if you are referring to ranges in VBA you do not use defined names, >> but just use range variables. Use defined names when the names need to be >> used from cell formulae, or as one method of retaining those names between >> sessions (file close, reopen). >> >> >> >> If you use only a range variable, and column C contains a certain field of >> data -- say, Birthday -- then you or the user inserts a column before C to >> add another field -- say, Anniversary... And you use code similar to the >> following: >> >> Public Birthday As Range >> >> Sub Workbook_Open() >> >> Set Birthday = Range("C:C") >> >> 'Column gets inserted here with heading "Anniversary" >> >> MsgBox Birthday.Cells(1,1).Value ' Display the first cell value (column >> heading) >> >> End Sub >> >> ...open the file, allowing the above event to execute. Message should say >> "Birthday". >> >> ...then close the file... >> >> ...reopen...message will say "Anniversary". >> >> >> >> The problem with the above is that although the Range variable will adapt >> correctly when a column is inserted and still refer to the Birthday column, >> every time the file is opened it is set explicitly to refer to column C >> again. >> >> >> >> A defined name is a simple solution since it is saved between sessions and >> automatically adapts. >> >> >> >> The next simplest solution that comes to mind, and is actually even smarter >> (adapts if user just changes the text of the column heading without >> inserting or deleting columns) is to use range.find or >> worksheetfunction.match to find the column with the correct heading, and use >> that. For example: >> >> Set Birthday = Rows(1).Find("Birthday").EntireColumn >> >> ...or: >> >> Set Birthday = Sheets("MySheet").UsedRange.Rows(1).Find("Birthday", >> LookIn:=xlValues).EntireColumn >> >> ...or: >> >> Set Birthday = Columns(WorksheetFunction.Match("Birthday", Range("1:1"), 0)) >> >> ...or: >> >> With Sheets("MySheet").UsedRange >> >> Set Birthday = .Columns(WorksheetFunction.Match("Birthday", .Rows(1), >> 0)) ' Resulting range will be the used part of the Birthday column, e.g. >> C1:C101 >> >> End With >> >> >> >> The above methods and their variants will always find the column with the >> given heading, so they are very adaptable and will find the right column >> after closing/reopening the file or inserting/deleting columns, or changing >> column headings. >> >> >> >> Asa >> >> >> >> -----Original Message----- >> From: Domain Admin [mailto:domainqu...@gmail.com] >> Sent: Monday, March 19, 2012 3:23 PM >> To: Asa Rossoff >> Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using >> column names for looping >> >> >> >> You are a very helpful fellow. You were right the first time. I just >> >> did not understand that from your answer (I am still reading Excel VBA >> >> Programming for Dummies). This expression you sent before >> >> range("C:C").name="mycolumn" >> >> >> >> I assumed bound mycolumn name to column C no matter what was in column >> >> C and if you inserted a new column before C that became column C then >> >> mycolumn would be bound to that. And I do not understand why that is >> >> not the case from reading this but I take your word for it. >> >> >> >> In your new examples much confusion. What is the value of a defined name? >> >> Is the other method where you say if only using from VBA equivalent to this >> >> Range("C:C").Name = "mycolumn" and then use mycolumn as the data >> >> reference for the >> >> cells in column C? >> >> >> >> Howard >> >> (thanks again and I promise this is the last question and not to be >> >> your VBA stalker) >> >> >> >> On Mon, Mar 19, 2012 at 1:48 PM, Asa Rossoff <a...@lovetour.info> wrote: >> >>> I think I misunderstood your need. I thought you wanted the name to stay >> >>> with the data, allowing you to insert columns and have the name still >>> refer >> >>> to the same data. This method does that. >> >>> >> >>> >> >>> >> >>> If you want the name to stick to the column/range reference without regard >> >>> to inserted columns (always column C no matter what, i.e.), and you will >> >>> only use the name from VBA, then you could use a string variable: >> >>> >> >>> Dim MyColumnAddress As String, MyColumn As Range >> >>> >> >>> MyColumnAddress = "C:C" >> >>> >> >>> Set MyColumn = Range(MyColumnAddress) ' After the Set command the >>> range >> >>> WILL be effected by inserted columns; so re-set just before use if columns >> >>> could have been added/deleting since the last Set command. >> >>> >> >>> >> >>> >> >>> If you want to use a defined name, this will always refer to column C: >> >>> >> >>> Names.Add "MyColumn","=INDIRECT(""C:C"")" >> >>> >> >>> >> >>> >> >>> Hope this helps! >> >>> >> >>> Asa >> >>> >> >>> >> >>> >> >>> -----Original Message----- >> >>> From: tangledweb [mailto:domainqu...@gmail.com] >> >>> Sent: Monday, March 19, 2012 11:58 AM >> >>> To: Asa Rossoff >> >>> Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using >> >>> column names for looping >> >>> >> >>> >> >>> >> >>> thanks, sure wish there was a way to have it be position relative so >> >>> >> >>> could insert new columns without affecting the name on column C but >> >>> >> >>> does not appear to be the case >> >>> >> >>> >> >>> >> >>> On Mar 18, 5:19 pm, "Asa Rossoff" <a...@lovetour.info> wrote: >> >>> >> >>>> You can name the entire column as well; >> >>> >> >>>> >> >>> >> >>>> range("C:C").name="mycolumn" ' create workbook-level name >> >>> >> >>>> >> >>> >> >>>> then reference it as: >> >>> >> >>>> >> >>> >> >>>> range("mycolumn") >> >>> >> >>>> >> >>> >> >>>> also works as/in a cell formula: >> >>> >> >>>> >> >>> >> >>>> { =mycolumn } >> >>> >> >>>> >> >>> >> >>>> =match("findthis",mycolumn,0) >> >>> >> >>>> >> >>> >> >>>> evaluate("mycolumn") ' formula eval from vba >> >>> >> >>>> >> >>> >> >>>> [mycolumn] ' formula eval from vba >> >>> >> >>>> >> >>> >> >>>> Asa >> >>> -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
