Syntax Question - Passing a Table Name & Header Name as a variable

[idlewyld89]

I'm developing an excel application for a variety of users, and as a result several routines require the adaptability to test the location of several key columns of data. The goal with this small function is to identify the column index of a specified column via the header value.

By assigning static values, this function works 100% as intended. However, I am having trouble with the syntax of passing several of the values as dynamic variables.

Here is the basic premise:

Private Function ReturnHeaderIndex(ByVal Wrkbk As Workbook, ByVal TableName As String, ByVal Criteria As String) As Integer' References:
' Dependents:


' ~~>


ReturnHeaderIndex = Wrkbk.ListObject("TableName[Criteria]").Column


End Function

Where Wrkbk is previously defined as a workbook requested of the user and stored as a workbook object, TableName is just hard-coded as a value (typically static, but referenced elsewhere... just wanted it to be easy to update at a later time if necessary) and Criteria is also defined previously as an array and incremented through in a separate sub.

Therefore, the call would be approximately:

ReturnHeaderIndex(GMDirectory, "Directory", ColumnNames(i))

Again, to be clear, GMDirectory is previously defined as a Workbook object (global variable) and ColumnNames(i) is an array of length 3 that is looped through to UBOUND in order to VLOOKUP against the users directory.

Any assistance would be greatly appreciated, as I've been somewhat stumped on this. Have found several useful sites that reference the general structure when working with tables, but none have examples of introducing parameters as variables.

 

[wideboydixon]

You make no reference to the sheet on which the table appears but I think you could try:

ReturnHeaderIndex = Wrkbk.ActiveSheet.ListObjects(TableName).ListColumns(Criteria).Range.Column

WBD

 

[idlewyld89]

@wideboydixon: Thanks, I had done so in a previous iteration but took it out during the course of troubleshooting. Thanks for pointing out that is necessary.

So, the following section of code is the correct way to pass a variable through as a table name and header value? Does the .ListColumns(Criteria) portion refer specifically to the header value, or a column value?

[COLOR=#333333].ListObjects(TableName).ListColumns(Criteria).Range.Column[/COLOR]

Additionally, is it possible to simply refer to a listobject directly independent of the worksheet it's found in? That would be ideal (and what I was experimenting with above).

Each user keeps their own format of directory... which is frustrating. The fewer changes I ask of them the better. It's both easy and organizationally indifferent to simply rename a table as opposed to changing the sheet's name as well.

Would like to minimize the impact on other reporting if possible, and not sure if they may have reports that depend on that sheet name... which is different across users... haha, frustrating I know.

Luckily none of them are using table names as meaningful references (some don't even have them formatted as tables) and therefore that change shouldn't affect anything meaningful.

 

[wideboydixon]

If you don't know which sheet the list object belongs to then you'll need to iterate over the sheets until you find it.

Private Function ReturnHeaderIndex(ByVal Wrkbk As Workbook, ByVal TableName As String, ByVal Criteria As String) As Integer ' References:

Dim Wrksh As Worksheet
Dim Lstobj As ListObject

On Error Resume Next

ReturnHeaderIndex = 0

For Each Wrksh In Wrkbk.Worksheets
    Set Lstobj = Wrksh.ListObjects(TableName)
    If Not Lstobj Is Nothing Then
        ReturnHeaderIndex = Wrksh.ListObjects(TableName).ListColumns(Criteria).Range.Column
        Exit For
    End If
Next Wrksh

End Function

WBD

 

[Marcelo Branco]

Maybe including the sheet name as a new argument.

Private Function ReturnHeaderIndex(ByVal Wrkbk As Workbook, [COLOR=#0000ff]ByVal sh As String[/COLOR], _
        ByVal TableName As String, ByVal Criteria As String) As Integer
    
    ReturnHeaderIndex = Wrkbk.Sheets(sh).ListObjects(TableName).ListColumns(Criteria).Index
End Function

Remark: the Index property returns the relative position of the column, not the the Column number. If you do want the Column number replace Index by Range.Column

M.

 

[idlewyld89]

@Marcelo Branco: Thanks for the solution... I'm going to test both and decide what level of flexibility is needed... IMHO simplicity is preferable where possible.

Alright, now the point of this was to facilitate a VLOOKUP return independent of what position the target column holds. My vlookup routine currently has a static reference to the column... I'm trying to implement the above function.

Private Sub vLookUp()' References:
' Dependents:


' ~~>


Dim ColumnNames(2) As String
Dim fillcol As Long


i = 0


Do While i <= UBound(ColumnNames)
On Error Resume Next


LastRow = Worksheets(MainSheet).Range("A" & Rows.Count).End(xlUp).Row
ColumnNames(0) = "Location (4-Digit)"
ColumnNames(1) = "BM"
ColumnNames(2) = "AM"


If TestHeader(ColumnNames(i), "FleetStatusDetail") = False Then


    Columns(2).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Cells(1, 2).Value = ColumnNames(i)
    
    fillcol = Range("FleetStatusDetail[[#Headers],[ColumnNames(i)]]").Column
    
    If i = 0 Then
        
        fillcol = Range("FleetStatusDetail[[#Headers],[Location (4-Digit)]]").Column
        Cells(2, fillcol).FormulaR1C1 = "=MID(RC[1], 1, 4)*1"
    
    ElseIf i = 1 Then
    
        fillcol = Range("FleetStatusDetail[[#Headers],[BM]]").Column
        Cells(2, fillcol).FormulaR1C1 = "=VLOOKUP(RC[1], 'GM Directory.xlsm'!GM_Directory[#All], 8, FALSE)"
        
    ElseIf i = 2 Then
    
        fillcol = Range("FleetStatusDetail[[#Headers],[AM]]").Column
        Cells(2, fillcol).FormulaR1C1 = "=VLOOKUP(RC[2], 'GM Directory.xlsm'!GM_Directory[#All], 5, FALSE)"


    End If


End If


    i = i + 1


Loop


Cells.EntireColumn.AutoFit


End Sub

This line in particular will need to be changed:

Cells(2, fillcol).FormulaR1C1 = "=VLOOKUP(RC[1], 'GM Directory.xlsm'!GM_Directory[#All], 8, FALSE)"

It's not clear however how you would implement a function output here and have it referenced correctly.

 

[idlewyld89]

This doesn't appear to cut it... columns are still created but no VLOOKUP data is filled down. Oddly, no errors though.

Sorry for the multi-post, couldn't edit prior:

Cells(2, fillcol).FormulaR1C1 = "=VLOOKUP(RC[1], 'GMDirectory'!GMDirTable[#All], ReturnHeaderIndex(GMDirectory, TblNm, ColumnNames(i)), FALSE)"

 

[idlewyld89]

@wideboydixon: When I go to test your function, I keep getting a compile error... I'm not passing the Workbook correctly when calling the function, but I've tried every combination of (), "", '', and variables I can think of... it just won't take it.

I know it works, because when I hard-code the workbook name into the function, it works properly... but it won't take the same "GM Directory.xlsm" syntax when passing it through the call within an external sub.

Dim GMDirectoryWbk As WorkbookDim GMDirectoryWrksht As Worksheet
Dim GMDirectoryTbl As ListObject


Dim SourceData As Workbook
Dim SourceDataSheet As String


Dim ActingWorkbook As Workbook
Dim ActingWorksheet As Worksheet
Dim ActingListObject As ListObject


Dim MainSheet As String


Sub TestFunction()


Dim test As Integer: test = ReturnHeaderIndex("GM Directory.xlsm", "GMDirectory", "AM")


MsgBox test


End Sub


Private Function ReturnHeaderIndex(ByVal Wrkbk As Workbook, ByVal TableNm As String, ByVal Criteria As String) As Integer
' References:
' Dependents:


' ~~> Tests for the column number (absolute) within a defined table independent of sheet location


Dim Wrksht_local As Worksheet


On Error Resume Next


ReturnHeaderIndex = 0


For Each Wrksht_local In Workbooks(Wrkbk).Worksheets


    Set GMDirectoryTbl = Wrksht_local.ListObjects(TableNm)
    
    If Not GMDirectoryTbl Is Nothing Then
    
        ReturnHeaderIndex = Wrksht_local.ListObjects(TableNm).ListColumns(Criteria).Range.Column
        GMDirectoryWrksht = Wrksht_local.Name
        Exit For
        
    End If
    
Next Wrksht_local


End Function

 

[wideboydixon]

You're passing a string; not a Workbook object.

WBD