![]() display a message to your user letting them know something went wrongĪs an aside, if you want to create macros that other people will use – perhaps other people you work with – having well behaved code will make you look far more professional than code which crashes without warning.Īlso, as people never (okay, rarely) read error messages, take the time to save the details of the error somewhere (like an error log) to help you figure out what went wrong and how to fix it.record the details of the error to a log.VBA provides the ability to trap unexpected errors giving you a second chance to deal with them. Secondly – when all else fails – you can “trap” the error. If the file does not exist, you can display a message to your user or exit gracefully without your macro crashing. One example is checking to confirm a file exists before trying to open it. That is, code which tries to anticipate where problems may occur and pre-empts the problem. In contrast, you can write error handling code to manage how an error should be managed.įirstly, you can write defensive code. You can't handle errors in a recorded macro. That is, an unexpected – and unhandled – error which stops the code running. When things go wrong, your VBA macro will encounter a runtime error. Maybe your code tries to copy data to a worksheet which has been renamed. Maybe your code tried to open a file that doesn’t exist. Let’s face it, even some of the best written (or recorded) VBA code may encounter a situation you didn’t expect or plan for. ![]() You can't create dialog boxes and input boxes in a recorded macro but you can if you write it by hand.ĭialog boxes are great if you want to make your spreadsheet more structured and leverage off built-in functionality. When you record a macro, you must “select” the object you want to work with before you can perform the “task”. Most tasks in Windows are based on the follow two-step process:įor example, in Windows Explorer this could be: Sure, the macro recorder will allow you to select and open a file, but it won’t allow you to do the kinds of tasks you would perform with Windows Explorer, for example.įor tasks like these, you’ll need to write you own code. These are tasks performed with your computer (operating system) itself. Sometimes the tasks you need to perform extend beyond Excel (or Word, or Access or PowerPoint).įor example, you may need to copy files, get a list of files to process, check if another spreadsheet exists or confirm you have access to a network drive. To make all the Microsoft Office application sing-and-dance together, you’ll need to write the VBA code yourself. You’ve probably already guessed by now, and you’d be right - automating one application from another cannot be done using the macro recorder.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |