asked on

VBA Access: Repeated Formatting - How do I create a public function for this?

Hi there!
I have some VBA code that involves a lot of formatting. My code is super long because of this, which is...distracting.

I would like to put the formatting part of the code into a public function so that I can just call the formatting with one word, but I am not sure who to do this with formatting. It isn't really a function (no "sub" at the front and no "end sub" at the end). It is just a chunk of code that formats. And I use it repeatedly, so it just be so great if I could make this work.

Any ideas?

Dale Fye

Can you give more details about what it is formatting?

Generally, you would simply cut the code that you want to create a procedure from and paste it into a new code module. But depending on what you are formatting, you might also have to pass some objects. I do this with Excel all of the time, one subroutine that accepts objects for XL (Excel application), WBK (workbook), and SHT (worksheet). It then loops through the header row looking at the column names and formats the columns according to a predefined parameters.

Then you simply call that subroutine from your original code, making sure you pass in all of the objects that are needed to perform whatever formatting you are doing.

Megin

ASKER

This is the type of chunk that I would like to shorten:

With C
    .Font.Name = "Arial"
    .Font.Bold = True
    .Font.Size = 12
    .Interior.ColorIndex = 15
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).Weight = xlThin
    .Borders(xlEdgeTop).ColorIndex = xlAutomatic
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlThin
    .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlThin
    .Borders(xlEdgeRight).ColorIndex = xlAutomatic
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlThin
    .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideVertical).ColorIndex = xlAutomatic
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).Weight = xlThin
    .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
    .WrapText = True
    .RowHeight = 54.75
End With

Open in new window

I have this all over my code.

It is formatting an Excel spreadsheet.

ASKER CERTIFIED SOLUTION

Dany Balian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Megin

ASKER

That was exactly what I was looking for.

Thank you!

Dany Balian

Glad i could help :)