VBA Automation Error

[-Wayner-]

Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have come across a seriously frustrating problem and one which i am genuinely completely stumped as to what the solution might be.<o></o>
<o></o>
I have a tool which uses a user defined function:<o></o>
<o></o>

[COLOR=black][FONT=Verdana]Function IsFormula(c)[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]On Error Resume Next[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]IsFormula = c.HasFormula[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End Function<o:p></o:p>[/COLOR][/FONT]

<o></o>
<o></o>
This drives the following formula:<o></o>
<o></o>

[COLOR=black][FONT=Verdana]=IF(ISERROR(IF(isformula(AE21),IF(AND(NOT(BP21=""),Apply_newrange_forecast="Yes"),AE21,IF(OR(Z21<D21*minfcastfactor,ABS(AC21-Z21)>Z21*fcastvarallow),AC21,Z21)*F21),AE21)),"",IF(isformula(AE21),IF(AND(NOT(BP21=""),Apply_newrange_forecast="Yes"),AE21,IF(OR(Z21<D21*minfcastfactor,ABS(AC21-Z21)>Z21*fcastvarallow),AC21,Z21)*F21),AE21))<o:p></o:p>[/FONT][/COLOR]

<o></o>
<o></o>
Amongst a few other smaller formulas in the document which run off the same user defined function.<o></o>
<o></o>
Everything works in my template document without any problems, and i can't foresee any issues or reasons why it would throw up an issue. I have used it multiple times - dropping data and in processing it as i need to.<o></o>
<o></o>
However something strange has started to occur. I use multiple versions of this document - all the same, but with different data dropped in to be processed. I am now finding that when i open some of the saved versions (but not all?!) the document throws up VBA 'Automation Error'. It gives me no more details than that.<o></o>
<o></o>
I select 'ok' (help is the only other option) and it brings up my vba project. I fi open the module containing the above code the first line is highlighted in yellow. If i exit the debugger it goes straight back to the auto error diag box, a vicious circle which prevents the user accessing their data.<o></o>
<o></o>
If i make a change to the code - by removing the on error line it them allows me back into the workbook, but none of the code stored in individual sheets works. (I have tried the function without the on error line and it still causes the same issue - where adding it solves the problem.<o></o>
<o></o>
Digging a bit deeper it appears that when the error occurs something strange is happening to my sheets in VBA - this is where my novice shows through (if it hasn't already!).<o></o>
<o></o>
For some reason my sheets have duplicated - <o></o>
<o></o>
Whereas before it would show<o></o>
<o></o>
Sheet1 (name)<o></o>
Sheet2 (name)<o></o>
<o></o>
etc.<o></o>
<o></o>
It now shows<o></o>
<o></o>
Sheet1<o></o>
Sheet2 (which initially appear in blue?) - with a different icon<o></o>
<o></o>
then my original sheet 1 is now shown as:<o></o>
<o></o>
Sheet7 (name)<o></o>
Sheet8 (name)<o></o>
<o></o>
These sheets do not contain the original code - this is located in the new sheet1 (with no name). Therefore none of my buttons in the worksheets work anymore.<o></o>
<o></o>
I hope this makes some sense as to what is happening. I am absolutely lost as to what is happening - it is almost as though the document is self-replicating its sheets which is stopping it from working. The difficulty is it doesn't even happen every time - it's only on certain sheets and it's proving really hard to isolate what the issue is!<o></o>
<o></o>
As a note i am using the document on Excel 2003 - Work Pc's, but when i try it on my laptop Excel 2010 it doesn't give me the automation error - but i still have instances where the buttons on the sheet don't work - i.e. the code and sheet names are moved around when viewed in VBA.<o></o>
<o></o>
Any ideas at all as to what might be causing this - and more so how i might rectify it would be absolutely hugely appreciated.<o></o>
<o></o>
If i need to try explain anything more clearly please let me know. I really hope someone knows what i am talking about or has seen this before...?!<o></o>
<o></o>
Thanks in advance for any help.<o></o>
<o></o>
Cheers<o></o>
Wayner<o></o>
<o></o>
<o></o>
</D21*minfcastfactor,ABS(AC21-Z21)></D21*minfcastfactor,ABS(AC21-Z21)>

 

[dave3009]

There's only one line of code so I don't think the On Error is necessary

try this

Function IsFormula(c As Range) As Boolean
  IsFormula = c.HasFormula
End Function

 

[-Wayner-]

Dave,

Thank you for the quick response. I have changed the code and am now trying to recreate the issue to see if it solves the problem.

Could his function erroring be causing all of the odd goings on with my sheets being effectively duplicating and therefore removing all the functionality of my document?

I'm just really struggling to comprehend what is going on!

Thanks again.
Wayner

 

[dave3009]

One of the problems might be the fact that you have this in multiple documents. I would expect to see an name ambiguity error though and not an automation error.

Also see here

http://www.mrexcel.com/forum/showthread.php?t=17200

 

[-Wayner-]

The issue you have identified in the above thread looks very similar to the one i am having, but the solution offered (regarding speech marks etc.) doesn't seem relevant to the issue i am experiencing.

It just seems such a strange problem!

I am not able to recreate the error at this moment (sods law in know!), so i will continue to run with the updated code and see if it happens moving forward. I'll post back with any further updates.

Thanks for your input - i welcome any further insight which might get me to the bottom of this issue.

Thanks
Wayner