Custom function, that works with structured references in Excel tables

Question

How to write UDF in Visual Basic for Applications that can work with structured references like this:

=UDF([Colomn1])

or this:

=UDF(if(len([Colomn1])>1,[Colomn1]))

Any ideas?

@Rory, this function work with range, but not work with reference to column. Result values are repeated.

Function udff(sRange As Range) As Variant
    Dim valueArr As Variant
    valueArr = sRange.Value
    ReDim resArr(LBound(valueArr, 1) To UBound(valueArr, 1), LBound(valueArr, 2) To UBound(valueArr, 2))
    For i = LBound(valueArr, 1) To UBound(valueArr, 1)
        For j = LBound(valueArr, 2) To UBound(valueArr, 2)
            resArr(i, j) = valueArr(i, j) & "!"
        Next j
    Next i
    udff = resArr
End Function

When you say `len(Column1)` ,What does that mean?rows count or something else.You can define your required functions using `Function` — Neelesh, Jun 28 '16 at 15:07
A structured reference simply returns a range, so it's the same as any other UDF. — Rory, Jun 28 '16 at 15:21
This should be in the original question using edit. Not in the comments — Scott Craner, Jun 28 '16 at 17:28

score 1 · Answer 1 · 2016-06-29T02:54:03.220

1

Your syntax appears wrong. [Colomn1] should be [@Colomn1] (and possibly spelled correctly).

Function udf(rng As Range)
    udf = rng * 2
End Function

Do not confuse the ability to use associative full column references with true worksheet range references (e.g. A1 or A:A) with ListObject (aka structured) table references.

edited Jun 29 '16 at 02:54

answered Jun 28 '16 at 15:34

Synax is not wrong. Built-in functions work with [Colomn1] well. [@Colomn1] is slower then [Colomn1], because in first one Excel loops values, in secont Range passed as a parameter – Aleksandr Gavrilov Jun 28 '16 at 16:48
See addendum added. – Jun 29 '16 at 02:54

Custom function, that works with structured references in Excel tables

1 Answers1