RE: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping
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
RE: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping
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:
RE: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping
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 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
-Original Message-
From: mailto:excel-macros@googlegroups.com
excel-macros@googlegroups.com [ mailto:excel-macros@googlegroups.com
mailto:excel-macros@googlegroups.com]
On Behalf Of dguillett1
Sent: Sunday, March 18, 2012 1:26 PM
To: mailto:excel-macros@googlegroups.com excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using
column names for looping
the simplest is
sub nameit
cells(7,4).name=whateveryouwanttonameit
end sub
=whaeveryounamedit
Don Guillett
Microsoft MVP Excel
SalesAid Software
mailto:dguille...@gmail.com mailto:dguille...@gmail.com
mailto:dguille...@gmail.com dguille...@gmail.com
-Original Message-
From: tangledweb
Sent: Sunday, March 18, 2012 5:22 AM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Need help with VBAfor Excel naming and using
column names for looping
I am trying to understand how to name a column of data and be able to
reference the data by that name. Ideally I could set the name
programatically but if it needs to be done through the ribbon command
that is acceptable.
Ideally inserting a new column before the named column will not affect
that column's name.
The simplest example of
=
Sub Macro1()
Dim x as long
Const ExitValue = 4
x = Cells(6, ExitValue).Value
Cells(7, ExitValue) = x
End Sub
===
Functions but for some reason I can not see the value of x in the
immediate window. I just get nothing back if I print x.
But is this really the best wah to do this? It seems inelegant at
best. And certainly if I insert a new column 3 I need to change this
code to
Const ExitValue = 5
--
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
mailto:excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com
mailto:excel-macros@googlegroups.com excel-macros@googlegroups.com
--
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
RE: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping
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 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 [
Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping
the simplest is sub nameit cells(7,4).name=whateveryouwanttonameit end sub =whaeveryounamedit Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com -Original Message- From: tangledweb Sent: Sunday, March 18, 2012 5:22 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping I am trying to understand how to name a column of data and be able to reference the data by that name. Ideally I could set the name programatically but if it needs to be done through the ribbon command that is acceptable. Ideally inserting a new column before the named column will not affect that column's name. The simplest example of = Sub Macro1() Dim x as long Const ExitValue = 4 x = Cells(6, ExitValue).Value Cells(7, ExitValue) = x End Sub === Functions but for some reason I can not see the value of x in the immediate window. I just get nothing back if I print x. But is this really the best wah to do this? It seems inelegant at best. And certainly if I insert a new column 3 I need to change this code to Const ExitValue = 5 -- 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 -- 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
RE: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping
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
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of dguillett1
Sent: Sunday, March 18, 2012 1:26 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using
column names for looping
the simplest is
sub nameit
cells(7,4).name=whateveryouwanttonameit
end sub
=whaeveryounamedit
Don Guillett
Microsoft MVP Excel
SalesAid Software
mailto:dguille...@gmail.com dguille...@gmail.com
-Original Message-
From: tangledweb
Sent: Sunday, March 18, 2012 5:22 AM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Need help with VBAfor Excel naming and using
column names for looping
I am trying to understand how to name a column of data and be able to
reference the data by that name. Ideally I could set the name
programatically but if it needs to be done through the ribbon command
that is acceptable.
Ideally inserting a new column before the named column will not affect
that column's name.
The simplest example of
=
Sub Macro1()
Dim x as long
Const ExitValue = 4
x = Cells(6, ExitValue).Value
Cells(7, ExitValue) = x
End Sub
===
Functions but for some reason I can not see the value of x in the
immediate window. I just get nothing back if I print x.
But is this really the best wah to do this? It seems inelegant at
best. And certainly if I insert a new column 3 I need to change this
code to
Const ExitValue = 5
--
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 mailto:excel-macros@googlegroups.com
excel-macros@googlegroups.com
--
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 mailto:excel-macros@googlegroups.com
excel-macros@googlegroups.com
--
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
