Excel Table Code | Get XL Column Number

Some simple code that takes a Excel table as an argument, plus the name of a column in that table, and then returns the column number within the XLT.

It’s saves hard-coding column numbers, and so still works if users move columns in your table.

It will return an error value of 0 (zero) if the matching column is not found)

VBA


'/////////////////////////////////////////////////////////////////////////////
'// Procedure:  Function XLTGetColNum
'// Author:     Duncan Salmon, https://matrix9.co.uk
'// Desc:       Returns the column number of a col in an XLT, based on the column name
'// Arguments:  XLT as ListObject - a listobject variable that represents an XLT
'//             XLTColName as string - name of the column we are looking for (plain text, NOT
'//             as a structured reference)
'// Returns:    Either the column number if found, else 0
'/////////////////////////////////////////////////////////////////////////////

Function XLTGetColNum(XLT As ListObject, XLTColName As String) As Double

On Error GoTo ErrorHandler

'   Not a valid table object
    If TypeName(XLT) <> "ListObject" Then Err.Raise 59999

'   Table has no headers so cannot check column numbers!
    If XLT.ShowHeaders = False Then Err.Raise 59999
     
'   Errors if no match found
    XLTGetColNum = Application.Match(XLTColName, XLT.HeaderRowRange, 0)    

CleanExit:
    Exit Function

ErrorHandler:
    XLTGetColNum = 0
    Resume CleanExit

End Function
q

VBA Code Use Licence

You may use this code freely in all your projects. If doing so, please add the following text/link, either as a comment in your app’s code, or as a link if posting the code elsewhere:
Code provided freely by https://matrix9.co.uk
q

Code Disclaimer

This free code is provided as is, without warranty of any kind. Matrix9 accepts no responsibility for any untoward behaviour or effects of this code, however caused. It’s free to use, but use at your own risk! 

Got any questions?

Feel free to offer any thoughts in the comments section for each page. Or get in contact with us direct if you’d prefer

Contact Us