Hi all,


I am wondering why some orksheetfunctions can be addressed and others can't.

If you look in the autolist of application.worksheetfunctions there is
no suggestion for Find.
But, when you address it in a module as
mystring=application.worksheetfunction.find(all the proper arguments) it
will run as expected.
However, with the worksheetfunctions like value or date, when run in a
udf you will always end up with #value!.

What's the logic?


Any help is greatly appreciated.

Greets


Sybolt

Look in the VBA help index for FIND or record a macro while doing your find
to see how.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Sybmathics" <(E-Mail Removed)> wrote in message
news:47c02a32$0$14359$(E-Mail Removed)...
> Hi all,
>
>
> I am wondering why some orksheetfunctions can be addressed and others
> can't.
>
> If you look in the autolist of application.worksheetfunctions there is no
> suggestion for Find.
> But, when you address it in a module as
> mystring=application.worksheetfunction.find(all the proper arguments) it
> will run as expected.
> However, with the worksheetfunctions like value or date, when run in a udf
> you will always end up with #value!.
>
> What's the logic?
>
>
> Any help is greatly appreciated.
>
> Greets
>
>
> Sybolt

VBA has its own version. Look at InStr in VBA's help.



Sybmathics wrote:
>
> Hi all,
>
> I am wondering why some orksheetfunctions can be addressed and others can't.
>
> If you look in the autolist of application.worksheetfunctions there is
> no suggestion for Find.
> But, when you address it in a module as
> mystring=application.worksheetfunction.find(all the proper arguments) it
> will run as expected.
> However, with the worksheetfunctions like value or date, when run in a
> udf you will always end up with #value!.
>
> What's the logic?
>
> Any help is greatly appreciated.
>
> Greets
>
> Sybolt

--

Dave Peterson

Don Guillett schreef:
> Look in the VBA help index for FIND or record a macro while doing your
> find to see how.
>
Don, thanks for the quick reply.

I had no trouble with the find function, though.
It works as expected.

I want to know why some worksheetfunctions can be addressed in VBA and
others can't.

The find function can be addressed even though it's not in the autolist.
Excel functions like date or value can not be addressed.

I don't see why.

Dave Peterson schreef:
> VBA has its own version. Look at InStr in VBA's help.
>
>

I have no problem with Find.

I have a problem with date or value.

Are there vba alternatives for the excel worksheetfunctions like date or
value


TIA

Sybolt

All can be used, not all directly. For some you need to use =EVALUATE()


--
Gary''s Student - gsnu200770


"Sybmathics" wrote:

> Don Guillett schreef:
> > Look in the VBA help index for FIND or record a macro while doing your
> > find to see how.
> >
> Don, thanks for the quick reply.
>
> I had no trouble with the find function, though.
> It works as expected.
>
> I want to know why some worksheetfunctions can be addressed in VBA and
> others can't.
>
> The find function can be addressed even though it's not in the autolist.
> Excel functions like date or value can not be addressed.
>
> I don't see why.
>

QUOTE: I want to know why some worksheetfunctions can be addressed in VBA
and others can't

In general, you cannot use an Excel function when VBA has an equivalent one.
For example SQRT|Sqr; MOD|Mod (although the results do not agree with
negative values); SIN|Sin, and many more pairs - only the VBA function can
be used in a module

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Sybmathics" <(E-Mail Removed)> wrote in message
news:47c03386$0$14354$(E-Mail Removed)...
> Don Guillett schreef:
>> Look in the VBA help index for FIND or record a macro while doing your
>> find to see how.
>>
> Don, thanks for the quick reply.
>
> I had no trouble with the find function, though.
> It works as expected.
>
> I want to know why some worksheetfunctions can be addressed in VBA and
> others can't.
>
> The find function can be addressed even though it's not in the autolist.
> Excel functions like date or value can not be addressed.
>
> I don't see why.

Gary''s Student schreef:
> All can be used, not all directly. For some you need to use =EVALUATE()
>
>

Okay.

Seems promising.
Let me try this one.

Thanks

Sybolt

Gary''s Student schreef:
> All can be used, not all directly. For some you need to use =EVALUATE()
>
>

I tried it in several ways.
But, nope.
I keep ending up with #value!

If a vba solution is available do NOT use a worksheet function.

date use dateserial
value use Val("1000") * 3

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Sybmathics" <(E-Mail Removed)> wrote in message
news:47c03507$0$14359$(E-Mail Removed)...
> Dave Peterson schreef:
>> VBA has its own version. Look at InStr in VBA's help.
>>
>>
>
> I have no problem with Find.
>
> I have a problem with date or value.
>
> Are there vba alternatives for the excel worksheetfunctions like date or
> value
>
>
> TIA
>
> Sybolt