VBA to read table contents

[Ray Bailey]

Hi All,

I have a number of files, which I've converted to tables, that have same headings but in different sequence dependant on usage. I also have a number bespoke functions.

Is there anyway that I can get a function to read the contents of a table.

Example

File A

Status	Start Date	End Date	Current Position
Started	01-Jun-20		=z_Function()
Ended	02-Jun-20	19-Jun-20	=z_Function()

Seems simple so far but another file could have 30 columns with Status, Start Date & End Date anywhere.

In z_Function I want to put

If [@[Status]] = "Started" Then
z_Function = "Started on " & [@[Start Date]]
Else
z_Function = "Ended on " & [@[End Date]]
End If

It's a bit more complex than that but hopefully will give an idea of what I need.

Thanks In Advance

 

[Ray Bailey]

Darren,

That's exactly what I need and I think I understand it. One of the functions required 15 dates and I didn't really fancy that. Putting into VBA is okay but for users to use ad-hoc is a bit too much.

Just put a quick version in to pickup two dates and return the difference. Works perfect.

Have a some followup questions if you don't mind, as I need functions to work on various tables.

1. How do I find the name of the table the function has been entered into?

2. Can I easily check if a column exists in a table? I currently select row 1, do a find and check err returned.

3. Is this an efficient way of putting formulas into tables, or is there anything easier.

    Range("Jobs_Table[[#Headers],[TESTING11]]").Select
    ActiveCell.Offset(1, 0).Formula = "=Return_StartDate()"

Cheers
Ray

 

[Darren Bartrup]

Didn't realise the formula would be entered into the table - previous code worked if it was entered on the same row as the table.
This code will look at the table the formula is entered to. Might be worth adding Application.Volatile at the start of the code as well.

Public Function Difference() As Variant

    If TypeName(Application.Caller) = "Range" Then
        Dim src As Range
        Set src = Application.Caller
        
        If Not src.ListObject Is Nothing Then
            Dim lo As ListObject
            Set lo = src.ListObject
            
            'Difference = lo.ListColumns("Start Date").DataBodyRange.Rows(src.Row - lo.Range.Row)
            
             Difference = lo.ListColumns("End Date").DataBodyRange.Rows(src.Row - lo.Range.Row) - _
                          lo.ListColumns("Start Date").DataBodyRange.Rows(src.Row - lo.Range.Row)
              
        Else
            Difference = CVErr(xlErrRef)
        End If
    Else
        Err.Raise vbObjectError + 513, , "Must be a cell range."
    End If

End Function