IsMissing()

[Guest]

Hi all,
I have a problem that does not just want to execute.
I have a sub as follows

Sub OutputResult(optional InCell as Range)

if IsMissing(InCell)
InCell.Value = 123
end if

end sub

I call the is sub with

call OutputResult(Worksheets("output").Range("B1))

Everything works fine but the "if IsMissing(InCell)" statement is always not
executed whether or not I provide a range value.
Any help will be greatly appreciated
Kuze

 

[Ron Rosenfeld]

Sub OutputResult(optional InCell as Range)

if IsMissing(InCell)
InCell.Value = 123
end if

end sub

There is no THEN after IsMissing(InCell). I don't see how it executes at all.

Also, IF InCell has not been initialized, then how does InCell.Value get
executed?

Perhaps there's something else going on in other code.


--ron

 

[Guest]

Ron,
Thanks for the response. That is an omission on my part. There is a then on
the if statement line. Also you should realise that this is a part of a long
complicated code that I have curled out here.
With regards to InCell note that the subroutine is called with a valid
range. See the call statement.

 

[Ron Rosenfeld]

Ron,
Thanks for the response. That is an omission on my part. There is a then on
the if statement line. Also you should realise that this is a part of a long
complicated code that I have curled out here.
With regards to InCell note that the subroutine is called with a valid
range. See the call statement.

OK, but correcting the lack of a quote mark in your call statement, and running
"foo", the IF statement always executes.

==================
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
==========================

However, I am not sure if the Range data type has a Missing bit to be tested.
It seems to behave as if it does NOT. If that is the case, you may want to use
an IsNothing test.

But I still don't understand how you can set InCell.Value=123 if InCell is not
passed to the sub.


--ron