How to improve this macro

I have a membership database spreadsheet with about 1000 members, and have developed various macros to extract data for various purposes (different mailing lists, email address files etc). A subroutine in all these checks the databases for empty cells in columns relevant to the macro, and reports an error message if it finds an empty cell (with the location of that cell). The macro basically just steps through every cell in the programmed columns and checks if empty.

Currently this subroutine is tailored for each macro, so that it only checks the relevant columns, as sometimes an empty cell can be valid.

I'd welcome suggestions on two points: is there a more efficient way of testing for empty cells, while at the same time being able to report the location if found. Secondly I'd like to modify the macro so that I could define a list of the columns to check at the beginning of the macro (maybe a comma separated string variable?), so that I can reuse the main code in each macro, rather than have umpteen versions of it.

I've included the current code here, but also attached the file, together with a sample membership database (dummy data) at the end.

Thanks for any ideas.

Please Login or Register  to view this content.

Check Membership.xlsMembership (Test Data B).xls

To answer you questions:

1. There is only one workbook, with one sheet) containing the membership details, and the macro above has already checked that it exists and has opened it. There are multiple workbooks containing only a "setup" macro, and each of these does different "functions" in terms of extracting and processing the membership data into a "data extract" workbook for the various uses (mailing lists, email address files etc). That "setup" workbook currently only has some instructions and a run macro button in sheet 1, and there are no other sheets.

2. The files (membership.xls, setup.xls and data extract.xls) for each "function" are all in the same "working folder" for that "function" (e.g. there are separate folders for "make mailing list for newsletters", make membership cards", "make group xx email list" etc.) A copy of the master membership.xls is manually copied into the "working folder" before the macro is run.

3. If a blank cell is found the setup macro displays an error message showing the location of the blank cell, then closes the membership.xls workbook before terminating. The user will then have to manually open the membership.xls workbook, correct the problem, save it, then start over. I do it that way because if there is a blank cell the user will probably have to contact the membership secretary to obtain the missing data (or probably a new version of the membership.xls workbook), which may take a day or so.

4. The macro in my original post is just one subroutine part of an overall "function" specific specific macro. My thought was to just have the configuration information at the start of this function specific macro, which is then used by a reusable "check membership" sub routine, rather than have a different version of this subroutine for each overall "function". This way the parameters are in the main macro itself, rather than tucked away in a, probably hidden, worksheet. I think that this would make it easier to maintain if and when someone else has to take this lot over.

I hope this is a bit clearer, if not please ask.

ooops, posted reply twice, sorry!

Sorry, I'm not explaining myself very well.

While there are many workbooks and macros that I'm working on and using, as far as this question is concerned there are only two workbooks involved (the ones attached to my original post):

Check Membership.xls - this workbook contains only one sheet (with a macro start button and instructions), and the setup macro (the code I listed in my original post).
Membership (Test Data B).xls - this is a test version of the membership database, with only 100 members, and anonymous data. This is the data that the setup macro is checking for spaces.

The objective is to enable the choice of the columns to be searched for blanks to be parameter driven, those parameters could be in a sheet (hidden) in the code workbook, or set up as a variable(s) at the beginning of the code itself - I'd prefer the latter, it's less complex for ongoing support purposes.

I don't think that you need to know this but:
Eventually, this "blanks check" macro will be used as a subroutine in what I've referred to as the "function macros" in my previous post, which need to do the "blanks check" before they start producing all the other different data files. To try and simplify my question I had just extracted the blanks check bit of code into it's own macro.

Thanks Nate,

I'll set this up and run some tests to see how it works and if it does what I'm looking for. The code that searches for blanks certainly looks a lot slicker than what I had. I'll post back with comments.

OK, I've made a few tweaks to Nates suggested code and have now got it doing what I want. The changes I've made are:

1. Changing the variables from Long to Integer (32,767 is more than adequate).
2. Moving the assignment of the ColList() array to the main "control" sub, so that I can choose the columns I want checked differently for each project by setting the ColList value to 0.
3. The Membership Database has two summary rows at the bottom, so I delete these before the finding the end of the data area to check for blank cells.
4. If a blank cell is found, display an error message containing the cell reference, and close the Membership Database without saving it (the user will normally have to obtain a corrected copy of the database from our membership secretary, not do any changes themselves).

I think that the way I have it now I can use the Sub CheckMemsData and Sub ErrMessage routines in a "common code" module in other projects, each with a just a project specific Main sub. I'll add more reusable routines to the the "common code" module as I develop more projects, and can just copy that module in each time (just have to remember to add the declarations for any new variables as I do this).

QUESTION - do the variable declarations all have to be in the module where the first run sub is (ie Main) or can I put the ones relevant to the common code routines at the top of the "common code" module? Will the compiler find them wherever they are?

If anyone's interested I've included the revised code below.

Thanks again to Nate for his help.

Please Login or Register  to view this content.