Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
shortening code by defining variables using an array?
hi all,
I'm trying to create a quick/short code approach for defining a group of previously declared (as long) variables. These variables identify the correct column for various types of data based on single cell named ranges in the header row and are defined for use later in a macro. My current working code is Concat = range("Concat").column & so on with a separate line of code for each of about 15 different variables. Is it possible to shorten the code? Below are my unsuccessful attempts so far, where I have tried to use an array with the thought of wrapping it in a For Each structure once I get it working... Option Explicit Sub CreatingVariables() Dim HeaderCols Dim Concat As Long dim factory as long 'etc etc... Dim NamedRange As Name HeaderCols = Array("Concat", "Factory", "Plant") 'etc etc 'HeaderCols(0) = Range(HeaderCols(0)).Column 'Evaluate(HeaderCols(0)) = Range(HeaderCols(0)).Column 'cstr(evaluate(HeaderCols(0)) = Range(HeaderCols(0)).Column Cells(4, Concat).Select 'the code currently errors here 'another considered but not yet investigated approach For Each NamedRange In ActiveWorkbook.Names With NamedRange ' .Name = right(.RefersTo, '??? End With Next NamedRange ''fyi, recorded code when initially creating the named range 'ActiveWorkbook.Names.Add Name:="Factory", RefersToR1C1:="=Data!R4C2" End Sub Thanks in advance Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
shortening code by defining variables using an array?
This code will only work if you have already assigned Defined Names to the
proper cells in the worksheet: Sub ColumnLabling() Dim ColumnIdNumbers(3) As Long ColumnHeaders = Array("Concat", "Factory", "Plant") For i = 0 To 2 ColumnIdNumbers(i) = Range(ColumnHeaders(i)).Column MsgBox (ColumnIdNumbers(i)) Next End Sub -- Gary''s Student - gsnu200773 "broro183" wrote: hi all, I'm trying to create a quick/short code approach for defining a group of previously declared (as long) variables. These variables identify the correct column for various types of data based on single cell named ranges in the header row and are defined for use later in a macro. My current working code is Concat = range("Concat").column & so on with a separate line of code for each of about 15 different variables. Is it possible to shorten the code? Below are my unsuccessful attempts so far, where I have tried to use an array with the thought of wrapping it in a For Each structure once I get it working... Option Explicit Sub CreatingVariables() Dim HeaderCols Dim Concat As Long dim factory as long 'etc etc... Dim NamedRange As Name HeaderCols = Array("Concat", "Factory", "Plant") 'etc etc 'HeaderCols(0) = Range(HeaderCols(0)).Column 'Evaluate(HeaderCols(0)) = Range(HeaderCols(0)).Column 'cstr(evaluate(HeaderCols(0)) = Range(HeaderCols(0)).Column Cells(4, Concat).Select 'the code currently errors here 'another considered but not yet investigated approach For Each NamedRange In ActiveWorkbook.Names With NamedRange ' .Name = right(.RefersTo, '??? End With Next NamedRange ''fyi, recorded code when initially creating the named range 'ActiveWorkbook.Names.Add Name:="Factory", RefersToR1C1:="=Data!R4C2" End Sub Thanks in advance Rob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
shortening code by defining variables using an array?
Thanks Gary's Student, yes, the names are defined in the ss. Your suggestion
is going in the right direction but it's not quite what I'm hoping for yet (as far as readibility of later code goes - see *). Instead of identifying the column using the index number from the array in "ColumnIdNumbers(0)" to equal 1 (for the first column) is there any way that I can end up with "Concat = 1" for use in code such as...? * examples of possible uses: dim Concat as Long .... ..autofilter Field:=Concat, Criteria1:="=xyz" 'cf .autofilter Field:=ColumnIdNumbers(0), Criteria1:="=xyz" or with range(cells(5,Concat),cells(lastrow,Concat)).Speci alCells(xlCellTypeVisible) 'format etc & ..value = .value end with I'm sorry I can't explain this very well - I guess I'm really after a "translation", from the indexed array variable into the single Long variable which has the same name as the defined name. The question came about b/c I've been optimising someone else's code & my use of named ranges has evolved from the original code which was looping through each column until it found the word Concatenate in the header row & asigning the column number at that point. I initially changed this to use a Find function rather than a loop and when I realised that named ranges in a Template file would be quicker still I ended up with the below: "Concat = range("Concat").column" "Factory = range("Factory").column" etc etc when I looked at this & saw the duplication on each side of the equals sign I started wondering if it could be shortened even more but I understand if that's not possible & will stick with my 15 lines of code... thanks Rob Gary''s Student wrote: This code will only work if you have already assigned Defined Names to the proper cells in the worksheet: Sub ColumnLabling() Dim ColumnIdNumbers(3) As Long ColumnHeaders = Array("Concat", "Factory", "Plant") For i = 0 To 2 ColumnIdNumbers(i) = Range(ColumnHeaders(i)).Column MsgBox (ColumnIdNumbers(i)) Next End Sub -- Gary''s Student - gsnu200773 "broro183" wrote: hi all, I'm trying to create a quick/short code approach for defining a group of previously declared (as long) variables. These variables identify the correct column for various types of data based on single cell named ranges in the header row and are defined for use later in a macro. My current working code is Concat = range("Concat").column & so on with a separate line of code for each of about 15 different variables. Is it possible to shorten the code? Below are my unsuccessful attempts so far, where I have tried to use an array with the thought of wrapping it in a For Each structure once I get it working... Option Explicit Sub CreatingVariables() Dim HeaderCols Dim Concat As Long dim factory as long 'etc etc... Dim NamedRange As Name HeaderCols = Array("Concat", "Factory", "Plant") 'etc etc 'HeaderCols(0) = Range(HeaderCols(0)).Column 'Evaluate(HeaderCols(0)) = Range(HeaderCols(0)).Column 'cstr(evaluate(HeaderCols(0)) = Range(HeaderCols(0)).Column Cells(4, Concat).Select 'the code currently errors here 'another considered but not yet investigated approach For Each NamedRange In ActiveWorkbook.Names With NamedRange ' .Name = right(.RefersTo, '??? End With Next NamedRange ''fyi, recorded code when initially creating the named range 'ActiveWorkbook.Names.Add Name:="Factory", RefersToR1C1:="=Data!R4C2" End Sub Thanks in advance Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify code for multiple sheets-Help defining array | Excel Programming | |||
Defining Variables with Same Name in Different Worksheets | Excel Discussion (Misc queries) | |||
Multiple Criteria - Shortening Code | Excel Programming | |||
VBA defining variables | Excel Discussion (Misc queries) | |||
Help with shortening/cleaning some code please | Excel Programming |