How to add this function to Personal Workbook

[Gary]

I have this *.bas file with the following code -

Attribute VB_Name = "Module1"

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.Text) > 0 Then sbuf = sbuf & cell.Text & ", "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function


How would I add this as a module or add-in so it would be available for use.
Right now, if I import the file it works but would rather not have to do
that for each new file.

Thanks.

 

[Barb Reinhardt]

I meant to say if you use

Set R1 = Range("B7:C12")

You get ...

Barb Reinhardt

 

[Gary]

Barb,

I don't see your first response. Could you repost.

Thanks.

 

[Dave Peterson]

So why not just add it to your personal.xls (or .xlsm or .xla) workbook?

You can copy and paste everything (ignore the "attribute" line) into a new
module in your personal workbook.

If your personal workbook is .xls, then use:
=personal.xls!concatrange(a1:a10)

if your personal workbook is .xla, then you can use:
=concatrange(a1:a10)


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Gary]

Dave,

Thanks. I was trying that but didn't realize I needed the to include the
personal.xls file name in the call.

Working fine.

 

[Barb Reinhardt]

Oops, wrong thread.

Barb Reinhardt

Barb,

I don't see your first response. Could you repost.

Thanks.