2003 Excell workbook macros not running on 2007 MS Office Excell

I have a Excell workbook that runs well on 2003, but not using 2007. Any suggestions?
LVL 39
Who is Participating?

Improve company productivity with a Business Account.Sign Up

NorieConnect With a Mentor VBA ExpertCommented:
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.
Patrick MatthewsConnect With a Mentor Commented:
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


ChiefITAuthor Commented:
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?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

tbsgadiConnect With a Mentor Commented:
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.

NorieVBA ExpertCommented:

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?
ChiefITAuthor Commented:
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.


ChiefITAuthor Commented:
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.

NorieVBA ExpertCommented:

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.
ChiefITAuthor Commented:

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.

NorieVBA ExpertCommented:

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.
ChiefITAuthor Commented:
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?

ChiefITAuthor Commented:
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.
ChiefITAuthor Commented:
I appreciate the efforts all of you made>
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.