A Sub within a Sub?

[Les Isaacs]

Hi All
This may or may not be a long shot, but I'll ask anyway!
I have a complex workbook that is regularly sent to clients for them to enter data and return. There is a lot of simple validation in place, and there is also a 'Confirm' button at the top of each sheet with code behind it that runs a lot of more complex validation for certain columns (using values in other cells, etc.). The 'Confirm' button ultimately generates 1 of 3 possible outcomes:

1. The data is fine
2. There are warnings
3. There are errors

If 'There are warnings', a comment is added to the cell, a Warnings flag is set to True, a Warnings count goes up by 1, and the cell fillcolour is set to yellow. This is done by (for example):

Please Login or Register  to view this content.

There is a similar routine if 'There are errors'.
This all works perfectly well
The issue is that the above code is repeated for different columns many times over, for different warnings/errors, so it seems to me that instead of repeating the whole warnings/errors process I should be able to write a simple Sub that would take as parameters the cell address and the warning/error comment, and would then do the business. So the Sub would be something like

Please Login or Register  to view this content.

and for the above example the call to the Sub would be something like:

Please Login or Register  to view this content.

. . . which would need to have the same effect as the code in lines 1390 to 1450 above.

The new Sub FailData works as far as adding the comment and fillcolour to the relevant cell on the sheet, but the assignments to WarningsThisPass / intWarningsCount / ErrorsThisPass are not happening . I'm guessing that this is something to do with those variables not being available from the Sub FailData, but I cannot see how to overcome this.

I hope someone has read all this, and can help me out
Very many thanks in advance
Les

[GC Excel]

To make the variables available to all subs, you must make them "Global"
To do so, declare them OUTSIDE of your subs (on top of your module), like this :

Please Login or Register  to view this content.

They will be available to all your modules. To make them available only in your module, use Private instead of Public

More info here : http://www.cpearson.com/excel/scope.aspx

[Norie]

Les

Where have you declared WarningsThisPass / intWarningsCount / ErrorsThisPass?

If you declared them in the sub that's calling FailData you coud pass them as parameters and they could be updated in FailData.

[mikerickson]

It sounds like you are doing several things. (And posted the code for only one of them)

Is the Cell's status good, warning or error?
Set Cell comment to match status
Set warning/error_thisPass variables
Loop through cells for one pass

Each of those could be their own routine.
For this example a cell is warning if it = "W", error if it = "E"

Please Login or Register  to view this content.

[Les Isaacs]

Hi All
Very many thanks for your help.
Moving the variable declarations (e.g. intWarningsCount etc) to outside the Sub as Private did the trick - for now
I do have the same variable names used in the validation code for 5 other sheets, and did worry that by making them global they would interfere with each other, but making them Private may be the answer ... but ...
It would obviously be preferable to have the Sub FailData declared just once, so it can be used in the validation code for all 5 sheets. I therefore assume it should not be in the sheet code, but should instead be under Modules in the VBA project, and declared as Public Sub FailData .... but if I do that, presumably the variable declarations would then need to be public and might interfere with each other. Perhaps I should give them different names, according to the sheet name?
Norie: you suggested I could pass the variable into Sub FailData so that they could be updated there: but I can't quite see how to do that in a way so that the updated values would be passed back to the main sub. Perhaps the sub should be a function?
Hope I'm on the right lines here - I'm certainly at the edge of my expertise
Thanks again
Les