Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling Issues - Pls Help
Can anyone help me understand why this does not work if there are no
pivots on the current sheet. this code is supposed to either use the pivot the user selected or the 1st pivot on the active sheet and then update the source data. It works perfectly except when a work sheet has no pivots - in that case, it creates an error that I cannot catch. Why does the line On Error GoTo Error_Need_Pivot_Source in the NoPivotSelected: block not find this error? How can I correct it? Any help would be greatly appreciated. thanks Tim Sub Update_Pivot_Table_Sources() ' ' Macro recorded 5/31/2007 by TB ' ' Dim iSheets As Integer, x As Integer Dim iPivot As Integer, _ strCurrentSheet As String, _ strNewPivotTblSrc As String, _ strResponse As String Dim pt As PivotTable strResponse = MsgBox("Do you want to change all of the Pivot Table Sources?", vbOKCancel) If strResponse < vbOK Then MsgBox ("Cancelled") If strResponse = vbOK Then 'see if the user selected a pivot table. if so, assign it to pt and get the source On Error GoTo NoPivotSelected Set pt = ActiveCell.PivotTable CurPivotTblSrc = pt.SourceData GoTo Found_Pivot_Source 'if the user didn't select a pivot, see if there is one on the active sheet. 'If so, use that. If not, return an error and exit NoPivotSelected: On Error GoTo Error_Need_Pivot_Source CurPivotTblSrc = ActiveSheet.PivotTables(1).SourceData GoTo Found_Pivot_Source Found_Pivot_Source: strNewPivotTblSrc = InputBox("Please enter the new source for the pivot table below", "New Pivot Source", CurPivotTblSrc) If strNewPivotTblSrc = "" Then MsgBox ("Cancelled") GoTo Exit_Update_All_Pivots End If strResponse = MsgBox("Do you want to update all pivots (click Yes) or just pivot tables with this data source: " _ & CurPivotTableSrc & " (click no)", vbYesNo) On Error GoTo Error_Found Application.ScreenUpdating = False 'Count number of sheets in workbook iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_Update_All_Pivots For x = 1 To iSheets 'go to a worksheet to change pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'change all pivot tables on 'this worksheet one at a time For Each pt In ActiveSheet.PivotTables If strResponse = vbNo Then If pt.SourceData = CurPivotTblSrc Then pt.SourceData = strNewPivotTblSrc ActiveWorkbook.ShowPivotTableFieldList = False End If Else pt.SourceData = strNewPivotTblSrc ActiveWorkbook.ShowPivotTableFieldList = False End If Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that you were originally at Application.ActiveWorkbook.Sheets(strCurrentSheet) .Activate MsgBox ("Pivots updated successfully") End If GoTo Exit_Update_All_Pivots Error_Found: MsgBox ("Error Found. Macro ending. " & Err & ": " & Error(Err)) GoTo Exit_Update_All_Pivots Error_Need_Pivot_Source: MsgBox ("Cannot find pivot table. Please select a sheet with a pivot and re-run macro") GoTo Exit_Update_All_Pivots Exit_Update_All_Pivots: Application.CommandBars("PivotTable").Visible = False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling Issues - Pls Help
Tim,
You need a Resume statement, Replace this: NoPivotSelected: With this: NoPivotSelected: Resume RightHere RightHe HTH, Bernie MS Excel MVP "Tim879" wrote in message ... Can anyone help me understand why this does not work if there are no pivots on the current sheet. this code is supposed to either use the pivot the user selected or the 1st pivot on the active sheet and then update the source data. It works perfectly except when a work sheet has no pivots - in that case, it creates an error that I cannot catch. Why does the line On Error GoTo Error_Need_Pivot_Source in the NoPivotSelected: block not find this error? How can I correct it? Any help would be greatly appreciated. thanks Tim Sub Update_Pivot_Table_Sources() ' ' Macro recorded 5/31/2007 by TB ' ' Dim iSheets As Integer, x As Integer Dim iPivot As Integer, _ strCurrentSheet As String, _ strNewPivotTblSrc As String, _ strResponse As String Dim pt As PivotTable strResponse = MsgBox("Do you want to change all of the Pivot Table Sources?", vbOKCancel) If strResponse < vbOK Then MsgBox ("Cancelled") If strResponse = vbOK Then 'see if the user selected a pivot table. if so, assign it to pt and get the source On Error GoTo NoPivotSelected Set pt = ActiveCell.PivotTable CurPivotTblSrc = pt.SourceData GoTo Found_Pivot_Source 'if the user didn't select a pivot, see if there is one on the active sheet. 'If so, use that. If not, return an error and exit NoPivotSelected: On Error GoTo Error_Need_Pivot_Source CurPivotTblSrc = ActiveSheet.PivotTables(1).SourceData GoTo Found_Pivot_Source Found_Pivot_Source: strNewPivotTblSrc = InputBox("Please enter the new source for the pivot table below", "New Pivot Source", CurPivotTblSrc) If strNewPivotTblSrc = "" Then MsgBox ("Cancelled") GoTo Exit_Update_All_Pivots End If strResponse = MsgBox("Do you want to update all pivots (click Yes) or just pivot tables with this data source: " _ & CurPivotTableSrc & " (click no)", vbYesNo) On Error GoTo Error_Found Application.ScreenUpdating = False 'Count number of sheets in workbook iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_Update_All_Pivots For x = 1 To iSheets 'go to a worksheet to change pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'change all pivot tables on 'this worksheet one at a time For Each pt In ActiveSheet.PivotTables If strResponse = vbNo Then If pt.SourceData = CurPivotTblSrc Then pt.SourceData = strNewPivotTblSrc ActiveWorkbook.ShowPivotTableFieldList = False End If Else pt.SourceData = strNewPivotTblSrc ActiveWorkbook.ShowPivotTableFieldList = False End If Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that you were originally at Application.ActiveWorkbook.Sheets(strCurrentSheet) .Activate MsgBox ("Pivots updated successfully") End If GoTo Exit_Update_All_Pivots Error_Found: MsgBox ("Error Found. Macro ending. " & Err & ": " & Error(Err)) GoTo Exit_Update_All_Pivots Error_Need_Pivot_Source: MsgBox ("Cannot find pivot table. Please select a sheet with a pivot and re-run macro") GoTo Exit_Update_All_Pivots Exit_Update_All_Pivots: Application.CommandBars("PivotTable").Visible = False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling Issues - Pls Help
Hello,
I am trying to incorporate this macro logic into one of my spreadsheets. I am much more familiar with Access, than Excel so here goes... My question is when I run this, and get to the question on the source data the default option is data source sheet!C1:C18. If I change that, the macro does not run without error. If I leave the default value in the response box, the will run. The error I receive states PivotTable field name is not valid. Is this because the Pivot Table that I am trying to refresh is summed or something or am I just entering the range incorrectly. Also, I can't quite put my finger on how the default is selected. Specifically, in the CurPivotTblSrc = ActiveSheet.PivotTables(1).SourceData statement below. The way I read that is that the SourceData for the pivot table on the active sheet should select everything, so why is it only showing cloumns C1-C18? On Error GoTo Error_Need_Pivot_Source CurPivotTblSrc = ActiveSheet.PivotTables(1).SourceData GoTo Found_Pivot_Source Found_Pivot_Source: strNewPivotTblSrc = InputBox("Please enter the new source for the" _ & " pivot table below", "New Pivot Source", CurPivotTblSrc) Any help is appreciated. "Tim879" wrote: Can anyone help me understand why this does not work if there are no pivots on the current sheet. this code is supposed to either use the pivot the user selected or the 1st pivot on the active sheet and then update the source data. It works perfectly except when a work sheet has no pivots - in that case, it creates an error that I cannot catch. Why does the line On Error GoTo Error_Need_Pivot_Source in the NoPivotSelected: block not find this error? How can I correct it? Any help would be greatly appreciated. thanks Tim Sub Update_Pivot_Table_Sources() ' ' Macro recorded 5/31/2007 by TB ' ' Dim iSheets As Integer, x As Integer Dim iPivot As Integer, _ strCurrentSheet As String, _ strNewPivotTblSrc As String, _ strResponse As String Dim pt As PivotTable strResponse = MsgBox("Do you want to change all of the Pivot Table Sources?", vbOKCancel) If strResponse < vbOK Then MsgBox ("Cancelled") If strResponse = vbOK Then 'see if the user selected a pivot table. if so, assign it to pt and get the source On Error GoTo NoPivotSelected Set pt = ActiveCell.PivotTable CurPivotTblSrc = pt.SourceData GoTo Found_Pivot_Source 'if the user didn't select a pivot, see if there is one on the active sheet. 'If so, use that. If not, return an error and exit NoPivotSelected: On Error GoTo Error_Need_Pivot_Source CurPivotTblSrc = ActiveSheet.PivotTables(1).SourceData GoTo Found_Pivot_Source Found_Pivot_Source: strNewPivotTblSrc = InputBox("Please enter the new source for the pivot table below", "New Pivot Source", CurPivotTblSrc) If strNewPivotTblSrc = "" Then MsgBox ("Cancelled") GoTo Exit_Update_All_Pivots End If strResponse = MsgBox("Do you want to update all pivots (click Yes) or just pivot tables with this data source: " _ & CurPivotTableSrc & " (click no)", vbYesNo) On Error GoTo Error_Found Application.ScreenUpdating = False 'Count number of sheets in workbook iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_Update_All_Pivots For x = 1 To iSheets 'go to a worksheet to change pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'change all pivot tables on 'this worksheet one at a time For Each pt In ActiveSheet.PivotTables If strResponse = vbNo Then If pt.SourceData = CurPivotTblSrc Then pt.SourceData = strNewPivotTblSrc ActiveWorkbook.ShowPivotTableFieldList = False End If Else pt.SourceData = strNewPivotTblSrc ActiveWorkbook.ShowPivotTableFieldList = False End If Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that you were originally at Application.ActiveWorkbook.Sheets(strCurrentSheet) .Activate MsgBox ("Pivots updated successfully") End If GoTo Exit_Update_All_Pivots Error_Found: MsgBox ("Error Found. Macro ending. " & Err & ": " & Error(Err)) GoTo Exit_Update_All_Pivots Error_Need_Pivot_Source: MsgBox ("Cannot find pivot table. Please select a sheet with a pivot and re-run macro") GoTo Exit_Update_All_Pivots Exit_Update_All_Pivots: Application.CommandBars("PivotTable").Visible = False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling Issues - Pls Help
If you are trying to incorporate the code listed in the original post with
the solution to that problem provided by Bernie, you need to understand two basic things about GoTo statements. The first form of a GoTo is simply: GoTo TheLabel: '' ' code '' TheLabel: "" ' more code '' ErrHandler: '' ' error handler block '' End Sub This could be called a simple "code jump" GoTo. This simple type of GoTo merely transfers code execution over one block of code so execution continues at a new line. This sort of GoTo is frowned upon by professional developers. While there is nothing wrong with GoTo per se, its use does often lead to "spaghetti code", code with lots up jumps with GoTos. This makes the code difficult to understand and especially difficult to maintain. As a general rule, if you are using GoTo to skip over a block of code, that block of code should be in a Sub or Function procedure to be called only some condition is true. For example, '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' ' Bad GoTo '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' '' ' some code '' If X<Y Then GoTo TheLabel: End If '' ' conditionally called code, runs only if X=Y '' TheLabel: '' ' some code '' This is better written as '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' ' Better Structured Code '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' '''' ' Good Structured code ''' ''' ' some code '' If X=Y Then Call SomeProc ' called only if X=Y End If '' ' more code '' '' ' some code '' This removes the GoTo statement and calls SomeProc (which replaces the Conditionally Called Code of the first example). Not only is this easier to understand and maintain, it also promotes the very good practice of code re-use. It is quite possible that you can use the SomeProc procedure in several different areas of the code, meaning you write and test it once and you don't have to re-invent the wheel each time such code is needed. Using the GoTo statement in an On Error Goto XXX statement is quite different than a simple jump Goto. In this case, a run time error occurs and execution results at the XXX label. However, VBA is still running in "error handling mode". In this mode, no subsequent errors are handled by the On Error statement. You can't trap error when VBA is in "error handling mode". Thus, code like '' ' Some Code '' On Error Resume Label1: '' ' more code '' Exit Sub Label1: Debug.Print CStr(Err.Number), Err.Description ' for illustration, for another error Debug.Print 1/0 End Sub In this code, an error in "more code" will transfer execution to the Label1: marker. However, VBA is running in "error handler mode" and will not trap any subsequent errors. In the example code, we use 1/0 to force a #DIV/0 error. Here, the On Error Resume statement will NOT have any effect. In order to re-establish error handling, your code must use a Resume statement to pass execution to some other point. The Resume statement (like the End Sub or End Function or End Property statements) turns off the "error handling mode" and resets it to "standard mode". Error handling mode is also turned off and restored to normal mode when the procedure containing the "On Error Goto XXX" terminates, either naturally or prematurely with an Exit Sub, Exit Function, or Exit Property statement. Error handling can be tricky especially when you have multiple procedures that call one another and some of those procedures had error handling code and others do not. See http://www.cpearson.com/Excel/ErrorHandling.htm for a fairly comprehensive discussion of error handling. I think everything will be improved once VB/NET is fully integrated with Office so instead of On Error statements you can use Try/Catch/Finally blocks to handler errors. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Roger Converse" wrote in message ... Hello, I am trying to incorporate this macro logic into one of my spreadsheets. I am much more familiar with Access, than Excel so here goes... My question is when I run this, and get to the question on the source data the default option is data source sheet!C1:C18. If I change that, the macro does not run without error. If I leave the default value in the response box, the will run. The error I receive states PivotTable field name is not valid. Is this because the Pivot Table that I am trying to refresh is summed or something or am I just entering the range incorrectly. Also, I can't quite put my finger on how the default is selected. Specifically, in the CurPivotTblSrc = ActiveSheet.PivotTables(1).SourceData statement below. The way I read that is that the SourceData for the pivot table on the active sheet should select everything, so why is it only showing cloumns C1-C18? On Error GoTo Error_Need_Pivot_Source CurPivotTblSrc = ActiveSheet.PivotTables(1).SourceData GoTo Found_Pivot_Source Found_Pivot_Source: strNewPivotTblSrc = InputBox("Please enter the new source for the" _ & " pivot table below", "New Pivot Source", CurPivotTblSrc) Any help is appreciated. "Tim879" wrote: Can anyone help me understand why this does not work if there are no pivots on the current sheet. this code is supposed to either use the pivot the user selected or the 1st pivot on the active sheet and then update the source data. It works perfectly except when a work sheet has no pivots - in that case, it creates an error that I cannot catch. Why does the line On Error GoTo Error_Need_Pivot_Source in the NoPivotSelected: block not find this error? How can I correct it? Any help would be greatly appreciated. thanks Tim Sub Update_Pivot_Table_Sources() ' ' Macro recorded 5/31/2007 by TB ' ' Dim iSheets As Integer, x As Integer Dim iPivot As Integer, _ strCurrentSheet As String, _ strNewPivotTblSrc As String, _ strResponse As String Dim pt As PivotTable strResponse = MsgBox("Do you want to change all of the Pivot Table Sources?", vbOKCancel) If strResponse < vbOK Then MsgBox ("Cancelled") If strResponse = vbOK Then 'see if the user selected a pivot table. if so, assign it to pt and get the source On Error GoTo NoPivotSelected Set pt = ActiveCell.PivotTable CurPivotTblSrc = pt.SourceData GoTo Found_Pivot_Source 'if the user didn't select a pivot, see if there is one on the active sheet. 'If so, use that. If not, return an error and exit NoPivotSelected: On Error GoTo Error_Need_Pivot_Source CurPivotTblSrc = ActiveSheet.PivotTables(1).SourceData GoTo Found_Pivot_Source Found_Pivot_Source: strNewPivotTblSrc = InputBox("Please enter the new source for the pivot table below", "New Pivot Source", CurPivotTblSrc) If strNewPivotTblSrc = "" Then MsgBox ("Cancelled") GoTo Exit_Update_All_Pivots End If strResponse = MsgBox("Do you want to update all pivots (click Yes) or just pivot tables with this data source: " _ & CurPivotTableSrc & " (click no)", vbYesNo) On Error GoTo Error_Found Application.ScreenUpdating = False 'Count number of sheets in workbook iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.Name If Windows.Count = 0 Then _ GoTo Exit_Update_All_Pivots For x = 1 To iSheets 'go to a worksheet to change pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'change all pivot tables on 'this worksheet one at a time For Each pt In ActiveSheet.PivotTables If strResponse = vbNo Then If pt.SourceData = CurPivotTblSrc Then pt.SourceData = strNewPivotTblSrc ActiveWorkbook.ShowPivotTableFieldList = False End If Else pt.SourceData = strNewPivotTblSrc ActiveWorkbook.ShowPivotTableFieldList = False End If Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that you were originally at Application.ActiveWorkbook.Sheets(strCurrentSheet) .Activate MsgBox ("Pivots updated successfully") End If GoTo Exit_Update_All_Pivots Error_Found: MsgBox ("Error Found. Macro ending. " & Err & ": " & Error(Err)) GoTo Exit_Update_All_Pivots Error_Need_Pivot_Source: MsgBox ("Cannot find pivot table. Please select a sheet with a pivot and re-run macro") GoTo Exit_Update_All_Pivots Exit_Update_All_Pivots: Application.CommandBars("PivotTable").Visible = False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling | Excel Programming | |||
VBA: Error handling | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error Handling issues | Excel Programming | |||
Error handling with a handling routine | Excel Programming |