2003 Excell workbook macros not running on 2007 MS Office Excell

Posted on 2009-05-13
Medium Priority
Last Modified: 2013-11-27
I have a Excell workbook that runs well on 2003, but not using 2007. Any suggestions?
Question by:ChiefIT
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 600 total points
ID: 24380067
Hello ChiefIT,

Please do the following:

1) Explain what you mean by not "running well"
2) Post the code or the whole workbook (first obfuscating or removing sensitive data, of course!)
3) Check the macro security setting in 2007


LVL 39

Author Comment

ID: 24380134
Explanation of the workbook.

The workbook is for trim and stability of our ship. It takes Engineering data and calculates the trim and stability Using some user input.

The calculated values, that are done through macros, come up with !!VALUE!! in the box. It appears that the macros are NOT running at all.

I don't know how to better explain that.

I can't see any reason, why I can't provide you a workbook. There is no PII in there.


I would like to check the macro security setting in 2007, first. 2007 is cumbersome to me. How do I navigate to that setting, since 2007 Office is a fresh install and I always suspected a security setting all along?
LVL 46

Assisted Solution

tbsgadi earned 600 total points
ID: 24382190
To change macro security settings in Excel 2007, the Developer tab must be displayed on the ribbon. If the Developer tab is not displayed:

1.Click the Office button.
2.Click Excel Options
3.In the Popular Category, place a check mark next to Show Developer Tab in the Ribbon and click OK.
4.On the Developer tab, click Macro Security. In the Macro Settings category, select Enable all macros.
5.Click OK to exit all dialog boxes. You may need to close, then re-open Excel.


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 36

Expert Comment

ID: 24384310

Why do you think the #VALUE! is happening because the code isn't running?

Are you even sure the code isn't running?

What 'box' are you referring to?

And if you can provide a workbook why not attach it?
LVL 39

Author Comment

ID: 24385800
I am sorry, I am just not really schooled on Macros. In my limited experience, macros look like little VBS scripts to perform a mathematical function. Since the macros work on a 2003 Office based machine and not a 2007 Office based machine, I automatically assumed 2007 might have a security setting that prevents the macros from running.

We currently have two machines that are suppose to run these macros. As explained earlier, this workbook was created for trim and stability of the ship. The chief Engineer can run the macros with no problem using a 2003 Office machine. The CO can not, using the 2007 Office machine. Prior to the install of 2007, the CO's machine ran this workbook fine.

I find Office 2007 a little cumbersome to navigate, and when I found and go into the macros, (unlike 2003 Office), I can't see the VBS that is associated with the macro, I just see the title of the macro.  

So, I have been contemplating if 2007 is backwards compatible, or if there is a security setting preventing the macros from being run.

I figured the inability to run macros, prevents the macro data from populating the Workbook.

I will send a copy of one of the Trim and stability workbooks when I confirm no Personal Identifiable Info is within the WB.

I would like to make sure that there is no security setting that prevents macros from running prior. I have done what little research I can and found out that, by default, 2007 prevents macros from running for security reasons. I really think this may be the culprite but can't find the security settings to adjust them. So, it could be as easy as adjusting security and allowing the macros to run.


LVL 39

Author Comment

ID: 24385883
I am going to try what tbsgad and matthewspatrick suggested in changing security settings real quick. If no worky, I will send you a workbook.

LVL 36

Expert Comment

ID: 24386226

If, and I mean if, this is a problem with the code then it code be due to one(or more) of many things, security being one.

If it's not security then the most likely source of the problem is something to do with the code itself.

How exactly is the code being used?

Is it for user defined functions that perform calculations and return the results to a worksheet?

By 'box' do you simply mean a cell on a worksheeet.

As to the compatibilty issue, it could be a factor but that could have nothing to do with the actual code/macros - as far as I know 2007 is backward compatible.
LVL 39

Author Comment

ID: 24387542

We enabled "all macros" to run under security settings as suggested above. That didn't work. I read somewhere that PASSWORD Encrypted macros will not run on a 2007 Office machine. So, I REALLY think we are on the right track with security settings of 2007 office.

The macros were created by an engineering firm, (not by ship personnel). None of us on ship have the password. The whole workbook populates all Cells on my machine running 2003 office and the Chief Engineer's machine with 2003 office. They use to populate all cells on the CO's machine until we upgraded him from 2003 office to 2007 office. Now the CO gets !!VALUE!! in the cells the macro is suppose to populate. So, I know it is not a problem with the coding. I think it has to do with encrypted macros.

Our workbook is attached for you to see. I don't have the password to look at the macros myself. So, I can't help you with that.

In conclusion:
Since these macros run on two 2003 machines and populate the workbook accordingly while 2007 does not, I really believe 2007's inability to run Encrypted macros is the problem. I read about this issue somewhere, but need to find the fix.

If the macro coding were off, then neither 2003 or 2007 will populate the cells.

LVL 36

Expert Comment

ID: 24388571

Why do you think it's not a problem with the code?

I think you might be getting your terminology mixed up, macros [b]and[/b] code are basically the same thing.

The main difference is in how they are used.

Also getting the error #VALUE! does not indicate that any code/macro hasn't been executed.

It's more indicative of the code/macro not running [b]correctly[/b].

And how do you actually know the code/macros are running when you use 2003?

Finally, and I'm sorry to say this, but I can't see anyway we can help you - not only is the code password protected so is the workbook/worksheet.

The only thing I could find out about the code was the names of a bunch of functions/subs that I couldn't access.

I found them by opening the VBE (ALT+F11), then the Object Browser (F2) and selecting Working.xls from the top dropdown.

And the subs appear when you goto Tools>Macros... but again you can't access the code but you can run it.
LVL 39

Author Comment

ID: 24388578
This is the problem, I am certain of it: http://support.microsoft.com/kb/927150

I don't have an Antivirus that supports the scanning of  Microsoft API. So, these macros that populate the cells are disabled. In other words, macros are disabled for this document because they are password protected and unable to be scanned by the AV package I have. On the two 2003 office machines, the security permit us to run the macros uncontested.

Can you help me overcome the security settings of 2007 Office?

LVL 39

Author Comment

ID: 24388881
Correct me if I am wrong, on this:

Macros are all VBS scripts that perform a function or subfunction. There should be no difference between the VBS script of a 2003 Office Excell workbook and a 2007 Office Excell workbook. So, macros of a 2003 workbook should run for a 2007 workbook. I don't understand what you mean by [b] and [/b]. In my view, this is comparable to a word .txt file being the same as a notepad .txt file. At least I thought I was comparing apples to apples, meaning the code of a VBS to the code of a VBS.

The idea that a macro is simply a vbs script, leads me to believe that same vbs script should run for either 2003 or 2007 office packages unless there is something within the security settings that prevent macros from running on 2007 office packages. So far, on any 2003 Office machines I open this workbook on, all cells populate with data. On the 2007 box, all cells populate with #value! as if the macros are not running.

Is there something I am missing coding? Are there different forms of compiling and running VBscripts like there might be in C and C++?

I suck at coding (programming). I try to avoid it like a bad habbit.
LVL 36

Accepted Solution

Norie earned 800 total points
ID: 24389099
Macros are not VBS.

The main code used in Excel (and Word etc) is Visual Basic for Applications (VBA).

And I'm unsure about your comparison between a word.txt file and a notepad.txt file, they are just text files.

If there is any difference between 2003 and 2007 then it could be a compatibility and is very unlikely to have anything to do with security settings.

If it was a problem with the security settings then you probably wouldn't even see #VALUE!.

Getting that error is not likely to be the result of any code/macro/script not running for whatever reason.

There are other possibilities - problems with the data, the code not taking into account changes from 2003 to 2007.

For the latter I mean things like the increase of the no of rows.

But without seeing the code there is no way to tell.

And as to overcoming security settings?!?!? They are there for a reason.

Perhaps you should be talking to the people that supplied the software in the first place.
LVL 39

Author Comment

ID: 24397229
I appreciate the efforts all of you made>

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question