Can VBA use array constants

[Walter Briscoe]

This question follows from
<http://groups.google.com/g/8077ff07/t/be2a4cfbf68813ee/d/c18cd6d29d06f79
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.

 

[Peter T]

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