Checking the data type (integer or string) in a word table-CodePudding

I am trying to do some conditional formatting in word table based on the value in a specific cell. If the value is <1 set the background to green; if the value is between 1 and 10, format the background yellow and if the value is above 10 format the background red.

I am able to loop through a table and debug.print the content of each cell but am struggling with checking for the datatype in the correspoding cell.

I tried IsNumeric, Int, Fix but none work

Sub ConditionalFormat()

Dim tbl As Table, r As Long, c As Long

    Set tbl = ActiveDocument.Tables(1)
    For r = 1 To tbl.Rows.Count
       For c = 1 To tbl.Columns.Count      
         If tbl.Cell(r, c) = Int(tbl.Cell(r, c)) Then    
          tbl.Cell(r, c).Shading.BackgroundPatternColor = wdColorBlueGray       
         End If
        Next c
    Next r

End Sub

where am i going wrong?

CodePudding user response：

Word tables have "end of cell" characters that can get in the way when you process a cell's content.

In your case,

Int(tbl.Cell(r,c))

won't work because tbl.Cell(r,c) returns the Cell, not its value or content. To get its content, you really need

tbl.Cell(r.c).Range

But even that just specifies a block of material in the cell, so it might contain text, images etc. What you are typically looking for is the plain text of the cell, which is really

tbl.Cell(r.c).Range.Text

So you might hope that, for example, if your cell contained the text "42" the expression

IsNumber(tbl.Cell(r.c).Range.Text)

would return True. But it doesn't, because each Word table cell has an end-of-cell character that is returned at the end of the .Range.Text, and that means VBA does not recognise the text as Numeric. To deal with that, you can use

Dim rng As Word.Range
Set rng = tbl.Cell(r.c).Range
rng.End = rng.End - 1
Debug.Print IsNumber(rng.Text)
Set rng = Nothing

SOme VBA functions will ignore the end-of-cell marker anyway as they are intended to be reasonably flexible about how to recognise a number, e.g. you should be able to use

Val(tbl.Cell(r,c).Range.Text)

without running into problems.

As for which functions to use to test/convert the value, that really depends on how much you can assume about your data, how much validation you need to do and what you need to do with your data.

In a nutshell, Val looks for "bare numbers", e.g. 123, 123.45, and numbers in scientific notation. If it finds something non-numeric it will return 0. AFAICR Int and Fix work in similar ways but modify the number in different ways. IsNumeric, CInt, CDbl and so on recognise numbers taking account of the Regional Settings in your OS (e.g. Windows) and accepts/ignores grouping digits (e.g. so they might recognize 1,234,567.89 and even 1,,234,567.89 as 1234567.89 on a typical US system and 1.234.567,89 as the "same number" on a German system). CInt etc. will raise an error if they don't recognise a number.

Anything more than that and you'll probably have to find or write a piece of code that does exactly what you need. I expect there are thousands of such routines out there.

Probably worth noting that the Range objects in Excel and Word have different members. Excel has a Range.Value property but Word does not.