Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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 "Sybmathics" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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 "Sybmathics" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
On Sat, 23 Feb 2008 16:00:22 +0100, Sybmathics
wrote: 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 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Sybmathics" wrote in message ... 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
application worksheetfunctions fail
http://www.google.com/search?sourcei...heet+functions
-- Don Guillett Microsoft MVP Excel SalesAid Software "Sybmathics" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste special function in macros fail if we disable application cellDrap Drop | Excel Programming | |||
Application.WorksheetFunctions | Excel Programming | |||
WorksheetFunctions | Excel Programming | |||
Why does Outlook.Application.GetNamespace("MAPI") Fail? | Excel Programming | |||
No VBA help for Worksheetfunctions available | Excel Worksheet Functions |