Macro stops working after copying to different computer

**Bacat** · 08-05-2009, 06:02 PM

I have a Molecular Mixing Model spreadsheet that uses VB macros in Excel 2003. It works fine on several workstations at the lab, but it does not work on my laptop. I am trying to resolve this.

It fails on a paste special saying that the paste area is not the same size as the copy area. However, I suspect that there is a compatibility issue since the exact same code works on other machines.

I have verified that I have the Solver add-in, but am not sure how to check if there are others I need.

I have tried to use Tools > References in VBA debugger, but it is grayed out. I have tried highlighting the work sheet, the modules, etc. with no success.

I have enabled macros and set security to Low (temporarily) and trusted VB scripts.

I have tried to update Excel using Microsoft Update and the update has failed. I am not sure. I do have an older version of Excel 2003 than my co-worker's workstation:

My Excel: 11.6560.6568 SP2
Working Excel: 11.8220.8202 SP3

I am working to resolve the update problem as well, but if someone can help me determine what else to check, I would really appreciate it.

**Phil_V** · 08-06-2009, 04:22 AM

The VBA references will be greyed out if the code is currently running, or paused.
If you think it might be a reference difference then make sure the code is stopped and try again.

Other than that I am not sure what changed between SP2 and SP3, but perhaps if you could upload a sample workbook someone here with SP2, (and someone else with SP3), might be able to try it for you.

**Bacat** · 08-06-2009, 06:36 PM

I have resolved the SP3 problem and am now running the most current version of Excel 2003. This has not resolved the VBA problem.

I have checked references and they are the same except that I have Microsoft Access 11.0 Object Library where my co-worker has Microsoft Access 9.0 Object Library. Could this be the culprit?

I have attached the xls file in question. I am trying to run the "Processed Terrestrial OC" button on the Molecular Mixing Model sheet. It gives me an error in Line 141 of the ProcessedMolecularMixingModel Module (when I click Debug on the error). It says that the paste area and copy area do not match, but it doesn't give this error on my co-workers PC.

Really confused now.

**Phil_V** · 08-06-2009, 07:25 PM

Within Sub Terrestrial_MarineSetupResultsTable(), section 'Format cells within the molecular components portion of the results table your 'paste' area would seem to be incorrect:

Please Login or Register  to view this content.

M15 is still within your header area, it looks like you actually want the data in to be pasted into M16 downwards.

However, copying and pasting involving merged cells is always messy, as Excel will require the merged cells to be the same size at the copy location, and the destination location.
It is best to either use your macro to 'unmerge' the cells at both the source and destination, and do the copy / paste, then merge the cells again, or do away with the merging altogether and instead format the cell as "centered across selection".

**Bacat** · 08-07-2009, 04:33 PM

I discovered a new piece of information.

If I run the macro on my co-workers machine once from the original file, it works fine. But if I try to run it a second time, it fails with the same bug.

Is this because of the paste area bug?

I'm pretty fed up with this macro and am considering just re-coding the whole damn thing in python.

And now something else makes sense. When I first ran the script it told me that it couldn't compile because it was missing a component, but if I ran it a second time it would give me the paste area error. This means that my computer actually is missing a reference. When I look for it, it's solver.xla
So if I fix that, I should have the same functionality as my co-worker, but I'd rather just fix it completely...

I found this website, which has some interesting information about Solver.xla
http://www.egr.msu.edu/~lira/supp/macro.htm#pointing

But when I re-point the reference, now I get an unspecified internal error. *sigh*

I recently compressed some folders on my C drive, so I uncompressed them and discovered that this solved the unspecified internal error. Naturally, this information was not available on Microsoft's knowledge base under Excel 2003.

I can now run the macro once, which is better than nothing. I think I need to edit it to solve the paste area problem.