Back in the day, you could make a website with a single page of code. Technically you still can, but it would either be extremely simple for today's standards, or a big confusing mess. It's interesting to note that even then, this single file would most likely be called "index.html" and would work as a replacement for the directory's default index: a list of the files in that directory on the server. Even the simplest list is modular in the sense that it's composed of individual units.
Modularity has grown from separating images and CSS files into their own directories and files to grouping entire chunks of installable and configurable functionality. Content management systems like Drupal are prime examples of why this is good practice even with your own custom code. The question we always ask ourselves as we develop is, "Can this [module/theme/functionality] be disabled without affecting any other functionality?" If the answer is "no", we revise it until it works as independently as possible.
So when should you go modular? Let's discuss three examples of when this is a good idea with Drupal.
First: When you're setting up your site.
So you've downloaded some essential contributed modules (such as Views, Imagecache, and CCK if you're using D6), installed Drupal core, and possibly a theme. You've put your modules in the sites/all/modules directory and are about to create another custom module. But wait, let's go back a second. What if your client wants a subdomain or a second, related site down the line? Now is the perfect time to prepare for those possibilities by configuring your Drupal site as a multi-site. Instead of piling everything into sites/all, create a sites/example.com directory and determine what would be useful everywhere and what would only make sense for example.com. In each of those directories, create modules/contrib and modules/custom to separate anything contributed to drupal.org from any custom modules you build. Need help with the multi-site setup? Drupal.org has a slew of great tutorials and documentation (http://drupal.org/node/43816).
What's the advantage to the multi-site setup? It allows you to compartmentalize your code into site-specific divisions. Say example.com has video functionality that sub.example.com does not, but you need to share users or other functionality between them. This would be the perfect time to put your Embedded Media Field module into sites/example.com/modules/contrib and everything else that's used across both sites in sites/all/modules/contrib. Or, what if you need to build a custom games module for example.com that won't be available on sub.example.com? Same thing, except your custom work will go in the respective modules/custom directory. Themes are another great example. Maybe sub.example.com is a mobile version of example.com and requires a different theme. Now you have sites/sub.example.com/themes, sites/example.com/themes, and sites/all/themes to work with.
Second: When you're writing custom modules.
Drupal and a lot of its contributed modules are immensely configurable out of the box. However, no matter how robust modules get, we always end up needing a custom solution somewhere and therefore, a custom module. But again, you're stuck with the dilemma of where to pile those hooks and preprocessors. Cram it all in your aforementioned games module or an ever-expanding site module that if disabled will send your site back to an OOTB contrib state? Or modularize your custom folder even further? Here's a hint: choose the latter. You'll thank yourself later. Not only will this minimize or even eradicate the adverse effects of enabling/disabling such giant modules, but it'll also add to the best practice of self-documenting code by allowing for more specific module naming, making your code easier to navigate.
In some cases, you may not need custom edits, but it's still a good idea to export and backup certain bits of content to code, e.g. views, especially if you're working as part of a team and need to keep track of who made edits, you or the client. Keeping your views modular, i.e. adding your views export to a related module instead of stashing them all in a single site module, further exemplifies this best practice. Again, ask yourself, "Can this be disabled without affecting any other functionality?" If your views are stored in one module, your whole site will go down.
Third: When writing a custom theme.
It's easy to think of modules in a modular fashion, but how can we apply that practice to our theme? Now comes the question, "Does this belong in a theme or a module?" With all the theme functions and preprocess hooks in Drupal, it's both easy to load up your theme's template.php very quickly and tough to logically separate your theme from your modules. Generally speaking, if there are template files or theme function overrides involved, it should go in your theme. However, if your changes are part of a custom module's general scope of functionality, like if you need to use template_preprocess_views_view to customize the output of your games view, put it with your custom games module. Keep your theme focused on the design, markup and CSS and your modules focused on functionality. Sometimes you may use the same functions in both, but keeping the separation of purpose will make your site organized and easy to update.