Error Checker For Excel Workbooks?

**leaning** · 02-07-2011, 09:45 PM

Hello:

Allen Browne has an error checker for Access databases:
http://allenbrowne.com/appissuechecker.html

Has someone made something like this for Excel?

(DonkeyOte made the following which is a start of a checker. It seems like if a few more little checks were added, then it would be the bomb.)

Please Login or Register  to view this content.

a) I want to add something like this, but I don't know how to make it applicable to the whole sheet, not just a range. (This would check for cells which have spaces in them, and nothing else.)

Please Login or Register  to view this content.

b)Add in a routine for "The following cells have data validation that evaluate to an error. Please review the data validation of those cells, and correct them."

c) With the whole packaged up with a bunch of little subroutines that run through the workbooks and look for mistakes, that would alleviate a bunch of headaches, I think. Then your answer for alot of these forum questions would be "Have you run the checker on your workbook, because it will find that for you."

Any ideas on how to incorporate a) and b) into DonkeyOte's code and any other things that a workbook can be checked for?

Thanks!

VR/Lost

**nimrod** · 02-08-2011, 12:06 AM

Hello leaning:

If a macro is going to find a bunch of cells with spaces in them why not just have the macro correct the problem instead of asking the user to correct it . We get downloads with 1000's of these types of issues on a regular basis. Our end users would go nuts if the macro asked them 1000 times to remove the blank space.

Soooo... here's code that goes thru the current sheet correcting the issue and then reports how many cells it corrected. However ... it can also serve as an example for what you wanted to do... cheers

Please Login or Register  to view this content.

**leaning** · 02-08-2011, 10:43 PM

Nimrod,

I tried your CleanEmptyStringsFromActiveSheet code.

I use alot of concatenates in my workbooks. Most of the time, I get the "Formula too long" error. So I have to split the info so you don't get that error.

For example, in cell A1 is "The quick brown fox" and in B1 is "(space)jumps over the lazy dog.(space)" (Second space sets up the next sentence if there is one.)
In C1 is =A1&B1.
So you get "The quick brown fox(space)jumps over the lazy dog.(space)"

When I run your macro, I get "The quick brown foxjumps over the lazy dog." (which isn't good.). Your macro deletes spaces that are necessary in this case.

My little macro works, but like you said, it's a PITA for the user to acknowledge each trimming action.

So, do you know how to do:
"For each cell in ws, if there is nothing in the cell but spaces, clear that cell out." The macro runs through the worksheet and fixes everything without all the user intervention?

?

VR/Lost

**nimrod** · 02-08-2011, 11:15 PM

Hi Leaning :
This Sol'n prompts you for what "type" of space clearing you'd like to do... does this work better ?

Please Login or Register  to view this content.

**leaning** · 02-08-2011, 11:51 PM

Nimrod,

I made up a Excel Error Checker of sorts, but it is more like a "Toolbar of Macros Which are for Error Checking as Well as Assisting During Workbook Design".

I used this site for the toolbar: http://www.contextures.com/xltoolbar02.htm

I attached the xls file to this post, but I save it as an xla after all the editing is done.

a) It's a start, but do to the number of macros, the toolbar is too long, so I have to resize it so it fits down the screen rather than across.

b) I tried to work with your macro so that it scan all the sheets rather than just the active one. So I added the For each ws in Worksheets and Next ws, but I don't think it is properly recursing all the sheets.

c) Do you know of any other good macros that look for problem areas of worksheets?

VR/Lost

**nimrod** · 02-09-2011, 12:13 AM

Hi Leaning:
It's not quite that simple.
FIRST you didn't Connect your "For Each WS" with the "For Each oCell" sooo the oCell was always pointing at the same active sheet. (see attached demo)
SECOND , if you're going to look at alot of sheets you need to handle the error that will occurr if a sheet is totally empty.(see attached demo)
THIRD: If you going to create a tool you will need to control what book the macros are actually working on i.e. if the code is work on the same book that the VB code is in that refer to "ThisWorkBook".. while if your working on the active book the refer to "ActiveWorkBook".

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**nimrod** · 02-09-2011, 12:39 AM

To Continue with above disussion ... take the following code , open 3 new books , and put a couple of cells of data on a few sheets of each book ... and then run this code. In this code you can see how WB is linked to WS and How WS is linked to oCells.

Please Login or Register  to view this content.

**leaning** · 06-09-2011, 10:32 AM

All,

This Error Checker thread is continued here:

http://www.excelforum.com/the-water-...lways-use.html

Thanks, all!

Respectfully,

Lost

Error Checker For Excel Workbooks?

LinkBack

Thread Tools

Rate This Thread

Display

Error Checker For Excel Workbooks?

Re: Error Checker For Excel Workbooks?

Re: Error Checker For Excel Workbooks?

Re: Error Checker For Excel Workbooks?

Re: Error Checker For Excel Workbooks?

Re: Error Checker For Excel Workbooks?

Re: Error Checker For Excel Workbooks?

Re: Error Checker For Excel Workbooks?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions