Word VBA – Counting Characters in Tables – Error on Public Variable

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. example below

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 Public.
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 orow and cchct both continued to be 0.

The only solution I can think of is to

Call Public Sub Row_Col_Num()  

Before every

Call TableCharCount()  

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