Excel VBA

This is an interesting article that provides VBA code for a custom function that will convert a number such as 27.25 to "Twenty Seven Dollars and Twenty Five Cents". It looks like this function might be useful for creating checks, which is the main application I can think of for a function like this.

I just discovered that if you add a description to a custom excel formula (i.e. a user-defined function, VBA function, or whatever you want to call it) and then cut and paste the VBA code (so as to move it to a different spot in the VBA module for example), the description gets erased!. This can be frustrating if you have a lot of custom formulas created and documented.

By the way, what I mean by a "description" is that after you create the UDF, you go to Tools > Macros > Macros, then enter the name of the formula in the Macro Name: text box. The Options button will magically be enabled, allowing you to edit the description (which of course shows up when you use the Insert > Function feature in Excel - the whole point to wanting to add a description in this manner).

For more info on UDFs, see Custom Excel Functions.

The following article provides some ways of being more official about the way in which UDF descriptions are added and stored in categories:
Adding a Description and Category to your UDF in Excel

Dick Kusleika has provided a review of a couple resources for converting VBA code to HTML over at the Daily Dose of Excel blog.

I have been using the online version of zHTML to post VBA code on my website. It turns out that using this online tool is extremely handy for adding VBA code to this blog. I've placed a link (VBA to HTML) to the tool in the list of links on this blog so it will always be handy. Here is an example:

Function LinkAddress(cell As range, _  

                     Optional default_value As Variant)  

  'Lists the Hyperlink Address for a Given Cell  

  'If cell does not contain a hyperlink, return default_value  

  If (cell.range("A1").Hyperlinks.Count <> 1) Then  

      LinkAddress = default_value  

  Else  

      LinkAddress = cell.range("A1").Hyperlinks(1).Address  

  End If  

End Function

All you have to do is copy the VBA, paste it into the online zHTML tool, copy the resulting HTML code, and paste it right into your blog post. Quite simple. Great tool!

The purpose of this web log is to announce new or newly discovered sites, resources, and examples having to do with Excel VBA and macros. This log can be used as an RSS newsfeed if you have a related website and would like to include dynamic content. If you are an Excel consultant, adding posts to this web log can provide a means of publishing brief press releases or linking to new Excel VBA examples added to your website.

If you wish to add posts to this log, please contact me via my website (Vertex42.com).