VBA: How to reference variables using the TextJoin formula?

[Coyotex3]

Hello,

How could I reference Variables properly using VBA? Let's say I have a Variable named "Length" and I wanted to use the TextJoin formula within VBA to reference the variables "Length" and the variable "i". How could I do it?

I played around with a few variations but could not figure it out. This is what I have so far, which is wrong:

"=TEXTJOIN(""-"",,  & length &  , & Range("A" & i) & )"

Any help would be greatly appreciated.

 

[JGordon11]

Why not just use the concatenation operator: &.

myText = length & "-" & Range("A" & i)

If you need to use the TEXTJOIN function for some reason (it's a worksheet function not a VBA function, Join is the VBA function) you need to pass it an array of text to join as below. Note the test sub below overwrites cell A1 so run it in a test worksheet.

Sub test()
    Dim myText As String, length, i As Long, arr
    i = 1
    length = 33
    [A1] = "meters"
    myText = length & "-" & Range("A" & i)
    MsgBox "using concatenation operator ""&"": length & ""-"" & Range(""A"" & i)" & Chr(10) & Chr(10) & myText
    
    arr = Array(length, Range("A" & i))
    myText = Join(arr, "-")
    MsgBox "using VBA function: Join(arr, ""-"")" & Chr(10) & Chr(10) & myText
    
    myText = Application.TextJoin("-", , arr)
    MsgBox "using worksheet function: Application.TEXTJOIN(arr, ,""-"")" & Chr(10) & Chr(10) & myText
End Sub

 

[Coyotex3]

Why not just use the concatenation operator: &.

myText = length & "-" & Range("A" & i)

If you need to use the TEXTJOIN function for some reason (it's a worksheet function not a VBA function, Join is the VBA function) you need to pass it an array of text to join as below. Note the test sub below overwrites cell A1 so run it in a test worksheet.

Sub test()
    Dim myText As String, length, i As Long, arr
    i = 1
    length = 33
    [A1] = "meters"
    myText = length & "-" & Range("A" & i)
    MsgBox "using concatenation operator ""&"": length & ""-"" & Range(""A"" & i)" & Chr(10) & Chr(10) & myText
  
    arr = Array(length, Range("A" & i))
    myText = Join(arr, "-")
    MsgBox "using VBA function: Join(arr, ""-"")" & Chr(10) & Chr(10) & myText
  
    myText = Application.TextJoin("-", , arr)
    MsgBox "using worksheet function: Application.TEXTJOIN(arr, ,""-"")" & Chr(10) & Chr(10) & myText
End Sub

Perfect!

It makes more sense to use the concatenation operator. I was essentially just trying to join a variable with a cell reference. Admittedly I always just use the macro recorder to get the VBA equivalent but it always uses FormulaR1C1 etc.

Thank you for the workaround! Much better and easier to use.