Forum Discussion

aijatiw's avatar
aijatiw
Copper Contributor
Nov 03, 2023

An UDF for Concatenate Function

Hello I was wondering whether it is possible to achieve this result using VBA/UDF?

 

 

Basically what I wanted to do is to be able to concatenate various string from different cell and achieve the shown result without typing the concatenate formula over and over but only one function with VBA/UDF, so we can get different kind of result based on the chosen cells

  • aijatiw 

    I agree with Patrick2788 that there is little point in introducing VBA as a programming environment when worksheet formulas are sufficient.  Now that dynamic arrays and recursion have made the worksheet formulas into a Turing complete language, that covers most computational tasks.

     

    Another trick is to nest the Lambda functions to take two strings of parameters,

     

    StudentYearλ
    = LAMBDA(int, lnk, cls,
        LAMBDA(param1, param2, CONCATENATE(int, " ", param1, " ", lnk, " ", param2, " ", cls))
      )(intro, link, close)

     

    This is still a Lambda function because only one parameter string is already provided.  That allows a worksheet formula to return the appropriate message for a list of students

    = StudentYearλ(Name, Year)

    From there it is easy to envision related formulas generated from the same core such as 

    = StudentGradesλ(Name, Marks)

     

     

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    aijatiw 

    It looks like you have 365. A VBA/UDF solution is superfluous with Lambda as an option:

     

    'Results Lambda
    =LAMBDA(words,name,year,LET(word1, TAKE(words, 1), word2, TAKE(words, -1), word1 & " " & name & " " & word2 & " " & year))

     

  • aijatiw Yes, you can create a VBA function but my question is why complicate it that much?

     

    You could easily just have a column C with the below formula:
    ="Student with name "&A2&" is now on year "&B2

    You can drag this formula down as many rows as you need. No need to be "typing the formula over & over again".

     

    • aijatiw's avatar
      aijatiw
      Copper Contributor
      I'm sorry I think that I make myself unclear, because what I want is instead of typing =C35&C31&C36&D27, I want it to type =result(C31,D27) and it show the same result, so I was thinking how to put the "student with name " and "is now in year " like embedded on the sheet? I'm sorry if this confuses you I hope you get what I'm trying to say.

      Because I need it to become as dynamic as possible. I will be dealing with more column and the result is not only to concatenate a row then drag it down,
      • Deleted's avatar
        Deleted

        aijatiw Sure thing. I still wouldn't use VBA, you can just add two additional columns for your inputs:

         

        The formula will now be =C2&" "&A2&" "&D2&" "&B2

         

        If you're really, really desperate for a VBA solution I could look into it but honestly it's overkill for a simple task as this.

Resources