We recently upgraded to Office 2007. I have a number of macros in
Excel. I just opened a spreadsheet I use periodically and I get a
#NAME? error in every cell with a macro reference.

These are my own personal macros. How do I enable them without opening
the doors to potential malware?

Thanks


--

Hi LurfysMa,

These are my own personal macros. How do I enable them without opening
the doors to potential malware?

You can tell Excel 2007 to trust all macros in a specific folder on your
drive:

- click the Office button and choose Excel Options
- Click "Trust Center" and click the "Trust Center Settings" button
- Click the "Trusted Locations" tab and click the "Add new location"
button to add a new folder as a trusted location.
- OK your way back to Excel.

Might be needed to restart Excel after this to make it work.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

On Mon, 11 Jun 2007 07:44:24 +0200, Jan Karel Pieterse
wrote:

Hi LurfysMa,

These are my own personal macros. How do I enable them without opening
the doors to potential malware?

You can tell Excel 2007 to trust all macros in a specific folder on your
drive:

- click the Office button and choose Excel Options
- Click "Trust Center" and click the "Trust Center Settings" button
- Click the "Trusted Locations" tab and click the "Add new location"
button to add a new folder as a trusted location.
- OK your way back to Excel.

How do I know which folder my VBA macros are in? It's not like a VB
project. I just do Alt+F11.

There are already a number of folders in that list including:

C:\Program Files\Microsoft Office\Templates\
C:\Documents & Settings\Administrator\App Data\MSFT\Excel\XLSTART\
C:\Program Files\Microsoft Office\Office12\XLSTART\
C:\Documents & Settings\Administrator\App Data\MSFT\Templates\
C:\Program Files\Microsoft Office\Office12\STARTUP\
C:\Program Files\Microsoft Office\Office12\LIBRARY\

Might be needed to restart Excel after this to make it work.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com


--

Hi LurfysMa,

How do I know which folder my VBA macros are in? It's not like a VB
project. I just do Alt+F11.

In Excel, VBA code is a part of a workbook.
Excel has a special workbook called "Personal macro workbook", with a
filename of Personal.xls (or in Excel 2007 personal.xlsb). This
workbook is hidden from the user interface and is stored in a folder
called:

C:\Documents and settings\YourUserName\Application
Data\Microsoft\Excel\XLSTART\

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

On Mon, 11 Jun 2007 09:05:53 +0200, Jan Karel Pieterse
wrote:

Hi LurfysMa,

How do I know which folder my VBA macros are in? It's not like a VB
project. I just do Alt+F11.

In Excel, VBA code is a part of a workbook.
Excel has a special workbook called "Personal macro workbook", with a
filename of Personal.xls (or in Excel 2007 personal.xlsb). This
workbook is hidden from the user interface and is stored in a folder
called:

C:\Documents and settings\YourUserName\Application
Data\Microsoft\Excel\XLSTART\

There is no such folder.

I did a Search (with hidden files included). It returned only:

C:\Documents and Settings\Administrator\Recent

Using Explorer (with hidden files viewed), I found:

C:\Documents and settings\Administrator
\Application Data\Microsoft\Excel\XLSTART\

The folder is empty. Not sure why it didn't appear in the Search
results.

No other \Excel\XLSTART found.

Puzzling...

What's the name of the file containing the macros?



--

Hi

type Alt+F11 to open the VBE
In the Immediate window type
?application.StartupPath

For me it returns
C:\Documents and Settings\Roger Govier\Application
Data\Microsoft\Excel\XLSTART

The file containing your macros will be called Personal.xls unless you
have renamed it to something else.

In my case, I don't have my personal.xls for 2003 or my personal.xlsb
stored there, and I have used registry settings to tell where Excel to
find them. If you do not have any success with finding your file and
want to go this route,come back again and I will furnish more details.

--
Regards

Roger Govier


"LurfysMa" wrote in message
...
On Mon, 11 Jun 2007 09:05:53 +0200, Jan Karel Pieterse
wrote:

Hi LurfysMa,

How do I know which folder my VBA macros are in? It's not like a VB
project. I just do Alt+F11.

In Excel, VBA code is a part of a workbook.
Excel has a special workbook called "Personal macro workbook", with a
filename of Personal.xls (or in Excel 2007 personal.xlsb). This
workbook is hidden from the user interface and is stored in a folder
called:

C:\Documents and settings\YourUserName\Application
Data\Microsoft\Excel\XLSTART\

There is no such folder.

I did a Search (with hidden files included). It returned only:

C:\Documents and Settings\Administrator\Recent

Using Explorer (with hidden files viewed), I found:

C:\Documents and settings\Administrator
\Application Data\Microsoft\Excel\XLSTART\

The folder is empty. Not sure why it didn't appear in the Search
results.

No other \Excel\XLSTART found.

Puzzling...

What's the name of the file containing the macros?



--

Hi LurfysMa,

There is no such folder.

If you record a macro and tell Excel to store it in your personalmacro
workbook, a file called personal.xlsb is created in that location.
Maybe Excel even creates the folder too.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

On Mon, 11 Jun 2007 16:43:24 +0100, "Roger Govier"
wrote:

Hi

type Alt+F11 to open the VBE
In the Immediate window type
?application.StartupPath

On the old (Office 2000) system, this returns:

C:\Documents and Settings\Administrator
\Application Data\Microsoft\Excel\XLSTART

On the new (Office 2007) system, I first got an error message saying
that macros are disabled. I then went to the Trust Center and enabled
all macros, afterwhich I got the same result as above.

On the (semi) bright side, the macros now work, but apparently my
system is completely unprotected from malware.

For me it returns
C:\Documents and Settings\Roger Govier\Application
Data\Microsoft\Excel\XLSTART

The file containing your macros will be called Personal.xls unless you
have renamed it to something else.

I searched the entire hard disk. There are no files named personal.*
at all.

In my case, I don't have my personal.xls for 2003 or my personal.xlsb
stored there, and I have used registry settings to tell where Excel to
find them. If you do not have any success with finding your file and
want to go this route,come back again and I will furnish more details.

I would like to keep my system as standard as possible. I'd like it to
be a tool -- not a career. ;-)

Thanks for the help.

--

On Mon, 11 Jun 2007 18:01:13 +0200, Jan Karel Pieterse
wrote:

Hi LurfysMa,

There is no such folder.

If you record a macro and tell Excel to store it in your personalmacro
workbook, a file called personal.xlsb is created in that location.
Maybe Excel even creates the folder too.

No such file on either the old (Office 2000) system where the macros
work or the new (OFfice 2007) system where they don't unless I enable
all macros.

--

Hi LurfysMa,

On the (semi) bright side, the macros now work, but apparently my
system is completely unprotected from malware.

Well, you can of course set security to a slightly more secure level so
that you get a security warning on which you can decide whether or not
to enable macros.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Hi LurfysMa,

No such file on either the old (Office 2000) system where the macros
work or the new (OFfice 2007) system where they don't unless I enable
all macros.

Did you copy the personal.xls from old to new system?
If so, you'd best add that folder to your trusted locations list AND
add it to the "open all files from" folder.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Hi Jan Karel

add it to the "open all files from" folder
I had never noticed that feature in the General section of Advanced
options
Thanks for drawing my attention to it.

--
Regards

Roger Govier


"Jan Karel Pieterse" wrote in message
...
Hi LurfysMa,

No such file on either the old (Office 2000) system where the macros
work or the new (OFfice 2007) system where they don't unless I enable
all macros.

Did you copy the personal.xls from old to new system?
If so, you'd best add that folder to your trusted locations list AND
add it to the "open all files from" folder.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Hi Roger,

add it to the "open all files from" folder
I had never noticed that feature in the General section of Advanced
options

It is one which used to cause frequent posts of people wondering why
all of a sudden excel opened up with all their files from the my
documents folder (back then, the name of the beast in Tools, options
was a bit obscure, something like "alternate startup file location",
luring the users to believe that they could use that to set a default
folder which would be used on file, open).

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Hi Jan Karel,

Yes I am familiar with it and its change in description as we have gone
through previous XL versions.
I thought it had just "disappeared" in XL2007 - like a few other things.
Many thanks

--
Regards

Roger Govier


"Jan Karel Pieterse" wrote in message
...
Hi Roger,

add it to the "open all files from" folder
I had never noticed that feature in the General section of Advanced
options

It is one which used to cause frequent posts of people wondering why
all of a sudden excel opened up with all their files from the my
documents folder (back then, the name of the beast in Tools, options
was a bit obscure, something like "alternate startup file location",
luring the users to believe that they could use that to set a default
folder which would be used on file, open).

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com