VBA Function results in #NAME?

S

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
 
T

Tom Ogilvy

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

--
Regards,
Tom Ogilvy

sunfish62 said:
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:
Click to expand...
String, Optional delimiter As String = vbNullString) As String
 
S

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
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA - counting cells by colors and names to it in two different rows. 0
VBA does not recognize Excel worksheet function "indirect" 2
Can I still register a function in a DLL using ExecuteExcel4Macro REGISTER? 6
How to evaluate string form of numeric expression in VBA? 6
VBA function returns #NAME? 4
Can I evaluate a function parameter(string type) as VBA souce code 7
maxif function in vba 5
Excel Trying to code an Array Function in VBA Excel to works as a MaxIF 3

Top