Hiding AddIn Methods from Worksheet Functions But Exposing to VBA

[Matthew Wieder]

Our Automation Add-In has some methods that return object types Excel not fit
for Excel (one of the methods retunrs a custom collection for example). Is
there some way to hide this method so the end user won't try and use it as a
worksheet function, yet have it visible so that it can be used in VBA where
such objects can be correctly used? As it is, it appears all public methods
are visible in both VBA and Excel Sheet Functions.

[Ardus Petus]

Either turn the Sub to Private,
or create a dummy argument that will keep it from being shown as executable.

HTH
--
AP

"Matthew Wieder" <MatthewWieder@discussions.microsoft.com> a écrit dans le
message de news: 9D3C7EA0-790A-45A4-B8EE-EFD3CD259EF8@microsoft.com...
> Our Automation Add-In has some methods that return object types Excel not
> fit
> for Excel (one of the methods retunrs a custom collection for example).
> Is
> there some way to hide this method so the end user won't try and use it as
> a
> worksheet function, yet have it visible so that it can be used in VBA
> where
> such objects can be correctly used? As it is, it appears all public
> methods
> are visible in both VBA and Excel Sheet Functions.

[Jim Thomlinson]

At the top of a module you can add

Option Private Module which will keep everything in that module private from
the end user.

If you are in a class check out the bottom of Chip's site here...

http://www.cpearson.com/excel/ClassModules.htm
--
HTH...

Jim Thomlinson


"Matthew Wieder" wrote:

> Our Automation Add-In has some methods that return object types Excel not fit
> for Excel (one of the methods retunrs a custom collection for example). Is
> there some way to hide this method so the end user won't try and use it as a
> worksheet function, yet have it visible so that it can be used in VBA where
> such objects can be correctly used? As it is, it appears all public methods
> are visible in both VBA and Excel Sheet Functions.

[Matthew Wieder]

Making the method private hides it from VBA as well since it doesn't get
registed by COM so that won't work. What type of 'dummy argument' would
stop a method in an Automation Add-In from being shown as executable?

Ardus Petus wrote:
> Either turn the Sub to Private,
> or create a dummy argument that will keep it from being shown as executable.
>
> HTH
> --
> AP
>
> "Matthew Wieder" <MatthewWieder@discussions.microsoft.com> a écrit dans le
> message de news: 9D3C7EA0-790A-45A4-B8EE-EFD3CD259EF8@microsoft.com...
>
>>Our Automation Add-In has some methods that return object types Excel not
>>fit
>>for Excel (one of the methods retunrs a custom collection for example).
>>Is
>>there some way to hide this method so the end user won't try and use it as
>>a
>>worksheet function, yet have it visible so that it can be used in VBA
>>where
>>such objects can be correctly used? As it is, it appears all public
>>methods
>>are visible in both VBA and Excel Sheet Functions.
>
>
>

[Matthew Wieder]

I'm am in an "Automation Add-In" not an Excel VBA Add-In. Managed C#
code available in Excel XP and 2003.

Jim Thomlinson wrote:

> At the top of a module you can add
>
> Option Private Module which will keep everything in that module private from
> the end user.
>
> If you are in a class check out the bottom of Chip's site here...
>
> http://www.cpearson.com/excel/ClassModules.htm

[Bob Phillips]

Add

Option Private Module

to the head of the module. This will make all procedures private to the
project, i.e. any module in the project can use them, other projects (and
Excel) will not see them.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Matthew Wieder" <MatthewWieder@discussions.microsoft.com> wrote in message
news:9D3C7EA0-790A-45A4-B8EE-EFD3CD259EF8@microsoft.com...
> Our Automation Add-In has some methods that return object types Excel not
fit
> for Excel (one of the methods retunrs a custom collection for example).
Is
> there some way to hide this method so the end user won't try and use it as
a
> worksheet function, yet have it visible so that it can be used in VBA
where
> such objects can be correctly used? As it is, it appears all public
methods
> are visible in both VBA and Excel Sheet Functions.

[Matthew Wieder]

As I stated in my first post this is an Automation Add-In - managed C#
code; there is no module.

Bob Phillips wrote:

> Add
>
> Option Private Module
>
> to the head of the module. This will make all procedures private to the
> project, i.e. any module in the project can use them, other projects (and
> Excel) will not see them.
>

[Ardus Petus]

Any kind of argument, eg a boolean:
Public MySub(optional dummy as boolean)

HTH
--
AP

"Matthew Wieder" <MatthewWieder@discussions.microsoft.com> a écrit dans le
message de news: e8W1pXtlGHA.4076@TK2MSFTNGP05.phx.gbl...
> Making the method private hides it from VBA as well since it doesn't get
> registed by COM so that won't work. What type of 'dummy argument' would
> stop a method in an Automation Add-In from being shown as executable?
>
> Ardus Petus wrote:
>> Either turn the Sub to Private,
>> or create a dummy argument that will keep it from being shown as
>> executable.
>>
>> HTH
>> --
>> AP
>>
>> "Matthew Wieder" <MatthewWieder@discussions.microsoft.com> a écrit dans
>> le message de news: 9D3C7EA0-790A-45A4-B8EE-EFD3CD259EF8@microsoft.com...
>>
>>>Our Automation Add-In has some methods that return object types Excel not
>>>fit
>>>for Excel (one of the methods retunrs a custom collection for example).
>>>Is
>>>there some way to hide this method so the end user won't try and use it
>>>as a
>>>worksheet function, yet have it visible so that it can be used in VBA
>>>where
>>>such objects can be correctly used? As it is, it appears all public
>>>methods
>>>are visible in both VBA and Excel Sheet Functions.
>>
>>