J-Walk & Associates, Inc.

Excel 2010 VBA Enhancements

At the official blog of the Microsoft Excel product team: Migrating Excel 4 Macros to VBA.

Excel has a macro facility, known as Excel 4 macros (XLM for short) that was the primary macro language prior to the introduction of VBA in Excel 5.0. Most people have long since migrated their Excel 4 macros to VBA; however, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult.

In Excel 2010, one of our goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA.

The article lists quite a few things that, in the past, required an XLM macro. The most important new feature is the ability to provide argument descriptions for user-defined functions. These are the descriptions that appear in the Function Arguments dialog box.

I posted a new tip that demonstrates how to do this: User-Defined Function Argument Descriptions In Excel 2010.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By SB. Comment posted 17 February, 2010 9:18am

   Can you add a link to help file with a context id when you click on "Help on this function" in the insert function wizard?
2. By John Walkenbach. Comment posted 17 February, 2010 9:31am

   Yes, you can do that. The MacroOptions method lets you specify help file and the topic ID as arguments. There's an example in my Power Programming With VBA book that uses a CHM help file.
3. By steffan. Comment posted 18 February, 2010 10:39am

   Great, now how do I get it to bring in Macros stored on the old macro sheet?
4. By chefe. Comment posted 25 February, 2010 6:56am

   is there really no way whatsoever, to achieve s.th. like that for xls07 users?
   
   i assume your approach would also work for the small explanations under each argument when i begin typing a formula in a cell? (-> this is even more handy than having descriptions in the "function wizard")
   
   cheers
5. By Doug jenkins. Comment posted 25 February, 2010 3:47pm

   chefe - This link:
   http://newtonexcelbach.wordpress.com/2009/05/21/adding-function-categories-and-descriptions/
   
   gives a simple, but limited, method of adding parameter descriptions in XL 2007 and earlier, and contains a link to another article giving a more complex, but more flexible method.
6. By chefe. Comment posted 14 March, 2010 3:05pm

   thank you so much doug!
7. By chefe. Comment posted 14 March, 2010 4:18pm

   this is a great solution if people are using the function wizard... still, this solution doesnt solve the problem that the argument description shall be shown when typing the function directly in a cell ... i.e. w/o the wizard
   cheers
8. By John Wraase. Comment posted 01 April, 2010 4:42pm

   By chefe. Comment posted 14 March, 2010 4:18pm
   this is a great solution if people are using the function wizard... still, this solution doesnt solve the problem that the argument description shall be shown when typing the function directly in a cell ... i.e. w/o the wizard
   cheers

   
   
   Yes, does anyone know if we will be able to display function arguments for UDFs in XL2010?
   
   Thanks!
9. By John Wraase. Comment posted 01 April, 2010 4:51pm

   Strange thing... I see where the MS Blog states:
   
   

   
   The only way to get argument descriptions for custom worksheet functions into the Function Wizard is using XLM Macro Options dialog box. In Excel 2010, we added a new argument to the MacroOptions VBA Method to provide support for this.

   
   
   But I am able to see my UDFs in the Function Dialog, with arguments, in Excel 2007. My Function Dialog looks just like Jack's XL2010 example at
   
   

   I posted a new tip that demonstrates how to do this: User-Defined Function Argument Descriptions In Excel 2010.