Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can VBA use array constants
This question follows from
<http://groups.google.com/g/8077ff07/...18cd6d29d06f79 c. In VBA, I want to lookup a column number which refers to a London Underground line name. My first version using VLOOKUP was: Dim colIndex ' Translation of Line to a column number ActiveCell.Formula = "=VLOOKUP(""" & Line & """," _ & "{""Bakerloo"",5;""Central"",6, ...},2,TRUE)" colIndex = ActiveCell.Value My second version using MATCH used this line: ActiveCell.Formula = "=4+MATCH(""" & Line & """," _ & "{""Bakerloo"",""Central"", ...}, 1)" (With both those versions both "Central" and "Central line" match.) My third version using the VBA find function used the following code: Dim lines As Range ' at module level Set lines = Range("E1:O1") ' in a public subroutine colIndex = lines.Find(Line).Column (With this version, "Central line" (unsurprisingly) does not match.) Is it possible to use an array constant for the range in such code? If so, how? I got a 1004: "Method 'Range' of object '_Global' failed" with Set lines = Range(Array("Bakerloo", "Central", "Circle", ...)) As it happens, I am happy with the third method. I am not happy with my ignorance. ;) -- Walter Briscoe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can VBA use array constants
Not sure what you are really trying to do. However if you want to hardcode
an array of constants you could do something like this Sub test() Dim sLine As String Dim aLines As Variant Const LINES As String = "Bakerloo,Central" aLines = Split(LINES, ",") ' note lbound is zero sLine = "Central" For i = 0 To UBound(aLines) If UCase$(aLines(i)) = UCase$(sLine) Then colIndex = i + 1 ' adjust for Lbound zero Exit For End If Next If colIndex Then colIndex = colIndex + 4 ' add the 4 (why?) End If MsgBox colIndex End Sub A simple loop will be faster than the overhead of calling a worksheet function. Probably better though to put all your station names in a list in cells, perhaps in a hidden sheet, and in a named range, eg "myLines" on sheet1 aLines = ThisWorkbook.Worksheets("sheet1").Range("myLines") .Value aLines will now be a two dimensional array, so need to change the code above slightly from If UCase$(aLines(i)) = UCase$(sLine) Then to If UCase$(aLines(i,1)) = UCase$(sLine) Then Regards, Peter T "Walter Briscoe" wrote in message ... This question follows from <http://groups.google.com/g/8077ff07/...18cd6d29d06f79 c. In VBA, I want to lookup a column number which refers to a London Underground line name. My first version using VLOOKUP was: Dim colIndex ' Translation of Line to a column number ActiveCell.Formula = "=VLOOKUP(""" & Line & """," _ & "{""Bakerloo"",5;""Central"",6, ...},2,TRUE)" colIndex = ActiveCell.Value My second version using MATCH used this line: ActiveCell.Formula = "=4+MATCH(""" & Line & """," _ & "{""Bakerloo"",""Central"", ...}, 1)" (With both those versions both "Central" and "Central line" match.) My third version using the VBA find function used the following code: Dim lines As Range ' at module level Set lines = Range("E1:O1") ' in a public subroutine colIndex = lines.Find(Line).Column (With this version, "Central line" (unsurprisingly) does not match.) Is it possible to use an array constant for the range in such code? If so, how? I got a 1004: "Method 'Range' of object '_Global' failed" with Set lines = Range(Array("Bakerloo", "Central", "Circle", ...)) As it happens, I am happy with the third method. I am not happy with my ignorance. ;) -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Constants | Excel Worksheet Functions | |||
unions, intersections or array constants | Excel Worksheet Functions | |||
not sure on constants | Excel Programming | |||
Declaring array constants at module level | Excel Programming |