What is the problem in my word frequency counter?

[marlonsaveri]

Hello, I created a frequency count of words given by the following code:

Private Sub CommandButton1_Click()
    Dim i, j As Integer 'counters
    Dim var As String 'takes the text
    Dim size As Integer 'takes the number of words in the text
    Dim matrix As Variant
    Dim mtx(1, 1) As String
    
    var = TextBox1.Text
    matrix = Split(var, " ") 'separates each word of text in an array index.
    
    size = UBound(matrix) 'takes the number of words in the text
    
    ReDim mtx(size, 2) As String 'resizes the array to fit all the words; THE ERROR IS HERE, why?
    
    For i = 0 To size 'from 1 to size
       mtx(i, 0) = matrix(i) 'the first receives the word
       mtx(i, 1) = 0 'the second receives zero
    Next
    
    'The number of appearances of the word stored in mtx (i, 0) is stored in mtx (i, 1)
    For i = 0 To size
        For j = 0 To size
            If i <> j Then 'not to equate one thing with itself.
                If mtx(i, 0) = mtx(j, 0) Then 'when it finds the same word.
                    mtx(i, 1) = mtx(i, 1) + 1 'the counter receives one more.
                End If
            End If
        Next
    Next
    
    'This saves the words on the worksheet.
    Range("A1").Select
    For i = 0 To size
        ActiveCell.Offset(i, 0) = mtx(i, 0)
        ActiveCell.Offset(i, 1) = mtx(i, 1)
    Next
    
    'Now, we sort according to column B and excluded the repeated words.
End Sub

However, an error occurred while trying to resize array, why? What I make to it works?

Thank you.

[Fizziii]

ReDim can only be used to change the size of the last element in an array.

You tried to use ReDim to change more than one dimension of an array contained within a Variant.
You can only use ReDim to change the size of the last dimension of an array in a Variant. To create an array with multiple dimensions that can be redimensioned, the array can't be contained within a Variant, and you have to declare it the normal way.

[marlonsaveri]

Thanks, but:

If I change to:
Dim mtx(2, 1) As String

and

ReDim mtx(2, size)

it's not working yet.

[Fizziii]

What error is it giving you?

Edit: Doing some research, you can only use the ReDim command on dynamic arrays. Static arrays will give an "Array already dimensioned" error.

If you change to:

Dim mtx() As String

then the

ReDim mtx(2,size)

should work I believe.

[marlonsaveri]

Selects the word "size"within "Dim mtx (2, size) As String"and appears the error:
Constant expression required.

Or select the word "size"within "ReDim mtx (2, size) As String"and appears the error:
The matrix has already been scaled.

[marlonsaveri]

I did it, like you said.

Now, appears Runtime error "13", incompatible types.

At least that is one less problem.

Private Sub CommandButton1_Click()
    On Error GoTo studyerror
    Dim i, j As Integer 'counters
    Dim var As String 'takes the text
    Dim size As Integer 'takes the number of words in the text
    Dim matrix As Variant
    Dim mtx() As String
    
    var = TextBox1.Text
    matrix = Split(var, " " Or "." Or "," Or ";") 'separates each word of text in an array index.
    'MsgBox matrix(3), for example
    
    size = UBound(matrix) 'takes the number of words in the text
    
    ReDim mtx(2, size) As String 'resizes the array to fit all the words; THE ERROR IS HERE, why?
    
    For i = 0 To size 'from 1 to size
       mtx(0, i) = matrix(i) 'the first receives the word
       mtx(1, i) = 0 'the second receives zero
    Next
    
    'The number of appearances of the word stored in mtx (i, 0) is stored in mtx (i, 1)
    For i = 0 To size
        For j = 0 To size
            If i <> j Then 'not to equate one thing with itself.
                If mtx(0, i) = mtx(0, j) Then 'when it finds the same word.
                    mtx(1, i) = mtx(1, i) + 1 'the counter receives one more.
                End If
            End If
        Next
    Next
    
    'This saves the words on the worksheet.
    Range("A1").Select
    For i = 0 To size
        ActiveCell.Offset(i, 0) = mtx(1, i)
        ActiveCell.Offset(i, 1) = mtx(0, i)
    Next
    
    'Now, we sort according to column B and excluded the repeated words.

Exit Sub
studyerror:
        MsgBox Err.Number
End Sub

Hey, it works!!! Thanks.

The new error is in: matrix = Split(var, " " Or "." Or "," Or ";"). Why? I can separate words for points, not by spaces.

[Fizziii]

The split line is the one that's giving the error. I'm not sure if you can split something based on multiple delimiters as I've never tried it before... have you done that before?

I did find this on another page (link) to split with multiple delimiters.

'=======================================================
'ReplaceAndSplit by alainbryden, optimized by aikimark
'Uses the native REPLACE() function to replace all delimiters with a common
'delimiter, and then splits them based on that.
'=======================================================
Function ReplaceAndSplit(ByRef Text As String, ByRef DelimChars As String) As String()
    Dim DelimLen As Long, Delim As Long
    Dim strTemp As String, Delim1 As String, Arr() As String, ThisDelim As String
    strTemp = Text
    Delim1 = Left$(DelimChars, 1)
    DelimLen = Len(DelimChars)
    For Delim = 2 To DelimLen
        ThisDelim = Mid$(DelimChars, Delim, 1)
        If InStr(strTemp, ThisDelim) <> 0 Then _
            strTemp = Replace(strTemp, ThisDelim, Delim1)
    Next
    ReplaceAndSplit = Split(strTemp, Delim1)
End Function

[marlonsaveri]

Thanks,
I need study about this code.

And if, at the beginning of the text, I switched all the points with spaces? But I realized that error occurs when there are double spaces (the program calculates wrong).


I leave one last question:
How to differentiate not case sensitive?

Thank you, again.

[Fizziii]

I would use the UCase or LCase to ignore case.

If UCase(mtx(0, i)) = UCase(mtx(0, j)) Then 'when it finds the same word.
                    mtx(1, i) = mtx(1, i) + 1 'the counter receives one more.
                End If

This would check the mtx(0,i) string converted to uppercase against the string in mtx(0,j) converted to uppercase.

[marlonsaveri]

Very Good,

Let's see how is the code:

Private Sub CommandButton1_Click()
    On Error GoTo studyerror
    Dim i, j As Integer 'counters
    Dim var As String 'takes the text
    Dim size As Integer 'takes the number of words in the text
    Dim matrix As Variant
    Dim mtx() As String
    
    ActiveWorkbook.Sheets("frequency").Select
    Cells.Delete
    
    var = TextBox1.Text
    matrix = Split(var, " ") 'separates each word of text in an array index.
    'MsgBox matrix(3), for example
    
    size = UBound(matrix) 'takes the number of words in the text
    
    ReDim mtx(2, size) As String 'resizes the array to fit all the words; THE ERROR IS HERE, why?
    
    For i = 0 To size 'from 1 to size
       mtx(0, i) = matrix(i) 'the first receives the word
       mtx(1, i) = 1 'the second receives zero
    Next
    
    'The number of appearances of the word stored in mtx (i, 0) is stored in mtx (i, 1)
    For i = 0 To size
        For j = 0 To size
            If i <> j Then 'not to equate one thing with itself
                If mtx(0, i) = mtx(0, j) Then 'when it finds the same word.
                    mtx(1, i) = mtx(1, i) + 1 'the counter receives one more.
                End If
            End If
        Next
    Next
    
    'This saves the words on the worksheet.
    Range("A1").Select
    For i = 0 To size
        ActiveCell.Offset(i, 0) = mtx(1, i)
        ActiveCell.Offset(i, 1) = mtx(0, i)
    Next
    
    'Now, we sort according to column B and excluded the repeated words.
    Call Macro1
    
    Range("B2").Select
    For i = 0 To size
        If ActiveCell.Text = ActiveCell.Offset(-1, 0).Text Then
            ActiveCell.EntireRow.Delete
        End If
        ActiveCell.Offset(1, 0).Select
    Next
    
    
Exit Sub
studyerror:
        MsgBox "Error number: " And Err.Number
End Sub

Sub Macro1()
'
' Macro1 Macro

    ActiveWorkbook.Worksheets("frequency").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("frequency").Sort.SortFields.Add Key:=Range("B1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("frequency").Sort
        .SetRange Range("B:B")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Problems:
1. Does not differentiate between words that contain point, comma, etc.. (important)
2. By listing the words, unable to delete repeated items.
3. Do not jump in line "textbox!

But it seems it's working. Thanks.