I can probably answer any question you have about Modular Merchant, I've worked with them.
"Their website does not look that great"
Fortunately, they are aware of this. A redesign of the entire ModularMerchant.com website is in process. The new design has already been applied to some of MM's satellite sites, such as SubscriptionProducts.com.
"I'm not entirely sure on the capabilities of their package"
The MM package includes the shopping cart software and several other features related to ecommerce, such as an Affiliate marketing system, email campaign manager, coupons/gift certificates, etc. If you have any questions about specific functionality, I encourage you to send them in using the contact form on the MM website. They'll be happy to discuss any question in detail with you.
"(it looks like they host the site/store for you?)"
Yes, Modular Merchant is a hosted shopping cart solution. (SaaS) There are several reasons for this, including PCI Compliance and other security reasons. However, a "host your own" solution is also in consideration, but no timeline for its release has been determined.
"I've always wanted to insert shopping cart modules into my design--rather than designing around an eCommerce template."
I don't know if this is exactly the answer you're looking for, but each page of the shopping cart software (store home, checkout pages, customer account area, etc.) is template driven. The design of each page is controlled by a standard HTML template, which can be edited just like any other web page. Templates can also include JavaScript and PHP code. Each dynamic item on a page is displayed by what MM calls a "QuickCode Tag". For example, the name of your company is displayed by adding the tag {COMPANY-NAME} anywhere in your template. There's over 700 QuickCode Tags available. They can be placed anywhere on the page, allowing the shopping cart's content to be incorporated into your page design however you like. Is this the type of design info you were looking for?
If you have any other questions, contact them and they'll be happy to provide any info or examples. They also try to leave out the "salesman schlock", and provide the most helpful info they can.
I hope this helps!