Results 1 to 7 of 7

Thread: Help With User Defined Function
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2012
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help With User Defined Function

    I haver a user defined function to take the second to last word in a string and add 1 to it. When I run the macro with the function in it, I get "Sub Or Function Not Defined". When I go to the debugger, the "Rept" function is highlighted.
    User Defined Function:

    Public Function SECONDWORD(sw As String) As String
    ln = Trim(Left(Right(" " & Substitute(Trim(sw), " ", Rept(" ", 60)), 120), 60))+1
    End Function

    Thanks in advance for any help I may get.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Help With User Defined Function

    Neither Rept, nor Substitute exist in VBA.
    Also that function won't return anything.

    Can you please post a few examples of your data & what you want it to look like?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Nov 2017
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help With User Defined Function

    Hello anwaee2

    If what you need to is find the last word in a string, that is the word to the right of the last space character in a string then why can't you simply use:

    RIGHT( text, [number_of_characters] )
    Where number_of_Characters can be the position of the last space +1? The + 1 is to get to the character that starts the last word.

    Would that not work in your strings?

  4. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,434
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help With User Defined Function

    Code:
    Public Function SECONDWORD(sw As String) As String
    Dim wf As Variant
    Set wf = WorksheetFunction
    SECONDWORD = Trim(Left(Right(" " & wf.Substitute(Trim(sw), " ", wf.Rept(" ", 60)), 120), 60))  ' no +1
    End Function
    I have not tested that extensively, but it does return "second" when the Excel formula is =secondword("first second third").

    Note: The VBA alternatives to wf.Substitute and wf.Rept are Replace and String. I did not bother to see how they might work (or not) with your design.

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    256
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help With User Defined Function

    Quote Originally Posted by anwaee2 View Post
    user defined function to take the second to last word in a string and add 1 to it .
    Code:
    Public Function SECONDWORD(sw As String) As String
    Dim x As Variant
        x = Split(sw, " "): x(0) = 1:  x = Join(x)
        SECONDWORD = x
    End Function

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Help With User Defined Function

    Maybe
    Code:
    Function anwaee2(sw As String) As String
       Dim sp As Variant
       sp = Split(sw)
       sp(UBound(sp) - 1) = sp(UBound(sp) - 1) & 1
       anwaee2 = Join(sp, " ")
    End Function
    or
    Code:
    Function anwaee2(sw As String) As String
       Dim sp As Variant
       sp = Split(sw)
       anwaee2 = sp(UBound(sp) - 1) & 1
    End Function
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Sep 2012
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help With User Defined Function

    joeu2004, thank you so much. I have googled and searched through 3 books i have with no results. But your code worked perfectly. I have not tried the others yet but wish to thank all for the replies. Thanks again to all.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •