CODEWITHSUNDEEP
excelvbaoptimizationformatting
zepeu
zepeu

Reputation: 81

How to reduce the number of repetitions for formatting many tables?

I have a nice table with a VBA code that fills it and format it automatically (I have been working on this quite a while).

It's very nice, fast, working good, but when I look at the code, there is tens and tens of lines just concerning the formatting of cells...

Is there a way to optimize this, to make it more bearable to human eye/brain?

I let you see :

Application.DisplayAlerts = False                                           'Deactivate the alerts in case the cell is filled
    With Range("A" & PosStartLine + (TPICode * 3) - 3 & ":A" & PosStartLine + (TPICode * 3) - 1)
        .Select
        .Value = FullTPICode
        .Interior.Color = RGB(220, 230, 241)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Borders.LineStyle = xlContinuous
        .Font.Size = 11
        .Font.Bold = True
        .Merge       'Merging the 3 cells
        .EntireRow.Borders(xlEdgeTop).Weight = xlMedium
        .EntireRow.Borders(xlEdgeBottom).Weight = xlMedium
    End With
    Application.DisplayAlerts = True

    'Writes down the details on the next column (as we're there... why not?)
    With Worksheets(RealData).Range("B" & PosStartLine + (TPICode * 3) - 3)
        .Select
        .Value = "Nb cars done"
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .Interior.Color = RGB(216, 228, 188)
        .Borders.LineStyle = xlContinuous
        .Font.Size = 11
    End With

    With ActiveCell.Offset(1, 0)
        .Value = "Nb cars left"
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .Interior.Color = RGB(217, 217, 217)
        .Borders.LineStyle = xlContinuous
        .Font.Size = 11
    End With

    With ActiveCell.Offset(2, 0)
        .Value = "Price"
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .Interior.Color = RGB(252, 213, 180)
        .Borders.LineStyle = xlContinuous
        .EntireColumn.AutoFit
        .Font.Size = 11
    End With

Somewhere else :

    Worksheets(RealDataReg).Activate
    With Worksheets(RealDataReg).Range(Split(Cells(1, PosStartColumn).Address, "$")(1) & PosStartLine + (TPICode * 3) - 3 & ":" & _
    Split(Cells(1, PosStartColumn + 61).Address, "$")(1) & PosStartLine + (TPICode * 3) - 3)
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
        .Interior.Color = RGB(235, 241, 222)
        .Borders.LineStyle = xlContinuous
        .Font.Size = 11
    End With

    With Worksheets(RealDataReg).Range(Split(Cells(1, PosStartColumn).Address, "$")(1) & PosStartLine + (TPICode * 3) - 2 & ":" & _
    Split(Cells(1, PosStartColumn + 61).Address, "$")(1) & PosStartLine + (TPICode * 3) - 2)
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
        .Interior.Color = RGB(242, 242, 242)
        .Borders.LineStyle = xlContinuous
        .Font.Size = 11
    End With

    With Worksheets(RealDataReg).Range(Split(Cells(1, PosStartColumn).Address, "$")(1) & PosStartLine + (TPICode * 3) - 1 & ":" & _
    Split(Cells(1, PosStartColumn + 61).Address, "$")(1) & PosStartLine + (TPICode * 3) - 1)
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
        .Interior.Color = RGB(253, 233, 217)
        .Borders.LineStyle = xlContinuous
        .Font.Size = 11
    End With

'Putting the calculation in the last cell of the table
    With Worksheets(RealDataReg)
            .Cells(PosStartLine + (TPICode * 3) - 3, PosStartColumn + 61).Formula = "=SUM(" & _
            .Cells(PosStartLine + (TPICode * 3) - 3, PosStartColumn + 1).Address(False, False) & ":" & _
            .Cells(PosStartLine + (TPICode * 3) - 3, Split(Cells(1, PosStartColumn + 60).Address, "$")(1)).Address(False, False) & ")"
    End With
'Formatting the cells with the calculations
    With Worksheets(RealDataReg).Range(Split(Cells(1, PosStartColumn + 61).Address, "$")(1) & PosStartLine + (TPICode * 3) - 3)
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlCenter
            .Interior.Color = RGB(216, 228, 188)
            .Borders.LineStyle = xlContinuous
            .Font.Size = 11
            .Select
    End With

And so on, and so on...

Do someone know a clever way to make all this formatting more efficient.nicer?As you probably saw, the formatting is each time really similar, but small differences appear (colour, the borders, the ranges etc)

I thought about making a function that I'll call, but I'll have to give it all the same informations, so, I guess it's quite pointless... As example, if I take this formatting block :

With Worksheets(RealDataReg).Range(Split(Cells(1, PosStartColumn).Address, "$")(1) & PosStartLine + (TPICode * 3) - 3 & ":" & _
Split(Cells(1, PosStartColumn + 61).Address, "$")(1) & PosStartLine + (TPICode * 3) - 3)
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlCenter
    .Interior.Color = RGB(235, 241, 222)
    .Borders.LineStyle = xlContinuous
    .Font.Size = 11
End With

It would become something like this :

Call FormatFunction( Worksheets(RealDataReg), Range(Split(Cells(1, PosStartColumn).Address, "$")(1) & PosStartLine + (TPICode * 3) - 3 & ":" & _
Split(Cells(1, PosStartColumn + 61).Address, "$")(1) & PosStartLine + (TPICode * 3) - 3), xlRight, xlCenter, (235, 241, 222), xlContinuous, 11))

But of course, there would be some parameters that are not needed in some cases, so I'll have to "aim for the good ones" when passing the parameters... It's easier to do it with the standard VBA formatting, I guess...

I imagine there is a standard clever way of doing this, but I was not able to find anything on google (it just gives me results on how to format the tables, but I already know that :D )

Thanks by advance !

Upvotes: 2

Views: 65

Answers (1)

ProfoundlyOblivious
ProfoundlyOblivious

Reputation: 1485

Maybe someone will prove me wrong (it'd be great if they did) but I don't believe there is any "right" answer to your question.

Perhaps the easiest and least redundant way forward would be start by identifying two or more formats that frequently appear together. Then defining a custom data type to pass the formats with one variable.

Alternatively, you could create a subroutine that set those same formats and you pass the object being formatted as the parameter. Though this is not something I would really recommend, too much work, to little benefit, and a bit messy.

Late edit: you can try adding a hidden template sheet to copy and paste formats instead of remaking them each time.

My personal choice is actually quite a bit more complicated, but I feel it is more elegant, and easier to both maintain and modify. Long story short: constants and enumerators are stacked into datatypes used for categories that are set by a class.

For example, I have a button states, they can be: on, off, idle, or loading. Each state is substantially distinct from the other. I start by defining all the possible base attributes as constants or enumerators(like color, font size, etc). Then I define an attribute datatype to hold them. This datatype is applicable to every button (like back color, forecolor, etc) I roll those datatypes up a level to another datatype that represents the specific state (on, off, etc). I create a variable of that type for each state. The datatypes are populated when the application initializes and I use a class to bring it all together neatly.

All that work doing the setup isn't for nothing. Any number of formats can be changed in a single line with no repetition while ensuring one change in one location apples to every object. That allows for cleaner self documenting code that is easy to write and maintain while also baking uniformity into the objects throughout the project.

For example, when used as a property:

myButton.State = isOn

Or a method:

SetButtonState myButton, isDisabled, "Not Available"

Setting it up is not exactly trivial but it isn't hard and I think it's worth the effort.

Sidenote: I could have used classes instead if those datatypes, but I consider the class list valuable real estate in the project explorer and unworthy of such minutia.

Upvotes: 1

Related Questions