Global function

[gbrandreth]

Hi all

I have a function I want to be available to any s/sheet.

I have saved it as an addin, and activated it in my addin list.

I can see its the in VB editor, but when I use it in a s/sheet, I get #NAME.

It works fine if I copy it into the local s/sheet.

Am I missing something to make it globally available....the code is contained within a module, assuming that is the right type?

Thanks

Graham

 

[RoryA]

If it's in a normal module, that should be fine as long as you didn't mark the module or function private. Do you have macros enabled? How are you calling the function and what does the code look like?

 

[gbrandreth]

The module is named concat1, and does not have any initial decalrations (Private or public)

The code is....

Function Concat1(myRange As Range, Optional myDelimiter As String)
Dim r As Range
Application.Volatile
For Each r In myRange
Concat1 = Concat1 & r & myDelimiter
Next r
If Len(myDelimiter) > 0 Then
Concat1 = Left(Concat1, Len(Concat1) - Len(myDelimiter))
End If
End Function

Basically it allows me to concatenate a range of cells as a range, rather than having to specify them seperately

I am calling the function by entering the folling in a cell....

=concat1(B1:Z1)

 

[Sektor]

Your function works as expected. Check input parameters.

 

[gbrandreth]

I know my fuction works, my problem is I have to copy it into the s/sheet I want to use it in.

My question is how do I make it available to all spreadsheets without copying it.

I have added it as an addin, but its still not available globally

 

[Sektor]

Put it into PERSONAL.XLSB file.
To make it appear, begin recording some macro and save it into Personal. This way Personal workbook will appear.

 

[RoryA]

Change the name of the module to something else. You should never give a module and function/sub the same name.

 

[gbrandreth]

I tried Rorya suggestion 1st, and that seems to have solved it. Its now working in all sheets

Thanks for the help

Graham