How to return an emty Range

[Guest]

H
Is it possible to return an "Empty range"? If so, how do you do

If (strRanges <> "") The
strRanges = Mid(strRanges, 2
Set GetAllCellsWithName = i_wsCellsInWorksheet.Range(strRanges
Els
'Return an empty Range ???
Set GetAllCellsWithName = i_wsCellsInWorksheet.Range(""
End I

Regard
/Niklas

 

[onedaywhen]

Remember a Range object is a notional entity which models reality.
What do you think an empty range looks like, how would you select one
using your mouse? You can't of course, because reality dictates that a
Range consists of at least one cell.

A similar problem exists for dates e.g. how do you return a null date?
The value zero in Excel VBA is a valid date (i.e. 30-Dec-1899), even
negative numbers are valid (e.g. -ve 999 is 05-Apr-1897). The usual
solution is to use a known date to signify a null value, preferably
one which is implausible in the context of your app. For example, in a
recent project here we used Newton's birthday to signify a null,
however I wouldn't recommend this because of a double ambiguity (01/04
or 04/01 depending on your locality or 25/12 depending on calendar
system used!) but I digress...

In short, assuming IV65536 is unlikely to arise in normal operation of
your app, what about:

Private Const NULL_RANGE_ADDRESS As String = "IV65536"
....
'Return 'null' Range
Set GetAllCellsWithName =
i_wsCellsInWorksheet.Range(NULL_RANGE_ADDRESS)

 

[JE McGimpsey]

one way:

If strRanges <> "" Then
On Error Resume Next
Set GetAllCellsWithName = _
i_wsCellsInWorksheet.Range(Mid(strRanges, 2))
On Error GoTo 0
End If

You can then test for an "empty" range with

If GetAllCellsWithName Is Nothing Then
'Range is empty
Else
'Do something
End if

 

[Guest]

Thank you. I will return Nothing
Regard
/Nikla

----- onedaywhen wrote: ----

Remember a Range object is a notional entity which models reality
What do you think an empty range looks like, how would you select on
using your mouse? You can't of course, because reality dictates that
Range consists of at least one cell

A similar problem exists for dates e.g. how do you return a null date
The value zero in Excel VBA is a valid date (i.e. 30-Dec-1899), eve
negative numbers are valid (e.g. -ve 999 is 05-Apr-1897). The usua
solution is to use a known date to signify a null value, preferabl
one which is implausible in the context of your app. For example, in
recent project here we used Newton's birthday to signify a null
however I wouldn't recommend this because of a double ambiguity (01/0
or 04/01 depending on your locality or 25/12 depending on calenda
system used!) but I digress..

In short, assuming IV65536 is unlikely to arise in normal operation o
your app, what about

Private Const NULL_RANGE_ADDRESS As String = "IV65536
...
'Return 'null' Rang
Set GetAllCellsWithName
i_wsCellsInWorksheet.Range(NULL_RANGE_ADDRESS

-