VBA - character counting analysis

[aurelien_]

hello,

i need a vba macro that do text analysis under some constraints

let's take a workbook with 2 worksheets : "text to analyze" tab and "Linguistic analysis" tab

on the "text to analyze" tab, you will enter a text on A1 cell

on the "Linguistic analysis" tab you will get statistical data like number of letters, vowels, most used words in the text etc...

i made an example file of final result i will like to get :
https://docs.google.com/spreadsheets...f=true&sd=true

there is constraints to follow that you can find and read in the 3rd tab of the workbook linked above and some purple text on "Linguistic analysis" tab

code with line comments will help much

Thank you for your help !

[Aussiebear]

Welcome to VBAX Aurelian_. I must caution you here as to the extent of the assistance required here.

[Aussiebear]

This should hopefully count the number of words in a cell.

=IF(A1="", 0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

[Aussiebear]

Counting Characters in a Cell. This will include all characters including spaces.

 =Len(A1)

Counting Characters in a cell not including spaces

 =Len(Substitute(A1,"" "",""))

Count the occurrences of a particular character in a Cell

 =Len(A1)-Len(Substitute(A1,"A",""))

[aurelien_]

hello,

so i did all the code trying to follow strictly the constraints and it works on my side

here is the vba code :
https://drive.google.com/file/d/1cFI...usp=drive_link

to apply on this test file, you have just to copy-paste code above in the vba Excel coding sheets on the test file linked below and run it after you save the test file as .xlsm to allow macro :
https://docs.google.com/spreadsheets...f=true&sd=true

But as you can see there is 3 macro :
first macro answer question 1 to 3 thanks to functions/end functions
the second macro answer question 4
the third macro answer question 5

can you help me to make one macro of this three ones by transforming macro 2 and macro 3 into functions ? Thus i will have only one macro button that will answer the 5 questions at once.

Thank you !

[Paul_Hossler]

To get you started, you could do something like this
It uses your functions to add information to the second worksheet
You'll need to add the rest

I added a command buttion to run the sub

Also, I added your data and macro to make a XLSM workbook and attached it

It's MUCH easier if you do it that way instead of Google docs, etc. so PLEASE next time

Option Explicit


Dim wsText As Worksheet
Dim wsAnalysis As Worksheet
Dim sText As String




Sub SuperSub()


    Set wsText = Worksheets("Texte à analyser")
    sText = wsText.Cells(1, 1).Value
    
    Set wsAnalysis = Worksheets("Analyse linguistique")
    
    With wsAnalysis
    
        .UsedRange.Clear
        .Cells(1, 1).Value = "Number of letters in a string, ignoring punctuation, spaces and numbers"
        .Cells(1, 2).Value = NombreDeLettres(sText)
        
        .Cells(2, 1).Value = "Number of vowels in a string, ignoring punctuation, spaces and numbers"
        .Cells(2, 2).Value = NombreDeVoyelles(sText)
        
    End With
        
        
    MsgBox "Done"


End Sub