intellisense functionality in a custom VBA function?

Question

In the standard IDE for VBA, intellisense is built-in to many standard VBA functions. i.e. the buttons variable for msgbox() gives you a list of options for how you want the messagebox to be displayed. This way, the developer doesn't have to memorize or look up the options every time function is used.

Can I achieve the same for my custom VBA functions? This is a rough example, but can i write something like:

Public Function DoSomething(X as string)(Options X="Opt1","Opt2") as variant
...

When I call this function, I would get a pop-up giving my options for X as Opt1 and Opt2

Answer 1

You'll need to declare your own enumerations, and then define the parameter to your functions as that enumerated type.

Public Enum eOptions
   Option1
   Option2
End Enum

public Function DoSomething(ByVal x as string, Byval MyOption as eOptions)

When you call the function ala this:

Call DoSomething("myValue", Option2)

You'll see the values available for the second parameter to the function as either "Option1" or "Option2".