2

I have an UDF named IP_Transpose, which is located under a standard module in Personal.xslb (so that every Excel workbook has access to it).

What I wanted to do is to register this function, so that it is accessible when user presses '=' key and it shows proper description just like any other Excel function (gives you hint when entering arguments).

Here is how I normally would register a function:

Public Sub RegisterFunction()
Dim vArg(1 To 2) As Variant
    vArg(1) = "argument description 1"
    vArg(1) = "argument description 2"

    Application.MacroOptions Macro:="IP_Transpose", Description:="Some overall description", Category:="IP_UDF", ArgumentDescriptions:=vArg
End Sub

The problem is that not only that this does not work (uness I change MacroOptions Macro:="IP_Transpose" to MacroOptions Macro:="Personal.xslb!IP_Transpose"), but also when I start typing '=IP_Tra....' I cannot see it under function list.

How can to solve this issue? (I don't want to call my function as ='Personal.xslb'!IP_Transpose, but directly typing =IP_Transpose(...).

Thanks!

pnuts
  • 58,317
  • 11
  • 87
  • 139
Robert J.
  • 2,631
  • 8
  • 32
  • 59
  • is IP_Transpose `Public`? – cyboashu Sep 10 '14 at 09:43
  • Have you considered making an add-in instead of using the Personal workbook? – Dave Sep 10 '14 at 09:54
  • You'll have to make it an add-in, but you still won't get the Intellisense prompt for the argument syntax. That's not possible. – Rory Sep 10 '14 at 11:23
  • Yes it is Public, plus I have also tried storing it to a new workbook, where I saved it as an AddIn. Afterwards I re-opened Excel and loaded that addin but it did not work either. What am I doing wrong? – Robert J. Sep 10 '14 at 11:23

0 Answers0