On Error Does Not Work
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Excel VBA: On Error Goto statement not working inside For-Loop up vote 7 down vote favorite 1 I'm trying to cycle through a table in excel. The first three columns of this table have text headings, the rest of them have dates as headings. I want to assign those dates, sequentially, to a Date-type variable, and then perform some operations based on the date To do this I am using a foreach loop on myTable.ListColumns. Since the first three columns do not have date headers, I have tried to set the loop up so that, if there is an error assigning the header string to the date-type variable, the loop goes straight to the next column This seems to work for the first column. However, when the second column's header is 'assigned' to the date-type variable, the macro encounters an error even though it is within an error-handling block Dim myCol As ListColumn For Each myCol In myTable.ListColumns On Error GoTo NextCol Dim myDate As Date myDate = CDate(myCol.Name) On Error GoTo 0 'MORE CODE HERE NextCol: On Error GoTo 0 Next myCol To reiterate, the error is thrown on the second round of the loop, at the statement myDate = CDate(myCol.Name) Can anyone explain why the On Error statement stops working? excel vba excel-vba for-loop error-handling share|improve this question asked Aug 17 '12 at 1:52 Swiftslide 41751828 Rather than using an error as your control structure, maybe an IF with an IsDate function would be more suitable in this scenario? –ray Aug 17 '12 at 2:29 1 If you are "blindly" handling the error - rather than taking specific action on an error type - then you should just use an On Error Resume Next outside your loop. At the moment you are using error handling afresh on each column. –brettdj Aug 17 '12 at 2:40 1 @br
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up VBA Error Handling not working in Excel up vote 5 down vote favorite 1 I have not had much http://stackoverflow.com/questions/11998836/excel-vba-on-error-goto-statement-not-working-inside-for-loop experience with VBA, but I sometimes use it at work. Recently, I have encountered a problem that shouldn't happen, and that neither my boss nor myself can figure out. Basically, the issue is that the Application property DisplayAlerts is set to True by default and can't be changed for some reason. Possibly related is that when I hit an error, it always display the End|Debug|Help alert and never hits the applied error handling. http://stackoverflow.com/questions/3440735/vba-error-handling-not-working-in-excel I am running 64-bit Office 2010 on a 64-bit Windows 7 machine. However, I do not believe it to be a platform issue, as I have tested on multiple different platforms, operating systems and software permutations and no other machine has this error; just mine. I have created some sample code in case anyone has encountered this before or has any ideas. The only thing I can think of, is that I have something installed on my machine that is causing this. But after a program purge and many restarts, I am no closer to deciphering what it might be. Public Sub TestErrorHandler() ' Suppress alerts Application.DisplayAlerts = False Dim strArray(1) As String strArray(0) = "Hello" strArray(1) = "World" ' Set up error handler On Error GoTo ErrHandler For i = 0 To 3 MsgBox strArray(i) Next ' Strip the error handler On Error GoTo 0 ' Unsuppress alerts Application.DisplayAlerts = True Exit Sub ErrHandler: MsgBox "Error: " & Err.Description Resume Next End Sub The error is thrown on the third enumeration of the for-loop (as it should). The type of the error is irrelevant, what is relevant is that I get the error and never hit the error handler. Any suggestions or help on this would be greatly appreciated. Many thanks! excel vba share|improve this questio
Technology and Trends Enterprise Architecture and EAI ERP Hardware IT Management and Strategy Java Knowledge Management Linux Networking Oracle PeopleSoft Project and Portfolio Management SAP SCM Security Siebel Storage UNIX Visual Basic http://visualbasic.ittoolbox.com/groups/technical-functional/vb-vba-l/on-error-goto-does-not-work-1560516 Web Design and Development Windows < Back CHOOSE A DISCUSSION GROUP Research Directory TOPICS Database Hardware Networking SAP Security Web Design MEMBERS Paul_Pedant DACREE MarkDeVries VoIP_News Inside-ERP MacProTX Inside-CRM I_am_the_dragon maxwellarnold Michael Meyers-Jouan TerryCurran Chris_Day Andrew.S.Baker Ramnath.Awate JoeTorre Craig Borysowich Locutus Dennis Stevenson DukeGanote Richard iudithm mircea_luca Clinton Jones bracke Nikki Klein AbhaiTripathi Iqbalyk Adrian_Grigoriu bluesguyAZ59 blrvenkat COMPANIES EdgeWave Sophos Pivotal CRM Wave Direct View All Topics View All on error Members View All Companies Toolbox for IT Topics Visual Basic Groups Ask a New Question Visual Basic for Applications A forum where peers share technical expertise, solve problems, and discuss issues related to the use of Visual Basic for Applications (VBA). Home | Invite Peers | More Visual Basic Groups Your account is ready. You're now being signed in. Solve problems - It's Free Create your account in on error does seconds E-mail address is taken If this is your account,sign in here Email address Username Between 5 and 30 characters. No spaces please The Profile Name is already in use Password Notify me of new activity in this group: Real Time Daily Never Keep me informed of the latest: White Papers Newsletter Jobs By clicking "Join Now", you agree to Toolbox for Technology terms of use, and have read and understand our privacy policy. On Error GoTo does not work Oops1 asked Aug 7, 2007 | Replies (11) Hi everyone! I have "On Error GoTo" right after variables declaration in my code. My macro just ignores it. It executes, but does not go to an error handler when an error occures. Isn't it frustrating? Any suggestions would be highly appreciated! Join this group Popular White Paper On This Topic Business Intelligence with SharePoint 2010 11Replies Best Answer 0 Mark this reply as the best answer?(Choose carefully, this can't be changed) Yes | No Saving... Cornish_Mike replied Aug 7, 2007 Hi Are you sure you have correctly used your Line Label and I notice you are using GoTo Name Plus a Colon e.g. On Error Go To ErrorTrapper ErrorTrapper: Best regards Mike Top Best A