Cannot get vba function to execute

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Donal McWeeney - 26 Jan 2007 12:15 GMT

I had a excel file with a custom vba function that worked fine.

I upgraded to 2007 and saved the file as a macro enabled workbook (.xlsm)
and cannot get the custom function to get called - it keeps displaying as a
#NAME? error.

In the Macro Settings "Enable all macros..." is enabled and "trust access to
the VBA.." is set.

The file is on a share and that share is added as a "Trusted Location"

The formula is listed in the user defined formula section of the insert
formula dialog.

The formula is in a module as follows:

Option Explicit

Public Function DoCalculate() As Single

DoCalculate = 1000

End Function

and is being called as follows: =DoCalculate()

Any help greatly appreciated

Thanks

Donal

Reply to this Message

Donal McWeeney - 26 Jan 2007 13:09 GMT

I think it is because I have the file password protected. If I remove the
encryption it works but if I put it back on so that I must specify a
password to open it cant see the vba functions.

Any idea how to get aroudn this

Thanks

Donal

Reply to this Message

Wei Lu [MSFT] - 29 Jan 2007 07:28 GMT

Hello Donal,

Have you tried just save your excel file as the original Excel 97-2003
format with .xls extension?

928613 You receive a message when you try to run a macro in an encrypted
workbook in Excel 2007: "Because of your security settings, macros have
been disabled"
http://support.microsoft.com/default.aspx?scid=kb;EN-US;928613

Sincerely,

Wei Lu

Microsoft Online Community Support

==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Reply to this Message

Wei Lu [MSFT] - 31 Jan 2007 11:13 GMT

Hi ,

How is everything going? Please feel free to let me know if you need any
assistance.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message