Excel97 - Custom Function Causes Code To Halt

[SHardy]

I have a file, within which I have created a couple of custom worksheet functions. These functions simply parse the contents of the referenced cell. I need these to seperate out certain parts of the text. These functions seem to work fine.

I also have some code, in the same workbook, that opens a text print file, copies formulas to extract required information and filter required records to another sheet.

However the code stops before it has finished, but doesn't give any error messages. The point at twhich it stops is after it copies and pastes the formulas, then recalculates. All the cells recalc OK except those that use the custom functions detailed above. These show as #VALUE.

If I F2 & ENTER these cells, they recalc OK. If I copy the formulas, prior to running the code, they recalc OK.

Why would these functions not be recalculating properly and stopping my code from running?

I can post my code if it would be of help.

T.I.A.

 

[dsi]

Have you included any error handling functions? This may pinpoint your problem.

Sub YourFunction()
    On Error Goto ErrHndlr



    Exit Sub
ErrHndlr:
    MsgBox "Error No: " & Err.Number & vbCrLf & _
           "Desc: " & Err.Description
    Err.Clear
End Sub

 

[SHardy]

I had included error handling within the main code. I changed this t odisplay the error number & desc. Still no change.

I didn't have error handling within the custom functions. After adding to these, I was revieving the following error for each of the cells using the function:

Error No: 5
Error Desc: Invalid procedure call or argument

But it then went on to finish processing the code, and all the cells in question had calculated OK.

I have now just added:

On Error Resume Next

at the start of each function. Seems to work fine. I'm still not sure why it would've been causing this problem though? And it was only when copying the formula via code. Adding or copying the formula manually was OK.

Any ideas?

Thanks for your help.

 

[dsi]

If you Resume Next after an error, you may not catch that an error occurred. You can set the code to break on errors by "On Error Goto 0". This should allow the program to break if there is an error.

 

[SHardy]

But it's actually working fine like that. As soon as I put the handler into the function, it reported errors but actually handles the function as it should. It only seems to be the case when copying the formulas via code. Even with the error handler in place, I can do exactly the same manually and get no errors.