VBA Function results in #NAME?

[sunfish62]

In working on a concatenation problem, I have come to realize that I
probably have use my own function to cycle through cells and
concatenate them. Below is the code I copied from this site and
modified for my own use. Ignoring for the moment that there are
probably problems with the code, I can't seem to get my spreadsheet to
recognize the function; every time I set my formula to
"=GlueText(E20:E30, A20:A30, "B")", Excel returns with "#NAME?". I
haven't used VBA in Excel before, so I know I'm missing something
really simple.


Code:
--------------------
Public Function GlueText(srch As Variant, rslt As Variant, srchstr As String, Optional delimiter As String = vbNullString) As String

Dim rArea, rCell, r&, c&, s$
If TypeOf data Is Range Then
For Each rArea In data.Areas
For Each rCell In rArea.Cells
'Note: for ranges the (formatted) Text property is used
If rCell.Text = srchstr Then s = s & delimiter & rCell.Text
Next
Next
Else
s = rslt
End If
TheEnd:
GlueText = Mid(s, 1 + Len(delimiter))
End Function

 

[Tom Ogilvy]

Put GlueText in a general module rather than a sheet module.

--
Regards,
Tom Ogilvy

In working on a concatenation problem, I have come to realize that I
probably have use my own function to cycle through cells and
concatenate them. Below is the code I copied from this site and
modified for my own use. Ignoring for the moment that there are
probably problems with the code, I can't seem to get my spreadsheet to
recognize the function; every time I set my formula to
"=GlueText(E20:E30, A20:A30, "B")", Excel returns with "#NAME?". I
haven't used VBA in Excel before, so I know I'm missing something
really simple.


Code:

String, Optional delimiter As String = vbNullString) As String

 

[sunfish62]

Well, okay. Trying not to sound stupid...

I have tried to put this in a "General" module, but still get th
error. In the VBA code editor, above the code, I have the optio
dropdown with "(General)" and "Workbook". (General) is selected.

What now

 

[Tom Ogilvy]

A general module is the one you where you do

Insert=>Module

in the VBE.

The only choice in the Left dropdown at the Top should be General.


You are in the ThisWorkbook Module - that is not a general module.

 

[sunfish62]

Hey, thanks! That solved *this* problem