create a new function

[dg]

Hi

I would like to create a new function that manipulates text. Say I want to
concatenate 2 columns as =A1&A2. Can you tell me how pls.

--
IMPORTANT: This email is intended for the use of the individual addressee(s)
named above and may contain information that is confidential privileged or
unsuitable for overly sensitive persons with low self-esteem, no sense of
humour or irrational religious beliefs. If you are not an intended
recipient, any dissemination, distribution or copying of this email is not
authorized (either explicitly or implicitly) and constitutes an irritating
social faux pas. No animals were harmed in the transmission of this email,
although the dog next door is living on borrowed time. Those of you with an
overwhelming fear of the unknown will be gratified to learn that there is no
hidden message revealed by reading this backwards (ereht si ro), however, by
pouring a complete circle of salt around yourself and your computer you can
ensure that no harm befalls you and your pets.
If you have received this email in error, please add some nutmeg and egg
whites, whisk, and place in a warm oven for 40 minutes.

 

[Jerry W. Lewis]

Your question covers a very big topic. My reply will only scratch the
surface.

Functions should be written in a VBA code module. From the menu select
Tools|Macros|Visual Basic Editor
Insert|Module

A bare bones conactenation function would then be

Function Concat(a, b)
Concat = a & b
End Function

If appropriate, you could explicitly declare the types of the inputs and
output

Function Concat(a as String, b as String) as String
Concat = a & b
End Function

If you want to be able to specify an arbitrary number of arguments, like
Excel's built in CONCATENATE() function, you would expand the code to

Function Concat(ParamArray args() As Variant)
Dim arg As Variant
Concat = ""
For Each arg In args
Concat = Concat & arg
Next arg
End Function

For any of these codes, the equivalent of =A1&A2 or =CONCATENATE(A1,A2)
in a worksheet cell would then be =Concat(A1,A2)

Jerry