How do i use a table as input to a function in Excel vba?

Question

I have a function that i would like to make more general. But i can't get the last part of the function to work as input to the function.

how do i use tables as input to a funktion? I would like to break out the table name and use it as input so i can use the function in other contexts.

Function GetTotalHours("Tablename?" As ??, columnNumber As Integer) As Integer

    For Each Row In [Tablename].Rows

        getTotalHours = getTotalHours + DateTime.Hour(Row.Columns(columnNumber).Value)

    Next

End Function

can i make this with a string some how?

This is a great link, have a look: jkp-ads.com/articles/Excel2007TablesVBA.asp — Ioannis
– Ioannis, Commented Oct 20, 2013 at 11:16

KekuSemau · Accepted Answer · 2013-10-20 11:59:27Z

2

The tables exist as named ranges, you can access these directly with .Range(name).

Function GetTotalHours(tableName As String, columnNumber As Integer) As Integer
    Dim r As Range
    Dim c As Range

    Set r = ActiveSheet.Range(tableName).Columns(columnNumber)
    For Each c In r.Cells
        GetTotalHours = GetTotalHours + DateTime.Hour(c.Value)
    Next
End Function

And you could also pass the heading instead of the number:

Function GetTotalHoursByHeading(tableName As String, columnName As String) As Integer
    Dim r As Range
    Dim c As Range

    Set r = ActiveSheet.Range(tableName & "[" & columnName & "]")
    For Each c In r.Cells
        GetTotalHoursByHeading = GetTotalHoursByHeading + DateTime.Hour(c.Value)
    Next
End Function

edited Oct 20, 2013 at 11:59

answered Oct 20, 2013 at 11:05

KekuSemau

6,8414 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sukakka Over a year ago

I still can't get this to work. Are you referring to a table as a sheet? because i mean i specific table on a sheet. not the entire sheet. like if i have a tale named trainingData on Sheet1.

KekuSemau Over a year ago

Yes sorry, I really thought you mean tables = worksheets. I have edited the answer.

Dick Kusleika · Accepted Answer · 2013-10-20 16:27:34Z

1

If you want to avoid a UDF, you could use this worksheet formula

=SUMPRODUCT(HOUR(Table1[Hours]))

answered Oct 20, 2013 at 16:27

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Collectives™ on Stack Overflow

How do i use a table as input to a function in Excel vba?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related