Using WorksheetFunction.Find in VBA

  • Thread starter Thread starter Schizoid Man
  • Start date Start date
S

Schizoid Man

Hi,

I have the following code snippet that I'm using to find whether one digit
is part of another large number using something like:

If IsNumeric(WorksheetFunction.Find(Third, y)) Then
Result = TRUE
Else
Result = FALSE
End If

where y is the large number and 'Third' represents the a particular digit.
If the expression in the IF statement does contain the said digit, the
result of the statement is that Result variable is assigned the value TRUE.
However, if the digit is not contained, then rather than hitting the ELSE
bit of the above statement, the code just unexpectedly terminates.

Any suggestions?

Thx,
Schiz
 
Bob Phillips said:
Use Instr

Result = InStr(y, third) > 0


--

HTH

Bob
Click to expand...

Hi Bob,

That certainly does help - your solution worked like a charm, though I'm not
sure why. How is it different from what I had originally written down?

Thx.
 
It is different because the worksheet function errors out if the value is
not found, and you are not handling an error. It can be done, like so

Dim tmp As Long

On Error Resume Next
tmp = WorksheetFunction.Find(third, y)
On Error GoTo 0

Result = tmp <> 0

but it is far more efficient to use a VBA function in VBA, rather than
calling out to Excel.
 
Back
Top