assistance with error handling please

[jmac1947]

Hello,

In my workbook I am processing entries with a VBA macro and as part of the processing flow I need to get some information from a nominated page in another, not open, workbook.

With some assistance from this forum I am able to achieve my goal WHEN the target worksheet actually exists in the second workbook.

Now, if I am running the macro and I see the error I know how to manually resolve the issue but, as is usually the case, the end user doesn't have a clue and I have very little clue about error handling in VBA (case of the blind leading the blind it seems )

I would appreciate some advice on how to trap and deal with the situation that occurs when "property_id" in the code snippet does not exist in the nominated workbook.

Please Login or Register  to view this content.

Thanks in advance

Jmac

[TKCZBW]

First off, identify the error number that occurs currently when that worksheet is not found (let's assume 1234). Put the following code in:

Please Login or Register  to view this content.

This assumes that you open a workbook identified as 'WorkBook_Name', i.e. Workbooks.Open(Workbook_Name). Make sure to place the 'ManualInput' line of code after you initially state what you want it to be (the initial input) and before it is actually opened, e.g.:

Please Login or Register  to view this content.

Let me know if it does the trick!

[adyteo]

add this at the beginning of your sub / function:

Please Login or Register  to view this content.

then at the bottom of your sub / function:

Please Login or Register  to view this content.

You need to change the error numbers or you can assign error number as this:

Please Login or Register  to view this content.

where xxx is a number greater than 512 (0 to 512 are reserved by Microsoft). you can use up to 65535.

I just saw TKCZBW's suggestion.

[jmac1947]

Thanks Guys, I appreciate the helpful suggestions.

I tried to include @TKCZBW suggestion and I nearly have it working but the user experience is not what I am looking for.

Here is the complete code for the macro

Please Login or Register  to view this content.

When I actually execute the code I get to the .Formula line and then I see the popup message shown in the attached .jpg file.

missing worksheet.jpg

If I then select "Cancel" the errorhandler code kicks in and I get the message I am expecting (having discovered the error is 1004).

I would really like to not see the popup and just use the msgbox in the errorhandler to coommunicate to the end user, unfortunately my best guess of application.displayalerts didnt do the trick.

Jmac

[TKCZBW]

Ok, first off, put the error handler in last and append an 'Exit Sub' after the Return statment of the 'get_property_rent' to stop it running into the error handler. Currently, the error handler will (after its last End If) automatically run over into the other internal routine (which I asume you don't want? Or have i misunderstood and that is part of the err handler?).

You could try checking for (cant test this out currently, but should give an idea):

Please Login or Register  to view this content.

[jmac1947]

@TKCZBW,

Thanks for the reply. I will re-arrange the code so that it appears in a better sequence but that will not really help (both parts of the IF in the error handler include an exit sub so I cannot accidentally run into the "get_property" code)

Unfortunately my data is a bit sensitive so I will now spend a while cleaning it up a bit to protect the guilty and then post a couple of workbooks to allow you to perhaps step through and see exactly what I am struggling to describe adequately

Thanks again for your help

Jmac

[romperstomper]

It should work if you use:

Please Login or Register  to view this content.

You should just end up with '#REF!' in the cell as a value.

[jmac1947]

Hi Rory,

Sadly no joy... I still get the popup message that I included in my previous post. I do end up with #ref in the response cell but not without having to manually respond to the popup first, which is what I am trying to overcome

@TKCZBW & @Rory

Here are a couple of sample workbooks. It doesn't matter where they are as long as they are both in the same directory / filepath

The financials 2013 workbook is simply a reference "lookup" workbook as far as this particular macro is concerned. The tab "susa46" shows in cell AA4 the value I am expecting to be returned to the executing macro in the Bank Statement Processing workbook.

In the Bank Statement workbook, select "maintenance tasks" from the opening page and then select "update Rent Lookups" from the "Constants" page.

On the resulting page the sort macro works. It is the update expected rents that is causing me grief.

The actual macro that is being executed after you press the "update expected rents" button is "update_frequence_rent_values"

Bank Statement Processing V1-0.xlsm

Financials 2013.xlsm

My code may not be pretty but it generally works (reflects the age of the coder I suspect )

[romperstomper]

That's really odd because it worked when I tested quickly earlier but clearly doesn't in your file.

Will your formula string (filepath, workbsheet name and cell reference) ever be longer than 255 characters? If not, you could use:

Please Login or Register  to view this content.

[jmac1947]

@rory,

sadly I cannot guarantee that the resolution of the variables in the formula will always be less than 255

[romperstomper]

Is it imperative that you don't open the source workbook? If so, ADOX might be an option - it will allow you to test for the presence of a specific sheet name.

[jmac1947]

Hi Rory,

Firstly, no it isn't imperative that I don't open the second workbook.

I have almost no experience in trying to deal with two workbooks at once, it is just beyond my current capacity and I have not been able to get a good example that actually fits my needs despite trying for a number of times. I was actually very happy to at least get as far as the current code gets if the truth be known.

I will Google "ADOX" as it isn't anything I have ever heard of before but as you can see from the code itself, I need the value back from the worksheet, I guess if I know it isn't actually there I can work around it.

The frustrating thing for me is that while I know the "error situation" will be at best 1:100 AND given that I know the end users of my code pretty well (family friends in their own business) I can construct a suitable "cheat sheet" to get them past the actual problem, the actual problem shouldn't be that hard to resolve.

Again, my sincere thanks to you and @TKCZBW for your help

Jmac

[romperstomper]

Here's a simple example function to return an array of the sheets in the file:

Please Login or Register  to view this content.

After adding that function to your module, change your code to:

Please Login or Register  to view this content.

[jmac1947]

Hi Rory,

Well all I can say in complete admiration is that while it might be a simple function for you I would never in 10 years have worked out that code, as it is I still need to study what you have done to try to get an understanding of the what you did

However, I did just as you suggested in your last post and voila, the issue happily resolved.

Many many thanks for your assistance. This has avoided a pretty ugly work around.

[romperstomper]

Glad to help.