Checking the text alignment of a cell in Excel using Word VBA 2007

Question

I know that the following Word VBA 2007 code

objExcel.ActiveWorkbook.Sheets("Book1").Cells(3, 4)

can get the text inside the cell D3 (Row 3, Column 4) of an opened Excel ~~workbook~~ worksheet named "Book1". But how to get the way of alignment (i.e. left, right, center or justified) of the cell? Thanks!

Unknown Unknown · Accepted Answer · 2015-09-27T18:56:52

There are two aspects that govern the horizontal cell alignment. The first is a forced non-default cell alignment that has been applied manually or through code. The second is the xlGeneral format which may be right, left or center aligned according to the nature of the data.

With objExcel.ActiveWorkbook.Sheets("Book1")
    Select Case .Cells(3, 4).HorizontalAlignment
        Case xlRight        ' equal to -4152
            Debug.Print "The cell is right-aligned."
        Case xlCenter       ' equal to -4108
            Debug.Print "The cell is center-aligned."
        Case xlLeft         ' equal to -4131
            Debug.Print "The cell is left-aligned."
        Case xlGeneral      ' equal to 1
            Select Case Application.Evaluate("TYPE(" & .Cells(3, 4).Address(0, 0, external:=True) & ")")
                Case 1
                    Debug.Print "The cell is right-aligned."
                Case 2
                    Debug.Print "The cell is left-aligned."
                Case 4, 16
                    Debug.Print "The cell is center-aligned."
                Case Else
                    Debug.Print "The cell is part of an array."
            End Select
    End Select
End With

Are you sure it is your worksheet and not your workbook that is called Book1?

Checking the text alignment of a cell in Excel using Word VBA 2007

1 Answers