UDF misunderstanding?All 7 messages in topic - view as tree
Fairfax O'Riley Aug 12 2001, 10:43 am show options

Newsgroups: microsoft.public.excel.programming
From: Fairfax O'Riley - Find messages by
this author
Date: Sun, 12 Aug 2001 13:42:32 -0400
Local: Sun, Aug 12 2001 10:42 am
Subject: UDF misunderstanding?
Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Hello all. I'm hoping somebody can give me a pointer.


The following code:
Option Explicit


Function Example() As Integer
Dim oc As Object


Set oc = Worksheets("Sandbox").Cells.Find( _
What:="Some string to look for", _
LookIn:=xlValues)
Example = Len(oc.Value)
End Function


Keeps giving me a #VALUE! error in Excel. Can anybody give me an
idea of why?


This is reduction of a much more involved function which grabs some
data from the web, so I don't know the format of the data, then
searches for a substring and returns the value of a number after the
substring. But the snippet above exemplifies my problem (and
ignorance, probably).


I'm something of a newbie to VBA, so no pride of
authorship or anything here... all thoughts greatly appreciated.


Thanks in advance,
--Fairfax O'Riley



Dave Peterson Aug 12 2001, 11:01 am show options

Newsgroups: microsoft.public.excel.programming
From: Dave Peterson - Find messages by this
author
Date: Sun, 12 Aug 2001 13:00:33 -0500
Local: Sun, Aug 12 2001 11:00 am
Subject: UDF misunderstanding?
Reply to Author | Forward | Print | Individual Message | Show or
iginal | Report Abuse

Turns out that .Find won't work if called from a userdefined function
called
from a worksheet.


If your data is always in the same row/column, you could use
application.match().


Another option would be to loop through the usedrange and use InStr.




- Hide quoted text -
- Show quoted text -

Fairfax O'Riley wrote:

Hello all. I'm hoping somebody can give me a pointer.


The following code:
Option Explicit


Function Example() As Integer
Dim oc As Object


Set oc = Worksheets("Sandbox").Cells.Find( _
What:="Some string to look for", _
LookIn:=xlValues)
Example = Len(oc.Value)
End Function


Keeps giving me a #VALUE! error in Excel. Can anybody give me an
idea of why?


This is reduction of a much more involved function which grabs some
data from the web, so I don't know the format of the data, then
searches for a substring and returns the value of a number after the
substring. But the snippet above exemplifies my problem (and
ignorance, probably).


I'm something of a newbie to VBA, so no pride of
authorship or anything here... all thoughts greatly appreciated.


Thanks in advance,
--Fairfax O'Riley



--

Dave Peterson




Fairfax O'Riley Aug 12 2001, 11:08 am show options

Newsgroups: microsoft.public.excel.programming
From: Fairfax O'Riley - Find messages by
this author
Date: Sun, 12 Aug 2001 14:07:36 -0400
Local: Sun, Aug 12 2001 11:07 am
Subject: UDF misunderstanding?
Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Dave,


Thank you!


--F


On Sun, 12 Aug 2001 13:00:33 -0500, Dave Peterson
wrote:



- Hide quoted text -
- Show quoted text -

Turns out that .Find won't work if called from a userdefined function
called
from a worksheet.

If your data is always in the same row/column, you could use
application.match().


Another option would be to loop through the usedrange and use InStr.




Chip Pearson Aug 12 2001, 11:11 am show options

Newsgroups: microsoft.public.excel.programming
From: "Chip Pearson" - Find messages by this
author
Date: Sun, 12 Aug 2001 13:10:25 -0500
Local: Sun, Aug 12 2001 11:10 am
Subject: UDF misunderstanding?
Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Fairfax,


VBA code that is called from a worksheet cell cannot change in
any way the workbook or the Excel environment. In other words,
code can read, but never write, values. Any attempt to do
otherwise causes Excel to terminate execution and return a #VALUE
error.


While you might think that Find would be supported (it is, of
course, really a read-only function), it is not supported. Why?
No one knows. It may be a bug, or it may be that it is so
closely tied to Replace that it is on the 'forbid list'.


In any case, you can't use Find in any VBA code called by a
worksheet cell.


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



"Fairfax O'Riley" wrote in message


...


- Hide quoted text -
- Show quoted text -

Hello all. I'm hoping somebody can give me a pointer.

The following code:
Option Explicit


Function Example() As Integer
Dim oc As Object


Set oc = Worksheets("Sandbox").Cells.Find( _
What:="Some string to look for", _
LookIn:=xlValues)
Example = Len(oc.Value)
End Function


Keeps giving me a #VALUE! error in Excel. Can anybody give me
an
idea of why?


This is reduction of a much more involved function which grabs
some
data from the web, so I don't know the format of the data, then
searches for a substring and returns the value of a number
after the
substring. But the snippet above exemplifies my problem (and
ignorance, probably).


I'm something of a newbie to VBA, so no pride of
authorship or anything here... all thoughts greatly
appreciated.


Thanks in advance,
--Fairfax O'Riley




Fairfax O'Riley Aug 12 2001, 11:42 am show options

Newsgroups: microsoft.public.excel.programming
From: Fairfax O'Riley - Find messages by
this author
Date: Sun, 12 Aug 2001 14:41:07 -0400
Local: Sun, Aug 12 2001 11:41 am
Subject: UDF misunderstanding?
Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Chip,


Thank you. I assume from your explanation that using a button rather
than a worksheet cell will present a likely solution to my problem.
(No offense Dave, but looping through the range is taking forever!
About 2.5 minutes per update.)


Anyway, thanks again, Chip and Dave. (Oh, man does that have
me biting my tongue.)


--Fairfax


On Sun, 12 Aug 2001 13:10:25 -0500, "Chip Pearson"
wrote:



- Hide quoted text -
- Show quoted text -

Fairfax,

VBA code that is called from a worksheet cell cannot change in
any way the workbook or the Excel environment. In other words,
code can read, but never write, values. Any attempt to do
otherwise causes Excel to terminate execution and return a #VALUE
error.


While you might think that Find would be supported (it is, of
course, really a read-only function), it is not supported. Why?
No one knows. It may be a bug, or it may be that it is so
closely tied to Replace that it is on the 'forbid list'.


In any case, you can't use Find in any VBA code called by a
worksheet cell.




Dave Peterson Aug 12 2001, 12:04 pm show options

Newsgroups: microsoft.public.excel.programming
From: Dave Peterson - Find messages by this
author
Date: Sun, 12 Aug 2001 14:04:39 -0500
Local: Sun, Aug 12 2001 12:04 pm
Subject: UDF misunderstanding?
Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

At least it wasn't Chip 'n Dale...




- Hide quoted text -
- Show quoted text -

Fairfax O'Riley wrote:

Chip,


Thank you. I assume from your explanation that using a button rather
than a worksheet cell will present a likely solution to my problem.
(No offense Dave, but looping through the range is taking forever!
About 2.5 minutes per update.)


Anyway, thanks again, Chip and Dave. (Oh, man does that have
me biting my tongue.)


--Fairfax


On Sun, 12 Aug 2001 13:10:25 -0500, "Chip Pearson"

wrote:


Fairfax,


VBA code that is called from a worksheet cell cannot change in
any way the workbook or the Excel environment. In other words,
code can read, but never write, values. Any attempt to do
otherwise causes Excel to terminate execution and return a #VALUE
error.


While you might think that Find would be supported (it is, of
course, really a read-only function), it is not supported. Why?
No one knows. It may be a bug, or it may be that it is so
closely tied to Replace that it is on the 'forbid list'.


In any case, you can't use Find in any VBA code called by a
worksheet cell.



--

Dave Peterson




Chip Pearson Aug 12 2001, 12:19 pm show options

Newsgroups: microsoft.public.excel.programming
From: "Chip Pearson" - Find messages by this
author
Date: Sun, 12 Aug 2001 14:16:38 -0500
Local: Sun, Aug 12 2001 12:16 pm
Subject: UDF misunderstanding?
Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Fairfax,



Thank you. I assume from your explanation that using a button
rather
than a worksheet cell will present a likely solution to my
problem

Apologies - please ignore - Hit send by mistake on something I was playing
with

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip