IsMissing()

K

Kuzemdoo Dawuni

I had a question re: 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
 
T

Tom Ogilvy

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.
 
R

Ron Rosenfeld

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



I had a question re: 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top