asked on

VBA function saved in personal.xls project returns #NAME? error

I added code for a worksheet function and saved it in a module in the PERSONAL.xls VB project. When I use this function in a worksheet formula, I'm supposed to be able to reference the values in a range of cells and return a concatenated text with the repeated separators entered into the second parameter of the function.

Function Join(RangeToJoin As Range, BetweenEach)

    Join = " "
    For Each cell In RangeToJoin
        Join = Join & cell.Value & BetweenEach
    Next cell
    
    'Get rid of the final Between
    If Len(Join) > 0 Then
        Join = Left(Join, Len(Join) - Len(BetweenEach))
End Function

Open in new window

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

janthonyn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

Also, JOIN() is the name of an intrinsic VBA function, partnered with the SPLIT() function. You should avoid naming functions and variables that are the same as your (VB) language items.

janthonyn

ASKER

I added the variable definition which was missing in the first iteration of my code