Excel vba split function not working

Question

I'm trying to get a number of character occurence in a string. It should work, but it doesn't.

Function countLetter(letter As String, secretWord As String)
    MsgBox (Split(secretWord, letter).Length)
    countLetter = Split(secretWord, letter).Length - 1
End Function

What is wrong here?

user4039065 · Accepted Answer · 2017-10-25 16:29:42Z

7

Split does not have a .Length property. Use UBound for a zero-based count of the elements in the array.

Function countLetter(letter As String, secretWord As String)
    MsgBox UBound(Split(secretWord, letter)) + 1
    countLetter = UBound(Split(secretWord, letter))
End Function

answered Oct 25, 2017 at 16:29

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Mathieu Guindon Over a year ago

@Ans unrelated to the splitting, but notice the lack of parentheses around the Message argument passed to the MsgBox function; whenever you invoke a procedure without capturing its return value into a local variable, you should not have parentheses around the arguments. This will cause you problems one day or another.

Collectives™ on Stack Overflow

Excel vba split function not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related