How a function can return "Nothing" or "Empty"



Hello all,
I have a function that return certain value depending on calculations.
In certain conditions I would want to return a value so that receiving cell
behaves as if I would press Del-key, this is nothing is displayed and content
is empty.
I tried returning Null, but 0 is displayed in this case.
I tried returning "", but conditional formatting of the cell understand that
this is a value and then gives an undesired format.
Do anybody of you know how to solve this?
Any idea or suggestion will be welcome.
Thanks in advance and kind regards,

(You can see here the function:

Public Function LastValue(iRowTitle As Long, _
FirstCell As Range, _
BlockSize As Long, ColInBlock As Long,
ColInBlock2 As Long) As Variant
Dim WS As Excel.Worksheet
Dim ThereIsData As Boolean
Dim i As Long
Dim RGBlock As Excel.Range
LastValue = Null '<<<<<<<========================= Value returned
when I cannot calculate the result

Set WS = FirstCell.Parent
ThereIsData = True
i = FirstCell.Column
Set RGBlock = Range(WS.Cells(FirstCell.Row, FirstCell.Column),
WS.Cells(FirstCell.Row, FirstCell.Column + BlockSize - 1))
If WS.Cells(iRowTitle, RGBlock.Column) = "" Then ThereIsData = False

Do While ThereIsData
If RGBlock.Cells(1, ColInBlock) <> "" Then
LastValue = RGBlock.Cells(1, ColInBlock)
ElseIf RGBlock.Cells(1, ColInBlock2) <> "" Then
LastValue = RGBlock.Cells(1, ColInBlock2)
End If
Set RGBlock = Range(WS.Cells(RGBlock.Row, RGBlock.Column + BlockSize),
WS.Cells(RGBlock.Row, RGBlock.Column + BlockSize + BlockSize - 1))
If WS.Cells(iRowTitle, RGBlock.Column) = "" Then ThereIsData = False

Exit Function

End Function

Dave Peterson

Excel doesn't have the equivalent of a Null.

Maybe you could change your conditional formatting rules or even return a unique
string and use that.


This is what I thougth. It is a pity but....
Thanks for your answer.
Kind regards,

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