Link to home
Create AccountLog in
Microsoft Access

Microsoft Access

--

Questions

--

Followers

Top Experts

Avatar of developingprogrammer
developingprogrammer

how to suppress error message excel vba
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.
User generated image
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!!

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Avatar of PratimaPratima🇮🇳

Avatar of mbizupmbizup🇰🇿

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
Reward 1Reward 2Reward 3Reward 4Reward 5Reward 6

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Avatar of mbizupmbizup🇰🇿

Sorry you're dealing with named ranges.

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

ActiveWorkbook.names.count
ASKER CERTIFIED SOLUTION
Avatar of mbizupmbizup🇰🇿

Link to home
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Create Account
SOLUTION
Avatar of Jeffrey CoachmanJeffrey Coachman🇺🇸

Link to home
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Create Account
Avatar of developingprogrammerdevelopingprogrammer

ASKER

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! = )
Avatar of Jeffrey CoachmanJeffrey Coachman🇺🇸

<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
Free T-shirt

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Avatar of developingprogrammerdevelopingprogrammer

ASKER

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!! = ))
Avatar of Jeffrey CoachmanJeffrey Coachman🇺🇸

Good Luck in the New Year!

;-)

Jeff
Avatar of developingprogrammerdevelopingprogrammer

ASKER

thanks Jeff! = )
Reward 1Reward 2Reward 3Reward 4Reward 5Reward 6

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Microsoft Access

Microsoft Access

--

Questions

--

Followers

Top Experts

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.