application worksheetfunctions fail

[Sybmathics]

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

 

[Don Guillett]

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

 

[Dave Peterson]

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

 

[Sybmathics]

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.

 

[Sybmathics]

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

 

[Gary''s Student]

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

 

[Bernard Liengme]

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

 

[Sybmathics]

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

 

[Sybmathics]

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!

 

[Don Guillett]

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

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

 

[Sybmathics]

Don Guillett schreef:

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

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

Don,

the dateserial sugggestion did the job.

via the help menu i found the instruction on how to use it. (same as in
Excel)

Strangely though, there was no help on val.

VBA Help doesn't seem to be as accurate as Excel Help.

Thanks for your time and suggestions.

Sybolt

 

[Ron Rosenfeld]

Dave Peterson schreef:

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

DateSerial in VBA works pretty much the same as the DATE worksheet function,
but it can use a wider range of dates.

The Value function in VBA is very different from the Value function in Excel as
it will ignore trailing text in the string, converting what it can starting at
the beginning of the string.

If you want to convert strings to values, and have an error message if the
whole string cannot be converted, you could use a data conversion function like
CDbl.
--ron

 

[Sybmathics]

Don Guillett schreef:

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

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

This one really helped, Don.

Do you know if there's a list somewhere that matches vba and excel
functions.

That would be really helpful.
In stead of calling a worksheetfunction I could simply use a vba function.

Thanks again,

Sybolt

 

[Don Guillett]

In the vba help index, I typed val and got:
Val Function


Returns the numbers contained in a string as a numeric value of appropriate
type.

Syntax

Val(string)

The required string argument is any valid string expression.

Remarks

The Val function stops reading the string at the first character it can't
recognize as part of a number. Symbols and characters that are often
considered parts of numeric values, such as dollar signs and commas, are not
recognized. However, the function recognizes the radix prefixes &O (for
octal) and &H (for hexadecimal). Blanks, tabs, and linefeed characters are
stripped from the argument.

The following returns the value 1615198:

Val(" 1615 198th Street N.E.")
In the code below, Val returns the decimal value -1 for the hexadecimal
value shown:

Val("&HFFFF")
Note The Val function recognizes only the period (.) as a valid decimal
separator. When different decimal separators are used, as in international
applications, use CDbl instead to convert a string to a number.

 

[Sybmathics]

Don Guillett schreef:

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

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

This one did the job, thanks Don.


Do you know if there's a list somewhere to compare or match vba and
excel functions?

I would prefer using vba functions in stead of calling
application.worksheetfunction.blabla(arguments) all the time.

Save a lot of time and annoyance.

Thanks again.


Sybolt

 

[Don Guillett]

http://www.google.com/search?source...S219&q=vba+equivalents+to+worksheet+functions