Solved

2003 Excell workbook macros not running on 2007 MS Office Excell

Posted on 2009-05-13
13
706 Views
Last Modified: 2013-11-27
I have a Excell workbook that runs well on 2003, but not using 2007. Any suggestions?
0
Comment
Question by:ChiefIT
13 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 150 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

Regards,

Patrick
0
 
LVL 38

Author Comment

by:ChiefIT
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?
0
 
LVL 46

Assisted Solution

by:tbsgadi
tbsgadi earned 150 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.

Gary
0
 
LVL 33

Expert Comment

by:Norie
ID: 24384310
ChiefII

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?
0
 
LVL 38

Author Comment

by:ChiefIT
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.

GUYS, ALL YOUR HELP IS APPRECIATED. MY BACON IS ON THE FIRE, SINCE THIS IS THE CO'S COMPUTER AND IS IMPORTANT TO THE SAFETY OF THE SHIP. I am good with IT and servers, networks, and telelphony, not M$ Office.

0
 
LVL 38

Author Comment

by:ChiefIT
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.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Expert Comment

by:Norie
ID: 24386226
Chief

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.
0
 
LVL 38

Author Comment

by:ChiefIT
ID: 24387542
OK:

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.

Dep-Sitka-10-20-08.XLS
0
 
LVL 33

Expert Comment

by:Norie
ID: 24388571
Chief

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.
0
 
LVL 38

Author Comment

by:ChiefIT
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?

0
 
LVL 38

Author Comment

by:ChiefIT
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.
0
 
LVL 33

Accepted Solution

by:
Norie earned 200 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.
0
 
LVL 38

Author Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now