Introduction

JKP Application Development Services.

Microsoft Office Application Development

Introduction

Up • Introduction • How does it work? • Setting things up • Class Module • VBA code • Put to Use • Wrap Up

• •

Search

Zoeken

Go to	Ga Naar
Home Up

Donate	Donaties
Did you find something helpful on my site? Consider a donation! Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

Registering a User Defined Function with Excel

Introduction

When one writes a User Defined Function in Excel VBA, this function appears in the function wizard under the category "Custom". It is possible to set which category the function belongs to by changing the "macro properties" of the UDF. Unfortunately, VBA does not allow specifying a description for the UDF's arguments. There is a workaround which uses the old XLM REGISTER function in conjunction with the ExecuteExcel4Macro function from within VBA (as described in the excellent book Professional Excel Development). This function however only accepts 255 characters or less, which is insufficient for UDF's with more than just one or two arguments and severely limits the amount of text one can use for the explanation of the arguments. This article describes how this can be overcome, using an ancient XLM macro sheet together with some trickery invented by Laurent Longre.

Originally I used an XLM (Excel 4 macro) sheet to overcome the 255 character limit, but this caused Excel to crash very frequently.

KeepItCool invented a workaround by putting the arguments of the register function into Excel's little know hidden namespace (Application level defined names only accessible through Excel4 macro commands). He also wrote the workbook that can be downloaded here.

Subscribe in a reader