I had a question IsMissing Function and Ron Rosenfeld assisted on this.
He provided the following streamline of the code I was trying to run
==================
Sub foo()
Call OutputResult(Worksheets("output").Range("B1"))
End Sub
-------------------------
Sub OutputResult(Optional InCell As Range)

If IsMissing(InCell) Then
InCell.Value = 123
End If

End Sub
==========================
The problem I am encountering is that whether or not the call subroutine
call provides the optional range argument, the if IsMissing statement is
skipped Since the IsMissing function is passed a variant data type I am
wondering if a range is considered a variant. Is that the reason the
IsMissing() statement is always evaluating to TRUE.
Actually the statement I want to use is
--------------------------
If NOT IsMissing(InCell) Then
InCell.Value = 123
End If

so that when no range is passed the InCell.Value Statement is not executed.
Any assistance to get over this will be greatly appreciated.
Kuze

Use this instead

If Not InCell Is Nothing then


End I

--
Nick
-----------------------------------------------------------------------
Nicke's Profile: http://www.excelforum.com/member.php...nfo&userid=293
View this thread: http://www.excelforum.com/showthread.php?threadid=26959

A range isn't a variant

Sub OutputResult(Optional InCell As Variant)

If Not IsMissing(InCell) Then
InCell.Value = 123
End If

End Sub

worked for me.

--
Regards,
Tom Ogilvy

"Kuzemdoo Dawuni" wrote in
message ...
I had a question IsMissing Function and Ron Rosenfeld assisted on
this.
He provided the following streamline of the code I was trying to run
==================
Sub foo()
Call OutputResult(Worksheets("output").Range("B1"))
End Sub
-------------------------
Sub OutputResult(Optional InCell As Range)

If IsMissing(InCell) Then
InCell.Value = 123
End If

End Sub
==========================
The problem I am encountering is that whether or not the call subroutine
call provides the optional range argument, the if IsMissing statement is
skipped Since the IsMissing function is passed a variant data type I am
wondering if a range is considered a variant. Is that the reason the
IsMissing() statement is always evaluating to TRUE.
Actually the statement I want to use is
--------------------------
If NOT IsMissing(InCell) Then
InCell.Value = 123
End If

so that when no range is passed the InCell.Value Statement is not
executed.
Any assistance to get over this will be greatly appreciated.
Kuze

Did you try the Is Nothing test I suggested in my last post?



On Fri, 15 Oct 2004 08:19:09 -0700, Kuzemdoo Dawuni
wrote:

I had a question IsMissing Function and Ron Rosenfeld assisted on this.
He provided the following streamline of the code I was trying to run
==================
Sub foo()
Call OutputResult(Worksheets("output").Range("B1"))
End Sub
-------------------------
Sub OutputResult(Optional InCell As Range)

If IsMissing(InCell) Then
InCell.Value = 123
End If

End Sub
==========================
The problem I am encountering is that whether or not the call subroutine
call provides the optional range argument, the if IsMissing statement is
skipped Since the IsMissing function is passed a variant data type I am
wondering if a range is considered a variant. Is that the reason the
IsMissing() statement is always evaluating to TRUE.
Actually the statement I want to use is
--------------------------
If NOT IsMissing(InCell) Then
InCell.Value = 123
End If

so that when no range is passed the InCell.Value Statement is not executed.
Any assistance to get over this will be greatly appreciated.
Kuze

--ron