Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to suppress error message excel vba

Posted on 2012-12-20
11
Medium Priority
?
732 Views
Last Modified: 2012-12-27
hey guys, how do i suppress this error message? i already tried application.displayalerts = false and added on error resume next, but when i run the code (F5), once it hits the errors it exits.
error pop up
i'm trying to delete all the named ranges in excel

Sub test()
Dim x As Integer
x = 1
Application.DisplayAlerts = False
On Error Resume Next
For x = 1 To 100
    ActiveWorkbook.Names(x).Delete
Next
Application.DisplayAlerts = True
End Sub

i tried do until err<>0 but the same problem still happens - once it hits an invalid reference it stops. thanks guys!!
0
Comment
Question by:developingprogrammer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38712282
It's better to write code that avoids errors by design as much as possible.   Is the error occurring after all worksheets are deleted?

Instead of using 100 as an upper limit, use the number of worksheets.

Application.worksheets.count
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 61

Expert Comment

by:mbizup
ID: 38712285
Sorry you're dealing with named ranges.

I'm not an Excel expert, but does this get the total count?

ActiveWorkbook.names.count
0
 
LVL 61

Accepted Solution

by:
mbizup earned 1800 total points
ID: 38712449
Just got a chance to test this.  This seems to work without error:

    Dim nr As Name
    
    For Each nr In ActiveWorkbook.Names
        Debug.Print nr.Name
        nr.Delete
    Next

Open in new window


Anyhow, the idea is to write code that avoids errors by design, and to add error handling to trap unexpected errors.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 38713366
<No Points wanted>

Basic/Generic error handler:

Sub YourSubName()
On Error GoTo Err_YourSubName

********** Your Code Goes Here **********

Exit_YourSubName:
    Exit Sub

Err_YourSubName:
    MsgBox "There was an error executing the command." _
    & vbCrLf & "Error " & Err.Number & ": " _
    & vbCrLf & Error, vbExclamation
    Resume Exit_YourSubName
End Sub
0
 

Author Comment

by:developingprogrammer
ID: 38723155
whao guys, no points wanted and researching an unfamiliar topic just to help me, i'm really touched and appreciative of how yall take the time to help me out in the problems i face. maybe i'm a bit naive but i definitely couldn't imagine someone going out of the way to help their fellows or someone they don't know - i'm really appreciative guys = )

pratima thanks for looking into this but i think docmd is for Access not Excel, i tried it in Excel and it couldn't work - not found in the object library. but thanks! = )
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38724389
<whao guys, no points wanted and researching an unfamiliar topic just to help me, i'm really touched and appreciative of how yall take the time to help me out in the problems i face.>

...In my case, I only posted a very basic example of a generic error handler.
In other words, ...any expert here could have posted the same thing...
(No research needed)

My post did nothing to actually "answer" your question, it was just extra info.

In the end, and to be honest, ...I have done well this year "point-wise", so I saw no reason to appear greedy for a simple post like that.

So AFAICT, you can still request that my post be removed from the points split.

..."There will always be another Question"

;-)

Jeff
0
 

Author Comment

by:developingprogrammer
ID: 38724669
ha thanks for being generous jeff, and it's not so much that you didn't answer the question but rather you took time to read through my question and add that info there so i can learn. so it's really much more of your time spent contributing that i want to show my appreciation for = ) thank you!! = ))
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38725009
Good Luck in the New Year!

;-)

Jeff
0
 

Author Comment

by:developingprogrammer
ID: 38725916
thanks Jeff! = )
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question