How to hold a UDF array formula in a variable in a vba macro?

Question

I don't have the TEXTJOIN function in my version of excel so I created a UDF of the function in my module. What I want to do is have a macro that calls TEXTJOIN but I'm having a hard time working with the array formula with variables.

My first thought was setting the cell equal to the formula through .FormulaArray but VBA didn't recognize the UDF.

Next, I tried to use a variable to hold the formula in order to call it as .FormulaArray but this runs a type mismatch as I'm not entirely sure what to dim the variable as.

This is the code that I've written but the line that returns the type mismatch is the t = TJoin(.....)

Sub TJ_1in()

Dim cell As Variant
Dim s1 As Worksheet
Dim input1 As Range
Dim output1 As Range
Dim t As Variant

Dim entry As Range

Set input1 = Range("Box_Sizes")
Set output1 = Range("Customers")
Set s1 = Worksheets("Plus-Minus 1 in.")

For Each cell In Range("range1")

    Set entry = s1.Cells(5, 6)

    t = TJoin(", ", IIf(entry = input1, output1, ""))

    With cell
        .FormulaArray = "=t"
    End With

    Set entry = entry.Offset(1, 0)

Next cell

End Sub

The reason why I want to call TEXTJOIN in a macro (with a button) rather than as an actual formula is that it refreshes a lot in my workbook and was constantly running which slowed everything down.

you will need to iterate the arrays. you cannot equate a single value to a 2d array. — Scott Craner, Jul 23 '19 at 17:27
BTW: try My TEXTJOIN UDF: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell — Scott Craner, Jul 23 '19 at 17:28
you will need to compare `entry` to each item in `input1`. if `input1` is larger than one cell you cannot do `entry = input1` — Scott Craner, Jul 23 '19 at 17:33
But to speed things up load everything into variant arrays and loop those keeping what you want. Looping ranges is slow. — Scott Craner, Jul 23 '19 at 17:33
here is another udf that may suit better: https://stackoverflow.com/questions/56858571/merge-values-of-column-b-based-on-common-values-on-column-a You would call it: `=TEXTJOINIFS(Customers,", ",Box_Sizes,F5)` — Scott Craner, Jul 23 '19 at 17:40
@ScottCraner the textjoinifs works very well and it is much easier to work with since it isn't an array function- thank you! — gobirds12, Jul 23 '19 at 18:14

How to hold a UDF array formula in a variable in a vba macro?

0 Answers0