VBA Error Handling

[ItalianPlatinum]

I thought this would be an easy find but I am actually having trouble finding a way to accomplish this. I am looking for a way pop a warning message if someone ran the VBA sub already. Essentially I have a file and only want my PRE work run once only and if a user tries to run it a second time I want an error message to display to sort of slow them down like are you sure you want to run this it was already run. I couldnt find anything via google searches. Someone able to help?

 

[Joe4]

A good way to do it is have your procedure update some cell on your sheet that indicates that the macro was run (it could be the date ran, or any other value you want).

Then you can put an IF...THEN clause at the beginning of your code that checks this value, and if it it matches, return your message box and exit your sub.

 

[ItalianPlatinum]

Got it so a field that says run date? how do I input that via vba?

 

[Joe4]

It could say anything you want.
Where date comes in handy is if you want it to be run every day, but only once a day. Using date would easily allow you to verify when it was last run.

So, if you wanted to place the current date in cell Z1, you would just put something like:

Range("Z1") = Date

Then, at the beginning of your code, you could have something like:

If Range("Z1") = Date Then
    Msgbox "You already ran this today!"
    Exit Sub
End If

 

[ItalianPlatinum]

It could say anything you want.
Where date comes in handy is if you want it to be run every day, but only once a day. Using date would easily allow you to verify when it was last run.

So, if you wanted to place the current date in cell Z1, you would just put something like:

Range("Z1") = Date

Then, at the beginning of your code, you could have something like:

If Range("Z1") = Date Then
    Msgbox "You already ran this today!"
    Exit Sub
End If

What if I wanted the option to rerun just pop up a warning would i remove the exit sub?

 

[Joe4]

What if I wanted the option to rerun just pop up a warning would i remove the exit sub?

Yes.

Don't be afraid to try some of these things for yourself and see what happens (just be sure to save a good copy of the file elsewhere in case things go haywire).
That is one of the best ways to learn, trial and error (that is what we do! ).

 

[ItalianPlatinum]

ok testing and failing here. If i want date and time to show i can use NOW, but i cant find a way to use NOW but if it is tomorrow to work and if it is still today to not know what I mean?

 

[Joe4]

Why do you need/care about time?

Think about it, if you use NOW(), it will never work, as time is constantly changing, and when you run it again, time will have already passed, so it will never be equal to the value you saved (in 1 second, the time will NOT be the same as it is now).

However, if you use DATE, then in 5 minutes, it will STILL be the same date (unless you it is after 11:55 PM).

 

[ItalianPlatinum]

more to know when it was run. but i think im all set ill set two cells one for NOW and one for DATE and will use DATE for the logic. Is there a way to do a decision tree on the msgbox. if yes continue to sub if no exit sub?

 

[Joe4]

more to know when it was run. but i think im all set ill set two cells one for NOW and one for DATE and will use DATE for the logic

If that is the case, you can do it with one variable, like this:

If Int(Range("Z1")) = Date Then
    MsgBox "You already ran this today!"
    Exit Sub
End If

The "INT" piece remove the time component from the value in cell Z1 for sake of the comparison.

Is there a way to do a decision tree on the msgbox. if yes continue to sub if no exit sub?

No need to. If you put that code above at the beginning of your code, then if it meets the conditions, it returns te MsgBox and exits the sub.
If it does not meet the condition, it continues on with the rest of the code.

No need to do anything else special, the logic it is already baked into how we set this up.