Existence checks collected in one module

[Warbe]

Existence checks are an unavoidable circumstance to prevent errors. Unfortunately they not only blow-up the code but are also ugly, specifically when On Error .... statements are used. Over time I've compiled all I needed and made them as robust as possible. It became a proper module, last but not least due to the integrated existence check for an open Workbook which considers any Workbook open in any application instance. An existence check for an open Workbook for instance will always look like:

Please Login or Register  to view this content.

whereby <wb> may be a Workbook object or a Workbook's name.
Some may ask now himself: Why may it makes sense to check the existence of an object? The answer is that it can happen that an object had been assigned which meanwhile no longer exists. May it be a Workbook meanwhile closed or a range pointing to a cell of which the row had been deleted meanwhile. Cases like this are a source for really ugly errors which easily can be avoided.

The following has to be copied into a new Standard module e.g. named mExists (the coding scheme may easily be extended for other existence checks):

Please Login or Register  to view this content.

[AliGW]

You have marked this as solved ...

[Warbe]

You have marked this as solved ...

Intentionally since this is not a question but something I presented in the Tips and Tutorials forum as a solution.

[JSTKwan]

Thanks for this Exists module. I have a question. Mainly, I would like to test if a workbook is opened and if a particular sheet exists within that workbook. I used your module to test it out and not quite got it to work as I think how it should, so i did a "work around" and I got it to work. Maybe I am not fully understand your module.... I hope you may point me in the right direction. The way I am doing this is that I test if the workbook is opened, if it is then I test for the worksheet. I thought it would be better if it can be done in one test. The reason for 2 passes, the Optional arguments are type of Workbook and Worksheet, therefore, if they don't exists in the first place, VBA throws an error, "so they must exist" in the first place. Hence, 2 passes check....

Please Login or Register  to view this content.

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

You need to start a thread in the VBA section, NOT the Tips & Tutorials section.

[Warbe]

Meanwhile I have reworked the whole thing and it now offers an existence check for a Worksheet as well. Using the various existence checks is now less complex since every check is an individual procedure. The module is now named mObject (can be copied from Workbook Object.xlsm ) and the existence check for a Worksheet would read:

Please Login or Register  to view this content.

• wb is anything which identifies the Workbook (name, fullname, or object).
• ws is anything which identifies the Worksheet (name or object)
  whereby, when an object is provided only its name is used. This allows to check whether a Worksheet in one Workbook exists in another one.

The module mObject uses my standard (full featured) error handling which may of course be replaced by any other.
When using the whole thing, open the Workbook and copy mObject, mErrHndlr, clsCallStack and clsCallStackItem

[Warbe]

Thanks for this Exists module. I have a question. Mainly, I would like to test if a workbook is opened and if a particular sheet exists within that workbook. I used your module to test it out and not quite got it to work as I think how it should, so i did a "work around" and I got it to work. Maybe I am not fully understand your module.... I hope you may point me in the right direction. The way I am doing this is that I test if the workbook is opened, if it is then I test for the worksheet. I thought it would be better if it can be done in one test. The reason for 2 passes, the Optional arguments are type of Workbook and Worksheet, therefore, if they don't exists in the first place, VBA throws an error, "so they must exist" in the first place. Hence, 2 passes check....

Please Login or Register  to view this content.

You cannot check an objects existence when you provide it. Worksheets(ws) is the object which you want to check. Please see my other reply for a working/better solution which will read:

Please Login or Register  to view this content.