Macro isn't compatible with other users

**jlt199** · 04-09-2012, 04:28 PM

Hey, I've written two macros on my computer that I have successfully run on my computer in both Excel 2003 and 2007. My collegue is running Excel 2007 and is finding that the macro keeps crashing. He is unable to debug the error as Excel needs to be restarted once crashed.

I don't understand the error, or why the macro doesn't work for him when it does me and I don't even know where to start looking for a solution.

Any suggestions would be gratefully received

The error message he got whn I was watching him is attached.

errorMessage.JPG

**Leith Ross** · 04-09-2012, 08:57 PM

Hello jlt199 ,

This is an OLE automation error that is telling you the object is no longer loaded or accessible. There are several causes of this but to help pin point the cause I would need to see the code. Any information you provide about the user's system will also help.

**royUK** · 04-10-2012, 01:58 AM

Does the code use a DatePicker or Calendar Control?

**jlt199** · 04-10-2012, 11:37 AM

it uses isdate to test if the string entered is a date, other than that no other date stuff.

I emailled the other user the excel file, who then saved it locally on his computer. He is running Excel 2007, on Windows 7 pro. Which should be the same as me. Would the fact that I also have Excel 2003 make a difference?

I can't replicate his problem, so don't know which part of the code is the problem. I can't really upload all of the code and don't know which are the relevent parts.

Maybe if I give a rough idea of the flow:
1) Run the macro that shows a form
2) the user enters a bunch of inputs, either strings, numbers or addresses
3) The inputs are checked
4) A bunch of analysis is done on the data input
5) The form has a drop down box that allows the user to either select the output to be saved to the active workbook or a new workbook. The output, a couple of worksheets and a form are output to this location

Is anything there ringing any alarms?

Thanks
Jane

**jlt199** · 04-10-2012, 12:03 PM

It seems to be an intermittent problem. Sometimes he can get it work correctly, which is leaving me even more baffled than it crashing every time would.

**Leith Ross** · 04-10-2012, 12:49 PM

Hello jlt199,

It doesn't seem that the program is doing anything unusual. I do wonder about step 5 - the output. Is the form you mention a worksheet or a VBA UserForm? What type of analysis is being done?

**jlt199** · 04-10-2012, 01:07 PM

The form is a VBA userform.

I'm calculating a bunch of theoretical curves. The points I save to an array.

When outputting to either the active work or a new workbook, based on the selection in the form, The arrays are printed to the workbook. One array per column. Each column is defined as a named range and plotted into a chart.

userForm.JPG

**royUK** · 04-10-2012, 02:01 PM

I think you are using refedits which can be problematic. Check in the VB Editor Tools-> References & see if any are marked missing

**shg** · 04-10-2012, 02:49 PM

See http://peltiertech.com/WordPress/ref...l-alternative/ for an alternative to a refedit control.

**jlt199** · 04-10-2012, 03:01 PM

Do you really think it could be the refedits? He can fill the fields with info and then hit the OK button. At which point the only use of the refedits is to read the strings in them