I have macros in my personal.xls workbook that work just fine. I have added a
macro that requires a helper cell to enter a formula. When I enter the
formula in a helper cell in a new workbook, it doesn't work.

What is "it" that doesn't work?

The macro or the formula?

What does occur?

Maybe posting some code would aid.


Gord Dibben MS Excel MVP

On Fri, 20 Jul 2007 16:56:01 -0700, dford
wrote:

I have macros in my personal.xls workbook that work just fine. I have added a
macro that requires a helper cell to enter a formula. When I enter the
formula in a helper cell in a new workbook, it doesn't work.

This is the code you helped me with the other day. It works great if I have
the code in the workbook I am working on but does not when I have it in my
personal.xls workbook and use it in another workbook. I use
=concatrange(A1:A6) in the helper cell. It returns #NAME? I have values in
cell A1 through A6 in the same worksheet as the helper cell.

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



"Gord Dibben" wrote:

What is "it" that doesn't work?

The macro or the formula?

What does occur?

Maybe posting some code would aid.


Gord Dibben MS Excel MVP

On Fri, 20 Jul 2007 16:56:01 -0700, dford
wrote:

I have macros in my personal.xls workbook that work just fine. I have added a
macro that requires a helper cell to enter a formula. When I enter the
formula in a helper cell in a new workbook, it doesn't work.

See this page
http://www.rondebruin.nl/personal.htm

You can see there how you can set a reference to your personal file
If you not set the reference you must add the file name before the function

For example

=PERSONAL.XLS!IsoWeekNum(A1)





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"dford" wrote in message ...
This is the code you helped me with the other day. It works great if I have
the code in the workbook I am working on but does not when I have it in my
personal.xls workbook and use it in another workbook. I use
=concatrange(A1:A6) in the helper cell. It returns #NAME? I have values in
cell A1 through A6 in the same worksheet as the helper cell.

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



"Gord Dibben" wrote:

What is "it" that doesn't work?

The macro or the formula?

What does occur?

Maybe posting some code would aid.


Gord Dibben MS Excel MVP

On Fri, 20 Jul 2007 16:56:01 -0700, dford
wrote:

I have macros in my personal.xls workbook that work just fine. I have added a
macro that requires a helper cell to enter a formula. When I enter the
formula in a helper cell in a new workbook, it doesn't work.

The file name before the function worked. Thanks!

"Ron de Bruin" wrote:

See this page
http://www.rondebruin.nl/personal.htm

You can see there how you can set a reference to your personal file
If you not set the reference you must add the file name before the function

For example

=PERSONAL.XLS!IsoWeekNum(A1)





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"dford" wrote in message ...
This is the code you helped me with the other day. It works great if I have
the code in the workbook I am working on but does not when I have it in my
personal.xls workbook and use it in another workbook. I use
=concatrange(A1:A6) in the helper cell. It returns #NAME? I have values in
cell A1 through A6 in the same worksheet as the helper cell.

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



"Gord Dibben" wrote:

What is "it" that doesn't work?

The macro or the formula?

What does occur?

Maybe posting some code would aid.


Gord Dibben MS Excel MVP

On Fri, 20 Jul 2007 16:56:01 -0700, dford
wrote:

I have macros in my personal.xls workbook that work just fine. I have added a
macro that requires a helper cell to enter a formula. When I enter the
formula in a helper cell in a new workbook, it doesn't work.

I prefer to store my global macros/functions in an add-in rather than
Personal.xls

Then you don't have to preface the function with Personal.xls!


Gord

On Sat, 21 Jul 2007 06:20:01 -0700, dford
wrote:

The file name before the function worked. Thanks!

"Ron de Bruin" wrote:

See this page
http://www.rondebruin.nl/personal.htm

You can see there how you can set a reference to your personal file
If you not set the reference you must add the file name before the function

For example

=PERSONAL.XLS!IsoWeekNum(A1)





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"dford" wrote in message ...
This is the code you helped me with the other day. It works great if I have
the code in the workbook I am working on but does not when I have it in my
personal.xls workbook and use it in another workbook. I use
=concatrange(A1:A6) in the helper cell. It returns #NAME? I have values in
cell A1 through A6 in the same worksheet as the helper cell.

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



"Gord Dibben" wrote:

What is "it" that doesn't work?

The macro or the formula?

What does occur?

Maybe posting some code would aid.


Gord Dibben MS Excel MVP

On Fri, 20 Jul 2007 16:56:01 -0700, dford
wrote:

I have macros in my personal.xls workbook that work just fine. I have added a
macro that requires a helper cell to enter a formula. When I enter the
formula in a helper cell in a new workbook, it doesn't work.

The OP can also set a reference to his Personal file Gord if he want.
See my page for a example


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
I prefer to store my global macros/functions in an add-in rather than
Personal.xls

Then you don't have to preface the function with Personal.xls!


Gord

On Sat, 21 Jul 2007 06:20:01 -0700, dford
wrote:

The file name before the function worked. Thanks!

"Ron de Bruin" wrote:

See this page
http://www.rondebruin.nl/personal.htm

You can see there how you can set a reference to your personal file
If you not set the reference you must add the file name before the function

For example

=PERSONAL.XLS!IsoWeekNum(A1)





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"dford" wrote in message ...
This is the code you helped me with the other day. It works great if I have
the code in the workbook I am working on but does not when I have it in my
personal.xls workbook and use it in another workbook. I use
=concatrange(A1:A6) in the helper cell. It returns #NAME? I have values in
cell A1 through A6 in the same worksheet as the helper cell.

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



"Gord Dibben" wrote:

What is "it" that doesn't work?

The macro or the formula?

What does occur?

Maybe posting some code would aid.


Gord Dibben MS Excel MVP

On Fri, 20 Jul 2007 16:56:01 -0700, dford
wrote:

I have macros in my personal.xls workbook that work just fine. I have added a
macro that requires a helper cell to enter a formula. When I enter the
formula in a helper cell in a new workbook, it doesn't work.

I realize that Ron, but would involve creating a reference to Personal.xls in
every workbook user opened.

OK for new workbooks created from BOOK.XLT with the reference pre-set but not
for existing books.


Gord


On Sat, 21 Jul 2007 17:15:32 +0200, "Ron de Bruin"
wrote:

The OP can also set a reference to his Personal file Gord if he want.
See my page for a example

Hi Gord

Correct I only want to add this option to this thread.

Have a nice weekend

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
I realize that Ron, but would involve creating a reference to Personal.xls in
every workbook user opened.

OK for new workbooks created from BOOK.XLT with the reference pre-set but not
for existing books.


Gord


On Sat, 21 Jul 2007 17:15:32 +0200, "Ron de Bruin"
wrote:

The OP can also set a reference to his Personal file Gord if he want.
See my page for a example