+ Reply to Thread
Results 1 to 5 of 5

The right place for an error-checking MSGBOX?

  1. #1
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    The right place for an error-checking MSGBOX?

    Hello everyone, first i'd like to thank this community for giving me the opportunity to learn about VBA and make my working and personal life more efficient!

    In my workbook I have got lots of macros that do various things and i'm at the point where I feel confident enough to add some error-checking and debugging to them.

    In the code below, Excel is looking for a CSV file that has been downloaded from the internet and is currently open. Since it has a different ending to it's name each time it is downloaded, excel has to cycle through the open workbooks until it finds the right one, and then run the routine. This works a treat and if there is no sheet open it gives the user a message asking if they want to go online and get a download.

    However, I'm not familiar enough with the FOR loop in order to put that msgbox in the right place, so even once the sheet has been found and the routine has been successful i get the popup. Can anyone please give me a suggestion on what to do to make the msgbox only pop up when no orders sheet has been found?

    Please Login or Register  to view this content.
    As a side note, I'm aware that the code contains a copy/paste instead of range.value = range.value but for some reason the code doesn't react well when i change it to that method. Maybe i'm not being explicit enough since excel doesn't actually know the name of the orders workbook? Either way this is a remnant from the time before I had learnt so much about VBA and i'll revisit to make the code better in the future
    IF("helping me", "thanks", "need more detail?")

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: The right place for an error-checking MSGBOX?

    Not finding an open workbook is not really an error in the literal sense - errors are when an unexpected event/set of circumstances actually throw a wobbly.

    The easiest way to determine if one of the Orders workbooks was found is to declare a Boolean variable at the top of the procedure. Then, if a workbook is found in the loop set it to True (it will be False by default after declaring it).

    Then, immediately before the download MsgBox check if the variable is False.

    You will also find it easier to follow the logic of code if you indent control structures correctly.
    Please Login or Register  to view this content.
    Instead of the Boolean Flag, it might be possible to check if the check cell (BX3) is empty before popping the MsgBox but I don't know enough of the logic of this to be definite.
    Last edited by cytop; 05-21-2016 at 05:20 AM.

  3. #3
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: The right place for an error-checking MSGBOX?

    Thank you cytop.

    I have often read about "correct" indentation but rarely are there examples of what people are actually talking about. I'm glad to see that you have indented so i can see better what it actually looks like. I've incorporated the boolean into my Sub so thanks for that.

    Out of interest, if this isn't called error-checking as such then what is it called? User-error? i suppose not, as in this case the user is not making a mistake - maybe just a user-prompt?

    Thank you also for the quick response

  4. #4
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: The right place for an error-checking MSGBOX?

    It's simply the logic flow. If you read any code of reasonable length you'll probably see a lot of IFs
    Please Login or Register  to view this content.
    An error is when VBA chucks out the Debug/End message - this is handled by Error Trapping - Have a read. you'll find lots of interesting (in a sad way) stuff on that site.

    To help with indenting code, and other stuff, get yourself a copy of MZ Tools 3.0 (Free, click here). This is a long established utility and the older versions as used by VBA are now free but it is only available for 32 bit installations. It also has a load of other utilities, some very good, some useless (my opinion - I'm sure what I find useless is useful to someone else).

    PS: The MZTools link will start the download immediately. I have no problem with that - you may. You should search for another link if you want to read about it first.

  5. #5
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: The right place for an error-checking MSGBOX?

    Thanks, i have read and bookmarked the error trapping page for future reference. You're right about it being interesting but not in a sad way. Although, I might not mention it at my next cocktail party lol.

    You've been massively helpful, thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Error Handler: place zero in error cell
    By lostest in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-26-2015, 06:10 PM
  2. [SOLVED] Error checking routine - check range values on various worksheets and report if error
    By TheRobsterUK in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-29-2014, 06:36 AM
  3. Replies: 2
    Last Post: 02-18-2013, 10:17 AM
  4. The correct position to place msgbox in code
    By Jim28 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-23-2011, 07:46 PM
  5. How can 1-Msgbox be used in place of Many?
    By 54Libra in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-21-2007, 05:23 PM
  6. Place Msgbox away from centre of screen?
    By PaulC in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-17-2007, 08:03 AM
  7. MsgBox - place and size?
    By Einar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-03-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1