• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dealing with spreadsheet error in VBA

wchaug

New Member
Hello folks,

I've been a long time fan of this website and have been able to solve a lot of Excel-related issues by browsing forums and reading articles here, to the point I'm now considered the magical excel wizard here in the office. If this was a radio station, I'd say "long time listener, first time caller."

I had a question regarding VBA, but it requires a bit of back story. I do quality checks on chemical compositions by plotting them on a line chart with a logaritmic y-axis. I use log scale because Chemical X could be 95% of the substance and you wouldn't see the other 15 chemicals, and the chemicals tend to form a pattern when plotted on log scale.

However, sometimes a chemical doesn't appear in a substance so it's 0%. If you try to put 0 on a log chart in Excel you get an error message: "Negative and zero values cannot be plotted on a log chart in Excel", or something similar. I know this because it's mathematically impossible, so I just add a formula to the pre-formatted template, and plot that instead, to make the error go away:

=IF(A1>0,A1,NA())

And bam, no more error. Which is all fine and dandy, except I've been tasked with creating a database of all the substances that have been examined at our laboratory in the last 5 years and there's thousands of the files, most of which have been created with the chart without the error-handling formula. I've already completed a VBA macro that goes through a list of folders and opens up all the excel files with pre-formatted templates in it and gets the info that it needs, but whenever this macro opens up a spreadsheet with one of these bad charts on it, the log chart error pops up again. So I have to babysit the macro all day and click on it whenever it shows up. Is there any way, in VBA, to handle this chart error when it pops up?
 
Hi wchaug!
First, thanks for the detailed message and info. A couple of quick questions about what's causing your current problem. It sounds like you've already got the macro setup, and it's opening up a bunch of files and pulling data from them. Is there error that when the files open up and have bad data, it causes XL to freeze (like a circular ref error would), or is it just that bad data is getting imported?

If the former, we might be able to disable the error with
Application.DisplayAlerts = False

If the latter, it sounds like we need some way to go back through all the data and find the 0 values. Before proceeding down this route, can you confirm that this is the problem, or is it something else?
 
Luke M,

Application.DisplayAlerts = False fixed the problem.
I knew it was something simple, I just couldn't find any documentation on how to disable the alert. Probably because I was looking up error handling instead of alerts.

Thanks for your time, greatly appreciated.
-wch
 
No worries, wch.
Just to be sure, and for other future readers, make sure you include a line to
Code:
Application.DisplayAlerts = True
in the code after you get past the section where you expect errors to occur. This is good for 2 reasons, 1) so you are notified of any other errors that might pop up, and 2) always good practice to turn Application settings (ie ScreenUpdating, Calculation, DisplayAlerts, EnableEvents) back on when we are done.
 
Back
Top