VBA函数不使用嵌套循环-Java 学习之路

我正在Excel VBA中创建一个函数，它检查字符串中的每个单词，以及字符串中的单词是否位于不同Excel表单中的关键字库中 .

如果它是一个正面的单词，则得分为10，如果是负面单词，则得分为-10 . 函数读取字符串后，它会计算总数并打印输出总数 .

在进行计算之前，我必须从字符串中删除 $!., 和 ? . 我应该使用 StrComp ， Split 和 Remove ，我已经完成了 . 我也应该使用嵌套循环：

关键字中每个单词一个，
和每个字符串文本一个 .

以下是我的代码，似乎不想为我工作 . 任何有关运行此操作的帮助都非常有用：

函数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

1 回答

这应该适合你

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

这是相同的方法，但是对于第3张中包含的可变数量的正面和负面词 .

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

回复于 2024-04-20T12:37:30+08:00

VBA函数不使用嵌套循环

1 回答

相关问题