Forum Discussion
how to write function or subroutine
I have a routine changing background and font of headers (shade of green, red and blue) and it keeps using the codes below. The 3 bold-italic are the ones that are changing, the rest are constant. Can I write below as function and make the 3 bold-italic as variables and call it in 3 private subs (private sub_HdGreen()....etc).. As it is, I keep writing the same codes for the three subs. (quite long and looks messy, it keeps the job done though..)
I hope I make myself clear
many thanks
Function Header_Color(ptc as ?, pts as long, fts as long) (How to write this line, is it function?)
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6 (assign variable as ptc)
.TintAndShade = -0.499984740745262 (assign variable as pts)
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.799981688894314 (assign variable as fts)
End With
End Function
when calling it from a private sub - can I write it as Header_Color(xlThemeColorAccent6 ,-0.499984740745262,0.799981688894314)..or?
I am not sure that it should be a function.
When using function, there should be "as variant/long" at the first statement of the function. This determine the type of variable return.
You may just use
Sub Header_Color (ptc as variant, pts as long, fts as long)
and then use
Call Header_Color (xlThemeColorAccent6 ,-0.499984740745262,0.799981688894314)
in your main program.
Mr Chan
Thank you for your reply.
You are very helpful.
many many thanks
