I have looked at tutorials and Q/A on here and there and have tried what I have read to no avail. I have this Word document.
There will be text in O1 and the VBA will count it (characters, spaces + paragraphs) and output it in C1, and the C1 fill changes red or green if over/under the character limit. The same for A1 and C2 and so on for any number of following tables.
The VBA options are as follows:
- Count the “Objective” O1 text for the selected table: Sub TableCharCount_Obj()
- Count the “Accomplishment” A1 text for the selected table: Sub TableCharCount_Acc()
- Count both the Obj O# and Acc A# texts in each table, for all tables: Sub TableCharCount()
- Delete the Obj and Acc texts and the char counts and clear the fill in the C# cells: Sub ClearCount()
- plus an additional module w/ code that inserts a new page and new table at the end of the document (vba not included)
- plus a userform to call the macro above (vba not included)
The VBA was working for the actions above when I had the row/columns hard coded into various places in the code. If rows/columns were ever added/deleted from the tables, it would be simpler if the row/column numbers were in one place and referred back to as variables, so I changed the row/col #s to public variables. In the code, I track what becomes of
orow (output row) &
chcct (character count col) and both are 0 as it runs, despite both being initialized as 3 in Sub Row_Col_Num().
My public variables are the top of the module before a Sub() and denoted as
Public. Sub Row_Col_Num() which contains the variable assignments is also
I tried putting the Sub() name in front of the variable when it is used, e.g. Row_Col_Num.orow, in Sub TableCharCount_Obj(). The produced the error “Expected Function or variable” at line:
Call TableCharCount(Row_Col_Num.oRow, Row_Col_Num.oRow)
I tried the module name in front of the variable as well, e.g. Module1.orow.
Both using nothing before the variable and using
Module1.orow resulted in the macro counting the wrong row and outputting to the wrong cell.
All 3 cases
cchct both continued to be 0.
The only solution I can think of is to
Call Public Sub Row_Col_Num()
In every procedure that calls the main code.
If it’s not obvious, there was some mission creep as I added more capability which resulted in not having a clear end state in mind. For now, if I could get the public variables to work, I would be happy with it. Appreciate any suggestion to get these variables to work. VBA below:
'Overall - These macros are for counting # of characters (w/ space) + # of paragraphs in tables... '...displaying that # in a cell in the table & removing the count. 'NOTE: If rows (or columns) are deleted/added in the tables, will need to change numbers in macros below. 'Row changes will break #1-4. Column changes will break #1 and #5. '****************************** '#0 -- This creates variables for column and row number used in all the macros. Only need to change row/col number here if row/col are added/deleted Option Explicit Public oRow As Integer 'row with "Objectives" text Public aRow As Integer 'row with "Accomplishments" text Public cOnA As Integer 'column that both obj and accmp text are in Public cChCt As Integer 'column that the char count is output to Public Sub Row_Col_Num() oRow = 3 aRow = 5 cOnA = 1 cChCt = 3 Debug.Print "cchct pub sub: " & cChCt End Sub '#1 Sub ClearCount() 'This will clear/delete the char counts in ALL tables in the document. Dim xRng As Word.Range Dim y As Integer Dim tcount, tbl As Integer Application.ScreenUpdating = False tcount = ActiveDocument.Tables.Count For tbl = 1 To tcount For y = 2 To 4 Step 2 'row #'s hard coded Set xRng = ActiveDocument.Range(Start:=ActiveDocument.Tables(tbl).Cell(y, cChCt - 1).Range.Start, _ End:=ActiveDocument.Tables(tbl).Cell(y, cChCt).Range.End) 'Range contains text "#char" and char count from their respective cells xRng.Select Selection.Delete 'delete the text xRng.Shading.BackgroundPatternColorIndex = wdGray25 'Change cell color back to grey Next y Next tbl Selection.GoTo What:=wdGoToSection, Which:=wdGoToFirst Application.ScreenUpdating = True End Sub '#2 Sub TableCharCount_Obj() 'Run character count for the "Objectives" in the SELECTED table Debug.Print "orow = " & oRow Call TableCharCount(oRow, oRow) 'provide it 2x to make IF and FOR loop End Sub '#3 Sub TableCharCount_Acc() 'Run character count for the "Accomplishments" (row 5) in the SELECTED table Call TableCharCount(aRow, aRow) 'provide it 2x to make IF and FOR loop End Sub '#4 Sub CharCount_AllTab() 'Run character count for "Objectives" & "Accomplishments" for ALL tables in the document. Call TableCharCount(oRow, aRow) End Sub '#5 Option Explicit Sub TableCharCount(ByVal a As Integer, ByVal b As Integer) ' 'Macro created by Jason Ryals 'Counts total characters in a cell w/in a table and outputs the number to a different cell, and colors the cell red or green if over/under the... '.......the maximum number of characters. Dim charCount, charWSCount, paraCount, charTot As Double Dim iRng, oRng, txtRng As Word.Range Dim i, max, s, t, x As Integer Dim tcount, tbl As Integer Dim DocT As Table 'for active doc tables Debug.Print "cchct1= " & cChCt 'Debug.Print vbCr & "-----START-------" & vbCr Application.ScreenUpdating = False If a <> b Then tcount = ActiveDocument.Tables.Count tbl = 1 'used in FOR loop, start w/ table #1 s = b - a '"STEP" used in FOR loop = # of rows between objectives text and accomplishments text Else On Error GoTo ErrMsg 'handles expected user error of not selecting a table to execute on tbl = ActiveDocument.Range(0, Selection.Tables(1).Range.End).Tables.Count 'ID the table that is selected tcount = tbl 'prevents FOR loop from trying to run again s = 1 '"STEP" used in FOR loop = # of rows between objectives text and accomplishments text / do not set to zero = infinite loop End If 'Debug.Print "# of Tables: " & tcount For t = tbl To tcount 'loops thru the tables Set DocT = ActiveDocument.Tables(t) For x = a To b Step s 'loops thru the applicable row(s) in the table 'Debug.Print "x @ start = " & x 'Debug.Print "table " & t iRng = DocT.Cell(x, cOnA) iRng.Select '-Alternative way of counting // not output into document----- ' charCount = Selection.Characters.Count 'does not count bullets or the 'space' between the bullet and first letter / does count line breaks (paragraphs) 'Debug.Print "selected char# " & charCount 'Count used in output Selection.MoveLeft wdCharacter, 1, wdExtend 'computerstats requires the text itself selected, characters.count can use the whole cell selected charWSCount = Selection.Range.ComputeStatistics(Statistic:=wdStatisticCharactersWithSpaces) 'counts bullets & space after bullet / not line breaks (paragraphs) 'Debug.Print "Comp statchar# " & charWSCount '--------- paraCount = Selection.Range.ComputeStatistics(Statistic:=wdStatisticParagraphs) 'Debug.Print "#paras = " & paraCount 'need count of paragraphs to add to #char IOT get a closer total to MyBiz '---------- charTot = charWSCount + paraCount 'Output to table cell i = x - 1 'output cell is 1 row above cell that is counted Set oRng = DocT.Cell(i, cChCt).Range 'Char count ouput row,column Debug.Print "cchct2= " & cChCt oRng.Text = charTot Set txtRng = DocT.Cell(i, cChCt - 1).Range '"# Char:" location row,column txtRng.Text = "# Char:" 'Maximum # of char allowed in a cell. Used to change cell fill red or green. max = 2000 '"Accomplishment" row (row 5) has a max of 2000 If i = 2 Then max = 1500 '"Objective" row (row 3) has a max of 1500 'Change color of cell to indicate over/under max # of characters If charCount < max Then oRng.Shading.BackgroundPatternColor = wdColorBrightGreen Else: oRng.Shading.BackgroundPatternColor = wdColorRed End If 'Debug.Print "x @ end = " & x 'Debug.Print "--------Next x--------------" Next x 'Debug.Print "------Next Table------" Next t ActiveDocument.Tables(tbl).Select 'attempt to move to top of 1st table if using CharCount_AllTab() or just to the top of the selected table for the other macros Selection.GoTo What:=wdGoToBookmark, Name:="Page" Selection.StartOf Application.ScreenUpdating = True Exit Sub ErrMsg: Msgbox "Select a table by placing the cursor anywhere in the table. Press OK and try the macro again numnuts!", _ vbOKOnly, "Table not selected" End Sub