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 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 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 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 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.
> > >
> > >
> > >