- Do you want to analyse what people say about your product on Amazon Marketplace?
• Or the reviews that people leave on your Facebook page?
Not a problem!
You can do some basic sentiment analysis with VBA!
As an example, I’ll be analysing 1000 fine food reviews posted on Amazon. Since I am mostly interested in the very positive and the very negative ones, I’ll be using the code below to loop through each review and highlight in green the words I’ve identified as positive and in red the ones I’ve identified as negative.
That way, I can then easily determine if the general perception of my product is mostly positive or negative.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub ColourWordsInString() | |
Dim Cell As Range | |
Dim i As Integer | |
Dim prv As String | |
Dim word As String | |
Dim positive(1 To 5) As String | |
Dim negative(1 To 5) As String | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'if it finds a certain word in a string, color it in either red or green | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
positive(1) = "tasty" | |
positive(2) = "delicious" | |
positive(3) = "love" | |
positive(4) = "great" | |
positive(5) = "awesome" | |
negative(1) = "expensive" | |
negative(2) = "crap" | |
negative(3) = "bitter" | |
negative(4) = "smelly" | |
negative(5) = "greasy" | |
For Each Cell In ActiveSheet.Range("J2:J" & ActiveSheet.UsedRange.Rows.Count) | |
prv = Cell.Value | |
For i = 1 To 5 | |
word = positive(i) | |
If InStr(prv, positive(i)) > 0 Then | |
Cell.Activate | |
With ActiveCell | |
.Characters(Start:=InStr(prv, positive(i)), Length:=Len(positive(i))).Font.Color = RGB(0, 176, 80) | |
End With | |
End If | |
Next i | |
'--------------------------------------------------------------------------------------- | |
For i = 1 To 5 | |
word = negative(i) | |
If InStr(prv, negative(i)) > 0 Then | |
Cell.Activate | |
With ActiveCell | |
.Characters(Start:=InStr(prv, negative(i)), Length:=Len(negative(i))).Font.Color = RGB(192, 0, 0) | |
End With | |
End If | |
Next i | |
Next Cell | |
End Sub |
And voila!
You can download the full spreadsheet here – 1000 Amazon Fine Food Reviews
Happy VBA coding!