Macro in personal.xls does not work

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
 
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
 
The OP can also set a reference to his Personal file Gord if he want.
See my page for a example
 
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
 
Back
Top