using a custom module, how to add a  tag after the theme's css files
Everything you add with drupal_set_html_head() is added before the CSS styles added with drupal_add_css(). In fact, the page template outputs them using the following code.

&lt;head&gt;
  &lt;?php print $head; ?&gt;
  &lt;title&gt;&lt;?php print $head_title; ?&gt;&lt;/title&gt;
  &lt;?php print $styles; ?&gt;
  &lt;?php print $scripts; ?&gt;
  &lt;script type="text/javascript"&gt;&lt;?php /* Needed to avoid Flash of Unstyled Content in IE */ ?&gt; &lt;/script&gt;
&lt;/head&gt; 


$head and $styles are initialized in template_preprocess_page() with the following code.

$variables['head'] = drupal_get_html_head();
$variables['styles'] = drupal_get_css();


As drupal_add_css() is used to add all the CSS styles used by a theme, including the ones defined in its .info file, you can change the order the CSS styles are added by changing the page template. If it would contain the following code, everything added with drupal_set_html_head() will come after everything added with drupal_add_css(). I would not suggest doing this, if not after verifying it doesn't cause any problem.

&lt;head&gt;
  &lt;title&gt;&lt;?php print $head_title; ?&gt;&lt;/title&gt;
  &lt;?php print $styles; ?&gt;
  &lt;?php print $head; ?&gt;
  &lt;?php print $scripts; ?&gt;
  &lt;script type="text/javascript"&gt;&lt;?php /* Needed to avoid Flash of Unstyled Content in IE */ ?&gt; &lt;/script&gt;
&lt;/head&gt; 


Notice that with Drupal 7, you can use drupal_add_css() to use inline CSS style too: Just pass the CSS styles as first argument, and pass array('type' =&gt; 'inline', 'weight' =&gt; 100) as second argument. You need also to use the group property. In that way, you don't need to alter the template file just to add your CSS  styles after the ones added by Drupal.
Generally, it is preferable to add the CSS styles in a file; in that way, they can be aggregated/compressed.