Using tables in UDF's in Excel 2007

Question

I am writing a UDF for Excel 2007 which I want to pass a table to, and then reference parts of that table in the UDF. So, for instance my table called "Stock" may look something like this:

Name            Cost            Items in Stock

Teddy Bear    £10              10

Lollipops         20p              1000

I have a UDF which I want to calculate the total cost of all the items left in stock (the actual example is much more complex which can't really be done without very complex formula)

Ideally the syntax of for the UDF would look something like

TOTALPRICE(Stock)

Which from what I can work out would mean the UDF would have the signature

Function TOTALPRICE(table As Range) As Variant

What I am having trouble with is how to reference the columns of the table and iterate through them. Ideally I'd like to be able to do it referencing the column headers (so something like table[Cost]).

Answer 1

This is very basic (no pun intended) but it will do what you describe. For larger tables it may become slow as under the hood it's going back and forth between the macro function and the worksheet, and that kind of activity adds up.

It assumes that you have one row of headers and one column of names (hence the For loop variables starting from 2).

There are all kinds of things that might be necessary - we can save those for another question or another round on this one.

Note that the function returns a "Variant", btw...

Public Function TotalPrice(table As Range) As Variant

Dim row As Long, col As Long
Dim total As Double

    For row = 2 To table.Rows.Count
        For col = 2 To table.Columns.Count
            TotalPrice = TotalPrice + table.Cells(row, col) * table.Cells(row, col + 1)
        Next
    Next

End Function

Answer 2

Note: I dont have Excel 2007 and I am trying to write this using MSDN doc on the web.
Looks like the range will have ListColumns collection

So, the syntax could be table.ListColumns("Cost").
Does this work?