Why aren't these functions built into Excel? It would simplify SOOO many
spreadsheets:

'
' If the value is blank, return the blankvalue,
' otherwise return the value
'
Public Function IfBlank(ByVal value As Variant, ByVal blankValue As Variant)
As Variant
If IsEmpty(value) Or (value = "") Then
IfBlank = blankValue
Else
IfBlank = value
End If
End Function

'
' If the value is valid, return the value, otherwise just return blank
'
Public Function IfValid(ByVal value As Variant) As Variant
If IsError(value) Then
IfValid = ""
Else
IfValid = value
End If
End Function

'
' If the value is in error, return the other value
'
Public Function IfError(ByVal value As Variant, ByVal valueIfError As
Variant) As Variant
If IsError(value) Then
IfError = valueIfError
Else
IfError = value
End If
End Function


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

IFERROR is a new function for the next version of Excel

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Darren Oakey" <Darren wrote in message
...
Why aren't these functions built into Excel? It would simplify SOOO many
spreadsheets:

'
' If the value is blank, return the blankvalue,
' otherwise return the value
'
Public Function IfBlank(ByVal value As Variant, ByVal blankValue As
Variant)
As Variant
If IsEmpty(value) Or (value = "") Then
IfBlank = blankValue
Else
IfBlank = value
End If
End Function

'
' If the value is valid, return the value, otherwise just return blank
'
Public Function IfValid(ByVal value As Variant) As Variant
If IsError(value) Then
IfValid = ""
Else
IfValid = value
End If
End Function

'
' If the value is in error, return the other value
'
Public Function IfError(ByVal value As Variant, ByVal valueIfError As
Variant) As Variant
If IsError(value) Then
IfError = valueIfError
Else
IfError = value
End If
End Function


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I don't think I follow. What's wrong with

=IF(value="",blankvalue,value)
=IF(ISERR(value),"",value)
=IF(ISERR(value),valueiferror,value)

Or, in VBA using the IIF function?


"Darren Oakey" wrote:

Why aren't these functions built into Excel? It would simplify SOOO many
spreadsheets:

'
' If the value is blank, return the blankvalue,
' otherwise return the value
'
Public Function IfBlank(ByVal value As Variant, ByVal blankValue As Variant)
As Variant
If IsEmpty(value) Or (value = "") Then
IfBlank = blankValue
Else
IfBlank = value
End If
End Function

'
' If the value is valid, return the value, otherwise just return blank
'
Public Function IfValid(ByVal value As Variant) As Variant
If IsError(value) Then
IfValid = ""
Else
IfValid = value
End If
End Function

'
' If the value is in error, return the other value
'
Public Function IfError(ByVal value As Variant, ByVal valueIfError As
Variant) As Variant
If IsError(value) Then
IfError = valueIfError
Else
IfError = value
End If
End Function


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

For instance the IFERROR function works like this

=IFERROR(VLOOKUP(A2,B2:C50,2,0),"")

whereas now you need to use something like

=IF(ISNA(VLOOKUP(A2,B2:C50,2,0)),"",VLOOKUP(A2,B2: C50,2,0))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"JMB" wrote in message
...
I don't think I follow. What's wrong with

=IF(value="",blankvalue,value)
=IF(ISERR(value),"",value)
=IF(ISERR(value),valueiferror,value)

Or, in VBA using the IIF function?


"Darren Oakey" wrote:

Why aren't these functions built into Excel? It would simplify SOOO many
spreadsheets:

'
' If the value is blank, return the blankvalue,
' otherwise return the value
'
Public Function IfBlank(ByVal value As Variant, ByVal blankValue As
Variant)
As Variant
If IsEmpty(value) Or (value = "") Then
IfBlank = blankValue
Else
IfBlank = value
End If
End Function

'
' If the value is valid, return the value, otherwise just return blank
'
Public Function IfValid(ByVal value As Variant) As Variant
If IsError(value) Then
IfValid = ""
Else
IfValid = value
End If
End Function

'
' If the value is in error, return the other value
'
Public Function IfError(ByVal value As Variant, ByVal valueIfError As
Variant) As Variant
If IsError(value) Then
IfError = valueIfError
Else
IfError = value
End If
End Function


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

Ahhh, I see. That would be useful.

"Peo Sjoblom" wrote:

For instance the IFERROR function works like this

=IFERROR(VLOOKUP(A2,B2:C50,2,0),"")

whereas now you need to use something like

=IF(ISNA(VLOOKUP(A2,B2:C50,2,0)),"",VLOOKUP(A2,B2: C50,2,0))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"JMB" wrote in message
...
I don't think I follow. What's wrong with

=IF(value="",blankvalue,value)
=IF(ISERR(value),"",value)
=IF(ISERR(value),valueiferror,value)

Or, in VBA using the IIF function?


"Darren Oakey" wrote:

Why aren't these functions built into Excel? It would simplify SOOO many
spreadsheets:

'
' If the value is blank, return the blankvalue,
' otherwise return the value
'
Public Function IfBlank(ByVal value As Variant, ByVal blankValue As
Variant)
As Variant
If IsEmpty(value) Or (value = "") Then
IfBlank = blankValue
Else
IfBlank = value
End If
End Function

'
' If the value is valid, return the value, otherwise just return blank
'
Public Function IfValid(ByVal value As Variant) As Variant
If IsError(value) Then
IfValid = ""
Else
IfValid = value
End If
End Function

'
' If the value is in error, return the other value
'
Public Function IfError(ByVal value As Variant, ByVal valueIfError As
Variant) As Variant
If IsError(value) Then
IfError = valueIfError
Else
IfError = value
End If
End Function


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions