manage excel return #value!

February 7, 2012 at 02:04:19
Specs: Windows XP
haw mange the returned value #VALUE! of a macro? See example:
Sub testsearch()
Dim to_search, in_text As String
Dim j As Integer
Let to_search = "ABC"
Let in_text = "AB1c+ECEDAR_AB1C"
Cells(1, 7) = Application.WorksheetFunction.IsError(WorksheetFunction.Search(to_search, in_text))
End Sub

See More: manage excel return #value!

Report •


#1
February 7, 2012 at 05:39:20
VBA isn't going to protect you from invalid data; that's your job. You could use On Error, but that opens its own can of worms. If manual error handling is enabled and an error is thrown, the rest of the statement isn't executed, and what happens next depends on the error handling method active.

How To Ask Questions The Smart Way


Report •

#2
February 7, 2012 at 07:56:04
What's with Dim j As Integer? I don't see j being used anywhere in the code.

Why not use the VBA InStr function instead of the SEARCH worksheet function?

Sub testsearch()
Dim to_search, in_text As String
Dim j As Integer
Let to_search = "ABc"
Let in_text = "AB1c+ECEDAR_AB1C"
 myPosition = InStr(in_text, to_search)
   If myPosition = 0 Then
     Cells(1, 7) = "String Not Found"
   Else:
     Cells(1, 7) = "String Starts At " & myPosition
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
February 7, 2012 at 08:03:26
Razor2.3:

If the SEARCH function returns a #VALUE error, it doesn't necessarily mean that the data is "invalid".

It could simply mean that the string that is being searched for doesn't exist. Both pieces of data could be perfectly valid depending on what the data is supposed to represent.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 7, 2012 at 08:45:32
I would agree with you that "search string not found" is not an error, but Excel disagrees with the both of us. That's fine, because VBA users should use InStr() instead. But that doesn't answer the question of, "What do you do if your VBA calculations are invalid?"

How To Ask Questions The Smart Way


Report •

#5
February 7, 2012 at 10:55:16
I wasn't saying that "search string not found" (#VALUE) isn't an error. I agree that as far as Excel/SEARCH is concerned, it is an error.

My point was that an error caused by a search string not being found doesn't make the data invalid (your word).

"VBA isn't going to protect you from invalid data"

A perfectly valid piece of data might not be found in the string being searched. It's the not being found that produces the error, not the non-validity of the data.

"Ann" is a perfectly valid piece of data if I have a list of names.

=SEARCH("Ann","Bob Smith") is going to produce an error, but it doesn't make "Ann" any less valid as a piece of data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 7, 2012 at 11:29:03
And we come to the heart of the disagreement. I define invalid data as, "data which doesn't produce an error when entered into a valid function/equation." In your example, the combination of "Ann" and "Bob Smith" is invalid for SEARCH(). It would, however, be valid for CONCATENATE().

EDIT: English, how does it work?

How To Ask Questions The Smart Way


Report •

#7
February 7, 2012 at 13:26:16
And a friendly disagreement, I hope! ;-)

You said: "I define invalid data as, "data which doesn't produce an error when entered into a valid function/equation."

I assume you mean I define valid data as "data which doesn't produce an error .."

Let's try another example of why I disagree with that sentiment by using the ISNA function with VLOOKUP:

=IF(ISNA(VLOOKUP(A1,$B$1:$D$50,2,0)),A1,A1*VLOOKUP(A1,$B$1:$D$50,2,0))

If A1 isn't found in the lookup_array, then VLOOKUP will return a #N/A error.

Therefore, if I understand your side of it, you would consider the data in A1 to be invalid since it will produce an error when used with VLOOKUP.

However, the data in A1 is perfectly valid and if not found in the lookup_array, I want the formula to return the value in A1 for further processing.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
February 7, 2012 at 15:08:05
And a friendly disagreement, I hope! ;-)
I do too! As they say, life's too short to be angry at the Internet.


That function would consider the data valid. You accept that VLOOKUP might fail, and the function compensates for that possibility. Your function doesn't depend on VLOOKUP's success to be successful. Mine doesn't test, so the data in A1 would be invalid for my function.

Me: Number * #N/A = #N/A --> Data invalid!

=A1*VLOOKUP(A1,$B$1:$D$50,2,0)

You: Number = Number --> Data valid!
=IF(ISNA(VLOOKUP(A1,$B$1:$D$50,2,0)),A1,A1*VLOOKUP(A1,$B$1:$D$50,2,0))

How To Ask Questions The Smart Way


Report •

#9
February 7, 2012 at 15:51:10
OK, now I think we have really come to the heart of the disagreement.

We need to define Function vs. Formula.

If you Google Function vs. Formula, you'll find that many of us define a Function as those built in pieces of magic inside Excel such as VLOOKUP, SUM, ISNA, IF, etc. Of course, let's not forget about User Defined Functions (UDF) written in VBA.

On the other hand, a Formula is that thing that you type into the Formula Bar of a spreadsheet that (hopefully) Excel can evaluate. A Formula may or may not contain one or more functions.

e.g. All three of these are Formulas, but only 2 of them contain Functions:

=SUM(A1:A4)

=A1+A2+A3+A4

=SUM(INDIRECT(CONCATENATE("A",1,":","A",4)))

So, to use your definition of "valid data" as something that does not produce an error when used in a Function, you would have to consider my data (A1) invalid since it will produce an error in the VLOOKUP Function, although it won't produce an error in my Formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
February 7, 2012 at 16:19:41
I use both terms interchangeably as synonyms of procedure. But yes, I consider it invalid data for VLOOKKUP because it returns an error. It's still valid for your method.

How To Ask Questions The Smart Way


Report •

Ask Question