Macro in personal.xls does not work

[Guest]

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.

 

[Gord Dibben]

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

 

[Guest]

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

 

[Ron de Bruin]

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)

 

[Guest]

The file name before the function worked. Thanks!

 

[Gord Dibben]

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

 

[Ron de Bruin]

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

 

[Gord Dibben]

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

 

[Ron de Bruin]

Hi Gord

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

Have a nice weekend