Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On error...
Is there a way for Excel to skip reporting an error if a function doesn't work? More specifically I once saw the use of the command "on error resume next" which as it stands I have very little clue as to what it does. Regardless... Code: -------------------- Private Sub Refresh_Click() Dim i As Integer ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources For i = 10 To 39 addComment (Cells(i, "B")) Next i End Sub -------------------- This is my code. I receive an *"Method 'UpdateLink' of object '_Workbook' failed"* error when the source and the destination sheets are both open and the "refresh" button is clicked. This will very rarely happen when the users are actually using the files, but in the rare case that it does I'd rather they be able to still update links without receiving the error. Is there a way to either ignore the error, or a different way to update the links which would avoid the error completely? THanks in advanced. -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=517217 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On error...
On Error Resume Next has the effect of ignoring errors and continuing on to
the next command. It can be very useful, but it has to be used with caution, resetting as a minimum with On Error Goto 0, at some point, otherwise all errors will get ignored, even those you don't. Which error do you want to ignore, and why? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ph8" wrote in message ... Is there a way for Excel to skip reporting an error if a function doesn't work? More specifically I once saw the use of the command "on error resume next" which as it stands I have very little clue as to what it does. Regardless... Code: -------------------- Private Sub Refresh_Click() Dim i As Integer ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources For i = 10 To 39 addComment (Cells(i, "B")) Next i End Sub -------------------- This is my code. I receive an *"Method 'UpdateLink' of object '_Workbook' failed"* error when the source and the destination sheets are both open and the "refresh" button is clicked. This will very rarely happen when the users are actually using the files, but in the rare case that it does I'd rather they be able to still update links without receiving the error. Is there a way to either ignore the error, or a different way to update the links which would avoid the error completely? THanks in advanced. -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=517217 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On error...
I can understand how ignoring errors is probably not the best habit t form when coding. So I guess maybe if there was a way to avoid th error altogether that would be the best bet. I get the error when both the source and destinatino spreadsheets ar open on the same computer and I try to update the links with tha particular line of VBA code which updates the link ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources). The erro I get is "-Method 'UpdateLink' of object '_Workbook' failed-." That code is on the destination spreadsheet. WHere formula links exis in the cells. But when I have both this destination spreadsheet an source spreadsheet open (on the same computer). When the source fil is opened on a different computer (ala network) or when its closed th function performs flawlessly -- ph ----------------------------------------------------------------------- ph8's Profile: http://www.excelforum.com/member.php...fo&userid=1987 View this thread: http://www.excelforum.com/showthread.php?threadid=51721 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On error...
In that instance, try just wrapping it with error statements as I showed
On Error Resume Next ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources On Error Goto 0 This should mitigate against any further problems,. The other option is to test whether the file is open, if so, don't issue the update. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ph8" wrote in message ... I can understand how ignoring errors is probably not the best habit to form when coding. So I guess maybe if there was a way to avoid the error altogether that would be the best bet. I get the error when both the source and destinatino spreadsheets are open on the same computer and I try to update the links with that particular line of VBA code which updates the link ( ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources). The error I get is "-Method 'UpdateLink' of object '_Workbook' failed-." That code is on the destination spreadsheet. WHere formula links exist in the cells. But when I have both this destination spreadsheet and source spreadsheet open (on the same computer). When the source file is opened on a different computer (ala network) or when its closed the function performs flawlessly. -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=517217 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On error...
Thanks for your help, Bob! One last question. Just to confirm. That code will ONLY skip the error for that particular line of code? It won't disable error reporting for other errors I may get for various reasons? -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=517217 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
On error...
No, the On Error Goto 0 disables error handling within the code, so VBA
takes back control. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ph8" wrote in message ... Thanks for your help, Bob! One last question. Just to confirm. That code will ONLY skip the error for that particular line of code? It won't disable error reporting for other errors I may get for various reasons? -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=517217 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Form Err.Raise error not trapped by entry procedure error handler | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |