How to create an add-in or function

[BenCrockett]

I’ve looked, and I am not sure I completely understand; I’m new to VBA.
I can’t remember how to create an add-in – which I drop into the program-files so the new function is always available.
I may be explaining this completely stupid, I apologize – I have a =ConcatenateRange function I want to add-in to excel, so when I pull a new workbook up, the same function is there, and I don’t need to ALT+F11 to add it each time.. I know I can create a file and add it – but I am not sure.
Anyone assist me?

Function ConcatenateRange(ByVal cell_range As range, _
Optional ByVal seperator As String) As String

Dim cell As range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long

cellArray = cell_range.Value

For i = 1 To UBound(cellArray, 1)
For j = 1 To UBound(cellArray, 2)
If Len(cellArray(i, j)) <> 0 Then
newString = newString & (seperator & cellArray(i, j))
End If
Next
Next

If Len(newString) <> 0 Then
newString = Right$(newString, (Len(newString) - Len(seperator)))
End If

ConcatenateRange = newString

End Function

[:) Sixthsense :)]

Add the code in Personal.xlsm file.

[BenCrockett]

I've added the code, but still does not work when I pull up a new workbook..

What am I missing?

[Izandol]

If the code resides in personal.xlsm you must write =Personal.xlsm!ConcatenateRange() when you use the function. If the code resides in an add-in you do not need the file name.

[BenCrockett]

I'll try making it an add-in

=Personal.xlsm!ConcatenateRange() does not work either

[BenCrockett]

Here's the file.

[MrShorty]

When I've created add-ins, it has been as simple as:
File -> Save as -> in file type field below name field, select "microsoft Excel add-in .xlam" and put the add-in where you can find it. Microsoft seems to default to an add-ins folder somewhere under your user name -- this can be a convenient place to store add-ins. Or store it wherever works for you.
Then, under Excel options, find the add-ins manager dialog. If your add-in is not included in the list, select browse, find your addin, and close the dialog. http://office.microsoft.com/en-us/ex...315.aspx?CTT=1

[BenCrockett]

Thanks -- I've done that - now shouldn't I just be able to type =ConcatenateRange? I don't know what I am doing wrong.. but nothing seems to work, I've added the code in the personal.xlsm file, in the Xlstart folder too.. I'm lost at this point..

[Izandol]

There is not a ConcatenateRange routine in the workbook you have posted?

[BenCrockett]

I went here C:\Program Files (x86)\Microsoft Office\Office14\XLSTART opened PERSONAL.xlsm file, and I see the ConcatenRange function...

[MrShorty]

When I view the attached file, I see one module containing 4 sub procedures and 0 function procedures. Nothing named "concatenaterange" or similar. Make sure the add-in file you are creating is the one that contains the UDF you want to use.

My VBA coding is almost exclusively UDF's that I store in add-ins, and the procedure I describe above is how I create the add-in. Once the add-in is created and installed, as you indicate, I simply type =myfunctionaname(arg1,arg2,) to use the function.

It seems that I've had trouble with file names if I have both a workbook and an add-in with the same file name (in this example, are you naming the add-in personal.xlam?). Make sure the add-in has a different filename from your personal.xlsx or other workbooks that you will be working with.

[BenCrockett]

So, there was a PERSONAL file in the AppData, and XLSTART and it wasn't trying to pull from both - I have no idea why.. the file in the AppData didn't have the function. So, I removed the file from the AppData, and now it works.. this was my resolution.. unsure how this happened..