Excel's cell center shifted

Question

Hi community,

I have been working on a math drill program for a bit now. I had asked a similar question to this previously but I wasn't getting any good help with it. So I decided to research the problem more thoroughly and found something interesting.

Now before I begin, I just want to go over the preliminaries. Yes, I am using Microsoft Excel 2007. No, while this may work for you, it is not for me.

Okay now that, that is taken care of:

The problem I am having is when I have

ActiveCell.NumberFormat = "# ?/?"

in my my code it causes the excel's center line to be shifted to the left (that is the only way i know how to explain it).

Meaning, if you have something right justified, it will look centered, if it is centered it will be almost left justified and there is very little difference between left and center justified.

if i have

ActiveCell.NumberFormat = "?/?"

then there is none of the above problems.

The entire code of this section is as follows:

Sub test()
  Sheets("sheet1").Select
  Range("a1").Select
  For i = 1 To 10
    ActiveCell.NumberFormat = "# ?/?"
    With ActiveCell
        .Locked = False
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
    End With
    ActiveCell.Value = 33
    ActiveCell.Offset(0, 1).Select
  Next i
End Sub

Any suggestion or reason for why this is happening would be greatly appreciated. Ideally the user should be able to see a mixed number.

Further information, if I change each cell by hand to fraction then it works fine. it is only when excel does it.

Answer 1

EDIT (2)

I tried it with Excel 2007 too, and it makes no difference if I change the NumberFormat by hand or by script.

Anyway, the following script should do what you want. Add it to your Worksheet's source.

It is triggered whenever a value is changed. Then it checks if the correct column is affected (1 in my case), and then it tries to format the number with fractions, but if no fractions result from that, it uses a no-fraction-format.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Target.Cells
        If cell.Column = 1 Then
            If IsNumeric(cell.value) Then
                cell.NumberFormat = "# ?/?"
                cell.Calculate
                If InStr(1, cell.Text, "/") = 0 Then
                    cell.NumberFormat = "#"
                End If
            End If
        End If
    Next cell
End Sub

---

Original post

I've only tried it with Excel 2003, but here is no difference between setting it by hand or via VBA. Please make sure that your code does exactly the same that you do manually, just record a macro of what you do manually and run that on another cell.

The reason for the shift is that Excel reserves that space for the fractions.

 1,2 -> |      1 1/5|
 1   -> |      1    |
33   -> |     33    |
33,5 -> |     33 1/2|

Is the following what you would like to have?

 1,2 -> |      1 1/5|
 1   -> |          1|
33   -> |         33|
33,5 -> |     33 1/2|

Why would you like it in that format?

Answer 2

It is aligning the number so that the fractional parts are aligned when viewed in columns with a fixed width font.

3 1/3
2    
1 3/4
3 2/7
2 2/3
3 1/2
6

Answer 3

Actually a conditional format would work much better to center everything. See the image below for my setup.