In VBA trying to Align Text either Horizontally or Vertically

[Mackeral]

Looking in Google to find out about aligining, you find references to xlVAlign and xlHAlign which define code to do this aligning. So you would think you could write a subroutine to do this. But you can't.

The basic problem is that both sets of code (both horizontal and vertical alignments) reference the constant "xlCenter", and VBA doesn't allow you to access a constant more that one time. It will also not allow you to redefine it with another line in an Enum like "YlCenter = xlCenter". There are also some other variables that are shown in the Google documentation that I found just don't work in Alignments as they work in VBA now: xlJustivied, xlDistributed.

To get around this problem, you will need to execute the following code to make these alignment changes:

Enum Horizontal_Aligns
    hrz_left = xlLeft
    hrz_right = xlRight
    hrz_center = xlCenter
End Enum

Enum Vertical_Aligns
    vrt_Top = xlTop
    vrt_Middle = xlCenter
    vrt_Bottom = xlBottom
End Enum

Sub Cells_Align_Horizontal(SHEET as Worksheet, Rng, _
                           Optional What_to_Do As Horizontal_Aligns = hrz_left)
    ' Do Horizontile Alignment of cells.    
     SHEET.Range(Rng).HorizontalAlignment = What_to_Do        
End Sub ' Cells_Align_Horizontal


Sub Cells_Align_Vertical(SHEET as Worksheet, Rng, _
                         Optional What_to_Do As Vertical_Aligns = vrt_Center)
    ' Do Vertical  Alignment of cells.      
     SHEET.Range(Rng).VerticalAlignment = What_to_Do        
End Sub ' Cells_Align_Vertical

Mac Lingo

 

[6StringJazzer]

I am really puzzled by this. Doing this type of alignment is trivial. Maybe I am misunderstanding what you need to do.

VBA doesn't allow you to access a constant more [than] one time

I don't understand what you mean by this. In VBA you can reference a constant as many times as you want.

It will also not allow you to redefine it with another line in an Enum like "YlCenter = xlCenter".

But that's exactly what you did in your example.

Aligning cell content is very easy:

    With Rng

        .HorizontalAlignment = xlRight
        .HorizontalAlignment = xlLeft
        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlTop
        .VerticalAlignment = xlCenter
        .VerticalAlignment = xlBottom

    End With

 

[Mackeral]

Yes, this is what I discovered by messing with the code. I was trying to read the documentation, but I never found any documentation that I made sense to me.

I included the code so the next person who might be asking this question would find an easy answer. I looked through a lot of examples originally thinking that I could just use an Enum variable. I did find two programs that you had to actually run to get a subroutine to work, but figured out a much simpler way to do it. And I'm more interested in creating code that I can use over; it's lots easier to debug. I generally find that when I have to write a particular function, it turns out that I have to use it again anyway, so...

 

[6StringJazzer]

I guess I don't see what's easy about this answer. I don't know why you need an Enum for this. It sounds like you have a complex solution to a problem that doesn't exist.