C is still an important language.. I started out on BASIC, then Clipper and Pascal in the 80s. Learned VB in the 90s as an obvious leap from my most comfortable BASIC background, and besides, I had a need to do Office programming. Played a little with C but never took my programming seriously enough/had a strong motivation to really study it.
"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?" The Set command is always needed to assign an object (technically a pointer to an object) to a variable. A variable assignment without Set is actually implicitly using the Let command, which assigns a value, never an object, to a variable. -----Original Message----- From: Domain Admin [mailto:domainqu...@gmail.com] Sent: Monday, March 19, 2012 6:08 PM To: Asa Rossoff Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping 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 < <mailto:a...@lovetour.info> 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> 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 < <mailto:a...@lovetour.info> 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> 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" < <mailto:a...@lovetour.info> 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
