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

 

[Yongle]

Based on what you provided this simple formula (placed either inside or outside the table but in the same row) returns the required text
=Table1[@Status]&" on " &TEXT(MAX(Table1[@[Start Date]],Table1[@[End Date]]),"dd-mmm-yy")

Are you after a UserDefinedFunction that can be used like a formula or something to use within VBA

 

[Ray Bailey]

Thanks for the reply,

I'm after a UDF if possible.

I have a working one but to call it I have to put in the formula =z_Function([@[Status]],[@[Start Date]],[@[End Date]]) or =z_Function(A2,B2,C2)

I want a single UDF to find the three parameters without having to pass them.

Is it easy to get a UDF to return the result of a formula?

 

[Yongle]

If you have a working function, please post it
- I may be able to amend it

I accept that z_Function([@[Status]],[@[Start Date]],[@[End Date]]) is rather horrible
Which paramater(s) do you want inside the function?

 

[Ray Bailey]

The "test" function is

Function z_Function(sStatus As String, sStart As String, sEnd As String)
If sStatus = "Started" Then
z_Function = "Started on " & sStart
Else
z_Function = "Ended on " & sEnd
End If
End Function

My actual requirement has many dates

 

[Yongle]

Apologies, I asked my question badly ..
Let me explain and try again

Explanation
For a function to return what we want ... we ask it a question (eg put formula in cell) and provide rules to allow VBA to determine the answer (ie create the procedure)
The question ... which column header is "Start Date" in TableX
(formula = GetMeColumnNumber(Table Ref?, Look for what?)
Rule ... return what this pseudo formula returns =match("Start Date", Row1 in TableX , 0)
Expected answer ... 2


This is what I have not grasped yet (and it is fundamental) ..
Q1 what goes inside the formula in the cell ?
Q2 what EXACTLY do you expect the function to return ?

 

[Ray Bailey]

Hi Yongle, Sorry for going quiet for a while, but not been too well and then I forgot to respond.

A friend has explained that I can't do exactly what I wanted and I have to pass the parameters to the function for it to work.

Here's how he explained it.

Wrote a function that just said MsgBox ("ActiveCell.Row")

Put this in a table with ten rows and each time it returned the row that the cursor was in not 2,3,4 etc. So I can't get a function to pickup say a Start Date from the current row, I have to pass [@[Start Date]] as a parameter. which is what you said last month.

Thanks very much for your time over this.

Stay Safe
Ray

 

[Yongle]

Glad you are feeling better
Reading your reply, it looks like you do not require any further help at this time

 

[Ray Bailey]

Not at the moment All is good regarding this bit.

I'm reading up on the better ways to use tables as I'm only just starting with them.

Thanks Again

 

[Darren Bartrup]

When you say 'current row' you mean the row that the formula is on?
You could use Application.Caller:

Public Function Return_StartDate() As Variant
    
    If TypeName(Application.Caller) = "Range" Then
        Dim src As Range
        Set src = Application.Caller
    
        Dim lo As ListObject
        Set lo = Sheet1.ListObjects("Table1")
        
        If Not Intersect(lo.Range.EntireRow, src.EntireRow) Is Nothing Then
            Return_StartDate = lo.ListColumns("Start Date").DataBodyRange.Rows(src.Row - lo.Range.Row)
        Else
            'Return #REF! error if Function is not on a row aligned with the table.
            'Function needs to return Variant for this to work.
            Return_StartDate = CVErr(xlErrRef)
        End If
    Else
        Err.Raise vbObjectError + 513, , "Must be a cell range."
    End If

End Function

The Err.Raise is used to raise an error if the function is not used as a UDF.

Sub Test_Error()
    Dim a 
    a = Return_StartDate
End Sub