Show what options are in custom function?

I am using Office 2003 on Windows XP.

Suppose you have a custom public function which requires certain arguments.
Suppose further that one of those arguments could be one of three choices.

When you call the function and open the parentheses you see the listed
arguments. Is it also possible to show the three possible choices for the one
argument?

Is so, could someone please illustrate how to do this? See example function
below:

Public Function CalculateValue(argItem1, argItem2, argType) as Long
If argType = "Normal" then ...
If argType = "Modified" then ...
If argType = "None" then ...
End Function

When I'm entering a call to the function as in: Call
CalculateValue(lngValue1, lngValue2, ...), when I get to the point where I
need to enter the "argType" I want to see what my options are (i.e. Normal,
Modified, or None). Kind of like auto-sensing.

Can this be done? If so, how? Thanks much in advance.

You can't have the editor display the possible choices for an
argument unless the argument's type is Long. In this case, you
use an Enum type. E.g.,


Public Enum MyType
Modified = 0
Normal = 1
None = 2
End Enum

Function MyFunction(Arg1 As MyType)
' your code here
End Function

With enums, you don't have to supply the actual values, as show
in my example. If you omit the values, the first value will be
zero, and values will be sequentail. Note that enums are
supported in Excel 2000 at later.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"quartz" <quartz@discussions.microsoft.com> wrote in message
news:771F5A56-CF5F-42DD-B277-09E093589101@microsoft.com...
>I am using Office 2003 on Windows XP.
>
> Suppose you have a custom public function which requires
> certain arguments.
> Suppose further that one of those arguments could be one of
> three choices.
>
> When you call the function and open the parentheses you see the
> listed
> arguments. Is it also possible to show the three possible
> choices for the one
> argument?
>
> Is so, could someone please illustrate how to do this? See
> example function
> below:
>
> Public Function CalculateValue(argItem1, argItem2, argType) as
> Long
> If argType = "Normal" then ...
> If argType = "Modified" then ...
> If argType = "None" then ...
> End Function
>
> When I'm entering a call to the function as in: Call
> CalculateValue(lngValue1, lngValue2, ...), when I get to the
> point where I
> need to enter the "argType" I want to see what my options are
> (i.e. Normal,
> Modified, or None). Kind of like auto-sensing.
>
> Can this be done? If so, how? Thanks much in advance.

Only if you use the function wizard to insert the function and supply help
for the arguments of a function. This can't be done with VBA alone. You
can use Larent Longre's addin free addin to assist.

http://xcell05.free.fr/

There is no option for it to work like autosense, however.

--
Regards,
Tom Ogilvy

"quartz" <quartz@discussions.microsoft.com> wrote in message
news:771F5A56-CF5F-42DD-B277-09E093589101@microsoft.com...
> I am using Office 2003 on Windows XP.
>
> Suppose you have a custom public function which requires certain
arguments.
> Suppose further that one of those arguments could be one of three choices.
>
> When you call the function and open the parentheses you see the listed
> arguments. Is it also possible to show the three possible choices for the
one
> argument?
>
> Is so, could someone please illustrate how to do this? See example
function
> below:
>
> Public Function CalculateValue(argItem1, argItem2, argType) as Long
> If argType = "Normal" then ...
> If argType = "Modified" then ...
> If argType = "None" then ...
> End Function
>
> When I'm entering a call to the function as in: Call
> CalculateValue(lngValue1, lngValue2, ...), when I get to the point where I
> need to enter the "argType" I want to see what my options are (i.e.
Normal,
> Modified, or None). Kind of like auto-sensing.
>
> Can this be done? If so, how? Thanks much in advance.

My comments are related to using a custom function in a worksheet.

I believe Chip's relate to using it in the VBE. But, he may know something
I don't.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:O99JwZaSFHA.244@TK2MSFTNGP12.phx.gbl...
> Only if you use the function wizard to insert the function and supply help
> for the arguments of a function. This can't be done with VBA alone. You
> can use Larent Longre's addin free addin to assist.
>
> http://xcell05.free.fr/
>
> There is no option for it to work like autosense, however.
>
> --
> Regards,
> Tom Ogilvy
>
> "quartz" <quartz@discussions.microsoft.com> wrote in message
> news:771F5A56-CF5F-42DD-B277-09E093589101@microsoft.com...
> > I am using Office 2003 on Windows XP.
> >
> > Suppose you have a custom public function which requires certain
> arguments.
> > Suppose further that one of those arguments could be one of three
choices.
> >
> > When you call the function and open the parentheses you see the listed
> > arguments. Is it also possible to show the three possible choices for
the
> one
> > argument?
> >
> > Is so, could someone please illustrate how to do this? See example
> function
> > below:
> >
> > Public Function CalculateValue(argItem1, argItem2, argType) as Long
> > If argType = "Normal" then ...
> > If argType = "Modified" then ...
> > If argType = "None" then ...
> > End Function
> >
> > When I'm entering a call to the function as in: Call
> > CalculateValue(lngValue1, lngValue2, ...), when I get to the point where
I
> > need to enter the "argType" I want to see what my options are (i.e.
> Normal,
> > Modified, or None). Kind of like auto-sensing.
> >
> > Can this be done? If so, how? Thanks much in advance.
>
>

Thanks Chip and Tom.
Sorry, I should have made it clearer Tom, I was looking for the VBE method.

Thanks for your reply.

"Tom Ogilvy" wrote:

> My comments are related to using a custom function in a worksheet.
>
> I believe Chip's relate to using it in the VBE. But, he may know something
> I don't.
>
> --
> Regards,
> Tom Ogilvy
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:O99JwZaSFHA.244@TK2MSFTNGP12.phx.gbl...
> > Only if you use the function wizard to insert the function and supply help
> > for the arguments of a function. This can't be done with VBA alone. You
> > can use Larent Longre's addin free addin to assist.
> >
> > http://xcell05.free.fr/
> >
> > There is no option for it to work like autosense, however.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "quartz" <quartz@discussions.microsoft.com> wrote in message
> > news:771F5A56-CF5F-42DD-B277-09E093589101@microsoft.com...
> > > I am using Office 2003 on Windows XP.
> > >
> > > Suppose you have a custom public function which requires certain
> > arguments.
> > > Suppose further that one of those arguments could be one of three
> choices.
> > >
> > > When you call the function and open the parentheses you see the listed
> > > arguments. Is it also possible to show the three possible choices for
> the
> > one
> > > argument?
> > >
> > > Is so, could someone please illustrate how to do this? See example
> > function
> > > below:
> > >
> > > Public Function CalculateValue(argItem1, argItem2, argType) as Long
> > > If argType = "Normal" then ...
> > > If argType = "Modified" then ...
> > > If argType = "None" then ...
> > > End Function
> > >
> > > When I'm entering a call to the function as in: Call
> > > CalculateValue(lngValue1, lngValue2, ...), when I get to the point where
> I
> > > need to enter the "argType" I want to see what my options are (i.e.
> > Normal,
> > > Modified, or None). Kind of like auto-sensing.
> > >
> > > Can this be done? If so, how? Thanks much in advance.
> >
> >
>
>
>

You checks would then be against the enum types defined:

Public Function CalculateValue(argItem1, argItem2, argType As MyType) As
Long
Dim sMsg As String
If argType = Modified Then
sMsg = "Modified"
ElseIf argType = Normal Then
sMsg = "Normal"
ElseIf argType = None Then
sMsg = "None"
Else
sMsg = "Not Valid"
End If

End Function

Without quotes.

--
Regards,
Tom Ogilvy

"quartz" <quartz@discussions.microsoft.com> wrote in message
news:E0BE325C-3D15-4F43-9E4F-FA80CC915C23@microsoft.com...
> Thanks Chip and Tom.
> Sorry, I should have made it clearer Tom, I was looking for the VBE
method.
>
> Thanks for your reply.
>
> "Tom Ogilvy" wrote:
>
> > My comments are related to using a custom function in a worksheet.
> >
> > I believe Chip's relate to using it in the VBE. But, he may know
something
> > I don't.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> > news:O99JwZaSFHA.244@TK2MSFTNGP12.phx.gbl...
> > > Only if you use the function wizard to insert the function and supply
help
> > > for the arguments of a function. This can't be done with VBA alone.
You
> > > can use Larent Longre's addin free addin to assist.
> > >
> > > http://xcell05.free.fr/
> > >
> > > There is no option for it to work like autosense, however.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "quartz" <quartz@discussions.microsoft.com> wrote in message
> > > news:771F5A56-CF5F-42DD-B277-09E093589101@microsoft.com...
> > > > I am using Office 2003 on Windows XP.
> > > >
> > > > Suppose you have a custom public function which requires certain
> > > arguments.
> > > > Suppose further that one of those arguments could be one of three
> > choices.
> > > >
> > > > When you call the function and open the parentheses you see the
listed
> > > > arguments. Is it also possible to show the three possible choices
for
> > the
> > > one
> > > > argument?
> > > >
> > > > Is so, could someone please illustrate how to do this? See example
> > > function
> > > > below:
> > > >
> > > > Public Function CalculateValue(argItem1, argItem2, argType) as Long
> > > > If argType = "Normal" then ...
> > > > If argType = "Modified" then ...
> > > > If argType = "None" then ...
> > > > End Function
> > > >
> > > > When I'm entering a call to the function as in: Call
> > > > CalculateValue(lngValue1, lngValue2, ...), when I get to the point
where
> > I
> > > > need to enter the "argType" I want to see what my options are (i.e.
> > > Normal,
> > > > Modified, or None). Kind of like auto-sensing.
> > > >
> > > > Can this be done? If so, how? Thanks much in advance.
> > >
> > >
> >
> >
> >

I Need to call an addin from another addin and also I need to pass arguments
to that addin. Is it possible. If so pls give me suggestions.

Do you mean a macro within the addin? That would be


.... with parameters

Application.Run "myAddin.xla!test_msgbox", 4, 8


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Prabakar" <Prabakar@discussions.microsoft.com> wrote in message
news:CEC9D50B-DE83-47D2-A7DF-A1D8BA03B0B4@microsoft.com...
> I Need to call an addin from another addin and also I need to pass
arguments
> to that addin. Is it possible. If so pls give me suggestions.