CODEWITHSUNDEEP
vbaexcel
Cyberpro
Cyberpro

Reputation: 47

How to pass a table name to a function in Excel

I have various settings tables in Excel, each with two column headings Parameter and Value.

I want to pass a specific table's name to a function getParameter that looks up a specific parameter's name in that table, returns the associated parameter's value, and performs all error handling, e.g. with the following code segment:

Function getParameter(....  
...  
Dim paramterValue as Variant  
With Application  
    parameterValue = .Index([tableName[Value], .Match("parameterName",  [tableName[Parameter], 0))  
    If Not IsError(parameterValue) then  
        ...  
    Else  
        ...  
    End If  
End With

End Function

How do I define the appropriate function arguments and call the function?

Upvotes: 2

Views: 3448

Answers (3)

Cyberpro
Cyberpro

Reputation: 47

@R3uk Axel Richter's code is sufficient, but yours also works.

Upvotes: 0

R3uK
R3uK

Reputation: 14537

I'll try like this, identifying the sheet and the ListObject corresponding to your TableName :

Function getParameter(ByVal tableName As String, ByVal parameterName As String) As Variant
Dim parameterValue As Variant
Dim RgVal As Range
Dim wS As Worksheet
Dim LOTable As ListObject

Application.Volatile

Set wS = Evaluate(tableName).Parent
Set LOTable = wS.ListObjects(tableName)
Set RgVal = LOTable.DataBodyRange

With Application.WorksheetFunction
    parameterValue = .Index(RgVal.Columns(2), .Match(parameterName, RgVal.Columns(1), 0))
End With 'Application.WorksheetFunction

If Not IsError(parameterValue) Then
    getParameter = parameterValue
Else
    '...
    DoEvents
    getParameter = CStr(parameterValue)
End If

End Function

Call in VBA :

Sub test_GetParameter()
    Debug.Print getParameter("Table1", "testParam")
End Sub

Call in Excel :

= getParameter("Table1", "testParam")

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61870

Tables are in VBA selectable as ListObject Object. But those objects are on worksheet scope only. So we must know the worksheet on which the table is placed on to get it using wrksht.ListObjects(tableName).

To be more flexible, we could using Evaluate to evaluate the structured references:

Public Function getParameter(tableName As String, parameterName As Variant) as Variant

Dim parameterValue As Variant
Dim oRangeTValues As Range
Dim oRangeTParameters As Range

Set oRangeTValues = Evaluate("" & tableName & "[Value]")
Set oRangeTParameters = Evaluate("" & tableName & "[Parameter]")
With Application
    parameterValue = .Index(oRangeTValues, .Match(parameterName, oRangeTParameters, 0))
    If Not IsError(parameterValue) Then
        getParameter = parameterValue
    Else
        getParameter = CStr(parameterValue)
    End If
End With

End Function

This will be usable on all worksheets since the table names are on workbook scope in reality.

This is supposed to be used as User Defined Function using a cell formula like =getParameter("TableName","Parameter").

Upvotes: 1

Related Questions