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?
MeginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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.
0
MeginAuthor Commented:
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.
0
Dany BalianCTOCommented:
if all the linestyles are the same, and all colorindexes are the same within the same block, you can use something like this:

public sub formatItem(byref obj, byval fontName as string, byval fontbold as boolean, byval fontsize as integer, byval interiorcolorindex as integer, byval colorindex as integer, byval linestyle as integer, byval weight as integer, byval wraptext as boolean, byval rowheight as double)
With obj
    .Font.Name = fontname
    .Font.Bold = fontbold
    .Font.Size = fontsize
    .Interior.ColorIndex = interiorcolorindex
    .Borders(xlEdgeTop).LineStyle = linestyle
    .Borders(xlEdgeTop).Weight = weight
    .Borders(xlEdgeTop).ColorIndex = colorindex
    .Borders(xlEdgeBottom).LineStyle = linestyle
    .Borders(xlEdgeBottom).Weight = weight
    .Borders(xlEdgeBottom).ColorIndex = colorindex
    .Borders(xlEdgeRight).LineStyle = linestyle
    .Borders(xlEdgeRight).Weight = weight
    .Borders(xlEdgeRight).ColorIndex = colorindex
    .Borders(xlEdgeLeft).LineStyle = linestyle
    .Borders(xlEdgeLeft).Weight = weight
    .Borders(xlEdgeLeft).ColorIndex = colorindex
    .Borders(xlInsideVertical).LineStyle = linestyle
    .Borders(xlInsideVertical).Weight = weight
    .Borders(xlInsideVertical).ColorIndex = colorindex
    .Borders(xlInsideHorizontal).LineStyle = linestyle
    .Borders(xlInsideHorizontal).Weight = weight
    .Borders(xlInsideHorizontal).ColorIndex = colorindex
    .WrapText = wraptext
    .RowHeight = rowheight
End With

Open in new window


and you call it using:
call formatitem(c, "Arial", True, 12, 15, xlAutomatic, xlContinuous, xlThin, True, 54.75)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MeginAuthor Commented:
That was exactly what I was looking for.

Thank you!
0
Dany BalianCTOCommented:
Glad i could help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.