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.

I meant to say if you use

Set R1 = Range("B7:C12")

You get ...

Barb Reinhardt





"Gary" wrote:

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,

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

Thanks.

"Barb Reinhardt" wrote:

I meant to say if you use

Set R1 = Range("B7:C12")

You get ...

Barb Reinhardt





"Gary" wrote:

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.

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 he
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 wrote:

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.

--

Dave Peterson

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.


"Dave Peterson" wrote:

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 he
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 wrote:

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.

--

Dave Peterson

Oops, wrong thread.

Barb Reinhardt




"Gary" wrote:

Barb,

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

Thanks.

"Barb Reinhardt" wrote:

I meant to say if you use

Set R1 = Range("B7:C12")

You get ...

Barb Reinhardt





"Gary" wrote:

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.