Getting the Column Number for a String

Code Sharing
1

I need the column number for two Columns, based on their names.

The names of the Columns (in that case) are:
Date (Start) and Date (End)

I wasted time until:


Var The_Header() As String

// a. Get the Header Strings
The_Header = Split(LB.HeaderAt(ListBox.AllColumns),Chr(9))

// b. Get the Position of Date (Start)
Date_Start_Idx = The_Header.IndexOf("Date (Start)")

// b. Get the Position of Date (Start)
Date_End_Idx = The_Header.IndexOf("Date (End)")

In the code above, LB stands for the ListBox reference.

Now, I can work with the dates stored in these two Columns.

3

I have a ListBoxUtils module, and one of its handiest functions is Column:

Public Function Column(Extends lb As Listbox, Heading As String) As Integer
  
  For c As Integer = 0 To lb.LastColumnIndex
    If lb.HeaderAt(c) = Heading Then Return c
  Next
End Function

Which then allows me to also extend CellValueAt, CellTagAt, etc like this

Public Function CellValueAt(Extends lb As Listbox, Row As Integer, Heading As String) As String
  Return lb.CellValueAt(row, lb.Column(Heading))
End Function

and

Public Sub CellValueAt(Extends lb As Listbox, Row As Integer, Heading As String, Assigns Value As String)
  lb.CellValueAt(row, lb.Column(Heading)) = Value
End Sub

So I can just write

Dim s As String = myListBox.CellValueAt(row, "someheading")

or

myListBox.CellValueAt(row, "someheading") = s

Makes for clean and concise code, although you can’t have columns with the same heading and you have to be careful if you ever change the headings. But then again if you use numeric column references, you have to be careful if you ever change your column ordering, so pick your poison :slight_smile:

4

Arsenic and Old Lace :grin:

2 Likes