This is the code that I created for this function however I get a value error which means there is something in the code that is not right can someone help me figure out what it is
Task 4: Calculate Sentiment Copy the data in the keywords.csv file (downloaded from OWL) and add it as a new sheet with the name keywords in your workbook. You will be using this sheet with the functions you make in the following tasks. Step 4.1: sentimentCalc Function (20 marks) Create a VBA function named sentimenCalc that determines the sentiment of each tweet based on its contents. The header for this function must be: Function sentimentCalc(tweet As String) As Integer This function should check each word in the tweet and if the word exists as one of the keywords in the positive list or negative list it should impact the overall sentiment value The positive list and negative list words exist in the keywords sheet. Access the keywords as ranges within your VBA code. The case of the word is inconsequential. For instance, happy, HAPPY, or hApPy are all treated as positive words regardless of their case (Hint: StrComp If the word is in the positive list, it should increase the sentiment value by 10, if it is in the negative list it should decrease it by 10. For instance, if the positive list includes "happy" rally", "growth" and the negative list includes "crash", "scam", "bad" then: If the Tweet is "I am Happy that Bitcoin is showing growth.". The sentiment value will be 101020 If the Tweet is "I am happy that Bitcoin is a scam and will CRASH!" The sentiment value will be 10-10-10 =-10 You must remove the following punctuation characters from the tweet text in your VBA code before calculating the sentiment:! . ,? :)C You may do this using multiple lines each calling the Replace function or with an array, loop and one call to the Replace function. Both methods will be marked as correct. Use this function in your processedData worksheet to create a new column (column J) that calculates the sentiment value for each tweet. Some Hints: You will need to use the string functions StrComp, Split and Replace in this function. To get the ranges from the keywords sheet use Worksheet and Range object like so: Dim positive As Range Set positive - Worksheets ("keywords").Range ("A2: A76") This will give you the range A2: A76 from the sheet named keywords as the variable named positive. You can do the same for the negative range (but with different cell references and variable name). You will need to use nested loops. One to go through each word in the keywords and one to go through each word in the tweet text. Function sentimentCalc (tweet As String) As Integeir Dim i As Integer, j As Integer, k As Integer Dim positiveWords As Range, negativeWords As Range Dim positive As Range, negative As Range Dim newTweet As String Dim Words ) As String Dim count As Integer newTweet Replace (tweet, "", "") newTweet Replace (tweet,"!", "") newTweet Replace (tweet, ".", "") newTweet Replace (tweet, ", ", "") newTweet Replace (tweet, "?", "") Words-Split (newTweet, "") positiveWords-Worksheets ("keywords" Range ("A2:A76") negativeWords-Worksheets ("keywords" Range"B2:B76") count0 For i-LBound (Words ()) To UBound (Words ) For j = 2 To positiveWords . Rows. count If (StrComp(Words(1), count-count 10 positiveWords .cells (Rowindex : =j, ColumnIndex :-"A").Value, vbTextCompare) 0) Then = End If Next j For k = 2 To negativeWords. Rows. count If (StrComp(Words(1), count-count-10 negativeWords .cells (Row! ndex : =k, ColumnIndex:-"B").Value, vbTextCompare) 0) Then = End If Next k Next i sentimentCalc-count End Function Task 4: Calculate Sentiment Copy the data in the keywords.csv file (downloaded from OWL) and add it as a new sheet with the name keywords in your workbook. You will be using this sheet with the functions you make in the following tasks. Step 4.1: sentimentCalc Function (20 marks) Create a VBA function named sentimenCalc that determines the sentiment of each tweet based on its contents. The header for this function must be: Function sentimentCalc(tweet As String) As Integer This function should check each word in the tweet and if the word exists as one of the keywords in the positive list or negative list it should impact the overall sentiment value The positive list and negative list words exist in the keywords sheet. Access the keywords as ranges within your VBA code. The case of the word is inconsequential. For instance, happy, HAPPY, or hApPy are all treated as positive words regardless of their case (Hint: StrComp If the word is in the positive list, it should increase the sentiment value by 10, if it is in the negative list it should decrease it by 10. For instance, if the positive list includes "happy" rally", "growth" and the negative list includes "crash", "scam", "bad" then: If the Tweet is "I am Happy that Bitcoin is showing growth.". The sentiment value will be 101020 If the Tweet is "I am happy that Bitcoin is a scam and will CRASH!" The sentiment value will be 10-10-10 =-10 You must remove the following punctuation characters from the tweet text in your VBA code before calculating the sentiment:! . ,? :)C You may do this using multiple lines each calling the Replace function or with an array, loop and one call to the Replace function. Both methods will be marked as correct. Use this function in your processedData worksheet to create a new column (column J) that calculates the sentiment value for each tweet. Some Hints: You will need to use the string functions StrComp, Split and Replace in this function. To get the ranges from the keywords sheet use Worksheet and Range object like so: Dim positive As Range Set positive - Worksheets ("keywords").Range ("A2: A76") This will give you the range A2: A76 from the sheet named keywords as the variable named positive. You can do the same for the negative range (but with different cell references and variable name). You will need to use nested loops. One to go through each word in the keywords and one to go through each word in the tweet text. Function sentimentCalc (tweet As String) As Integeir Dim i As Integer, j As Integer, k As Integer Dim positiveWords As Range, negativeWords As Range Dim positive As Range, negative As Range Dim newTweet As String Dim Words ) As String Dim count As Integer newTweet Replace (tweet, "", "") newTweet Replace (tweet,"!", "") newTweet Replace (tweet, ".", "") newTweet Replace (tweet, ", ", "") newTweet Replace (tweet, "?", "") Words-Split (newTweet, "") positiveWords-Worksheets ("keywords" Range ("A2:A76") negativeWords-Worksheets ("keywords" Range"B2:B76") count0 For i-LBound (Words ()) To UBound (Words ) For j = 2 To positiveWords . Rows. count If (StrComp(Words(1), count-count 10 positiveWords .cells (Rowindex : =j, ColumnIndex :-"A").Value, vbTextCompare) 0) Then = End If Next j For k = 2 To negativeWords. Rows. count If (StrComp(Words(1), count-count-10 negativeWords .cells (Row! ndex : =k, ColumnIndex:-"B").Value, vbTextCompare) 0) Then = End If Next k Next i sentimentCalc-count End Function
