VBA Function Not Working with nested loops

Question

I'm creating a function in Excel VBA which checks each word in a string, and if a word from the string is located in a keyword bank located in a different Excel sheet.

If it is a positive word, it gets a score of 10, and if it is a negative word, it gets a score of -10. After the function reads through the string it calculates the total and prints the output total.

I have to remove $!., and ? from the string before doing the calculation. I'm supposed to use StrComp, Split, and Remove which I have done. I'm also supposed to use nested loops:

one for each word in the keywords,
and one for each string text.

Here is my code below that doesn't seem to want to work for me. Any help on getting this to run would be extremely helpful:

Function sentimentCalc(tweet As String) As Integer Dim i As Integer, j As Integer, k As Integer Dim positiveworda As String, negativewords As String Dim positive As Range, negative As Range Dim tweetcleaned As String Dim count As Integer

Set positive = Worksheets("keywords").Range("A2:A53")
Set negative = Worksheets("keywords").Range("B2:B53")

tweetwords = Split(tweet, " ")
positivewords = Split(positive, " ")
negativewords = Split(negative, " ")

    tweetcleaned = Replace(tweet, "$", "")
    tweetcleaned = Replace(tweet, "!", "")
    tweetcleaned = Replace(tweet, ".", "")
    tweetcleaned = Replace(tweet, ",", "")
    tweetcleaned = Replace(tweet, "?", "")

    tweetcleaned = tweet

count = 0

For i = LBound(tweetwords) To UBound(tweetwords)
    For j = LBound(positiveword) To UBound(positiveword)
        If StrComp(tweetwords(i), "positivewords()", vbTextCompare) = 0 Then
           count = count + 10
           Exit For
        End If
    Next j
    For k = LBound(negativeword) To UBound(negativeword)
        If StrComp(tweetwords(i), "negativewords()", vbTextCompare) = 0 Then
           count = count - 10
           Exit For
        End If
    Next k
Next i

sentimentCalc = count
End Function

Here is my code below that doesn't seem to want to work for me. What is the problem? — AntiDrondert
you defined keywordsnegative As String so it throws an "Error 13 type mismatch" at keywordsnegative = Split(negative, " ") because it has to be an array like keywordsnegative() As String. If you omit the brackets () after the variable name it is just a string. With brackets it is an array of strings. Split returns an array! For future questions please always include your error message and tell in which position of the code they occur. — Pᴇʜ
You can't use Split on Range. You can't use Replace on arrays. — Vincent G

JahKnows JahKnows · Accepted Answer · 2018-03-13T03:27:33

This should work for you

Function sentimentCalc(tweet As String) As Integer
    Dim i As Integer, j As Integer, k As Integer
    Dim positiveworda As String, negativewords, word As String
    Dim positive As Range, negative As Range
    Dim tweetcleaned As String
    Dim count As Integer

    tweetcleaned = Replace(tweet, "$", "")
    tweetcleaned = Replace(tweet, "!", "")
    tweetcleaned = Replace(tweet, ".", "")
    tweetcleaned = Replace(tweet, ",", "")
    tweetcleaned = Replace(tweet, "?", "")

    tweetwords = Split(tweetcleaned, " ")

    count = 0
    For i = LBound(tweetwords) To UBound(tweetwords)
        For j = 1 To 53
            word = Worksheets("keywords").Cells(j, 1)
            If StrComp(tweetwords(i), word, vbTextCompare) = 0 Then
               count = count + 10
               Exit For
            End If
        Next j
        For k = 1 To 53
            word = Worksheets("keywords").Cells(j, 2)
            If StrComp(tweetwords(i), word, vbTextCompare) = 0 Then
               count = count - 10
               Exit For
            End If
        Next k
    Next i

    sentimentCalc = count
End Function

Here is the same method but for a variable number of positive and negative words contained in the 3rd sheet.

Function sentimentCalc(tweet As String) As Integer
    Dim i As Integer, j As Integer, k As Integer
    Dim positiveworda As String, negativewords, word As String
    Dim positive As Range, negative As Range
    Dim tweetcleaned As String
    Dim count As Integer

    MsgBox tweet

    tweetcleaned = Replace(tweet, "$", "")
    tweetcleaned = Replace(tweet, "!", "")
    tweetcleaned = Replace(tweet, ".", "")
    tweetcleaned = Replace(tweet, ",", "")
    tweetcleaned = Replace(tweet, "?", "")

    tweetwords = Split(tweetcleaned, " ")

    num_pos_words = Worksheets("keywords").Range("A65000").End(xlUp).Row
    num_neg_words = Worksheets("keywords").Range("B65000").End(xlUp).Row

    count = 0
    For i = LBound(tweetwords) To UBound(tweetwords)
        For j = 1 To num_pos_words
            If StrComp(tweetwords(i), Worksheets("keywords").Cells(j, 1), vbTextCompare) = 0 Then
               count = count + 10
               Exit For
            End If
        Next j
        For k = 1 To num_neg_words
            If StrComp(tweetwords(i), Worksheets("keywords").Cells(j, 2), vbTextCompare) = 0 Then
               count = count - 10
               Exit For
            End If
        Next k
    Next i

    sentimentCalc = count
End Function

VBA Function Not Working with nested loops

1 Answers