passing Excel system values (constant) of properties as arguments

[Guest]

I want to pass system constants as arguments to a procedure or function. In
my case, I want to pass the constants xlThick/xlMedium of the 'Weight'
property to a procedure.
How do I have to do this?
many thanks
roland

 

[Lonnie M.]

Roland, this is how I have handled it:

Sub Test()
Call BoxBorder(xlThin)
End Sub

Public Sub BoxBorder(myWeight As Variant)
With Selection.Borders(xlEdgeLeft)
..LineStyle = xlContinuous
..Weight = myWeight
..ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
..LineStyle = xlContinuous
..Weight = myWeight
..ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
..LineStyle = xlContinuous
..Weight = myWeight
..ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
..LineStyle = xlContinuous
..Weight = myWeight
..ColorIndex = xlAutomatic
End With
End Sub


HTH--Lonnie M.

 

[Rob van Gelder]

Just noted your example - in case you didn't know, the Range object has a
method called BorderAround
eg.
Selection.BorderAround Weight:=xlThick

 

[Lonnie M.]

Thanks--I am always on the lookout for a better way, and you certainly
provided one.