distinguish Range and "Single"

[Dietmar M. Kehrmann]

Hi,

I have to do different things ([BLOCK 1] and [BLOCK 2]) if Z is a
"Single" (a number or a Range of 1x1) or if Z is a Range (a genuine, not
1x1). My workaround is like

If TypeOf Z Is Range Then
If (Z.Rows.Count=1) And (Z.Columns.Count=1) Then
[Block 1]
Else
[Block 2]
End If
Else
[Block 1]
End If


but I don't like it: [Block 1] two times and the construction
'(Z.Rows.Count=1) And (Z.Columns.Count=1)' seems to be ugly.

I guess that this problem appears frequently.
What would be a good solution?

 

[Tom Hutchins]

You can just use Z.Count to count the cells in range Z.

Hope this helps,

Hutch

 

[Jon Peltier]

Probably better to use Z.Cells.Count, rather than rely on the default.
Especially since there's no visible cue that Z may refer to a range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

You can just use Z.Count to count the cells in range Z.

Hope this helps,

Hutch

Hi,

I have to do different things ([BLOCK 1] and [BLOCK 2]) if Z is a
"Single" (a number or a Range of 1x1) or if Z is a Range (a genuine, not
1x1). My workaround is like

If TypeOf Z Is Range Then
If (Z.Rows.Count=1) And (Z.Columns.Count=1) Then
[Block 1]
Else
[Block 2]
End If
Else
[Block 1]
End If


but I don't like it: [Block 1] two times and the construction
'(Z.Rows.Count=1) And (Z.Columns.Count=1)' seems to be ugly.

I guess that this problem appears frequently.
What would be a good solution?

 

[Dietmar M. Kehrmann]

If (TypeOf Z Is Range) And (Z.Cells.Count>1) Then
[Block 2]
Else
[Block 1]
End Fi

does not work if Z is a number (like 0,5). It works with [Block 2] for a
genuine Range (not of 1x1) and with [Block 1] for a 1x1-Range, but no for
numbers with [Block 1].

Why not?

 

[Rick Rothstein \(MVP - VB\)]

I think your problem has to do with the fact that VBA does not short-circuit
its logical evaluations. So, even if 'TypeOf Z Is Range' evaluates to
False, VB will still evaluate the 'Z.Cells.Count>1' expression which, of
course, produces an error. Try your code this way...

If TypeOf Z Is Range Then
If Z.Cells.Count > 1 Then
[Block 2]
Else
[Block 1]
End If
End If

Rick

 

[Jon Peltier]

Aside from changing

(Z.Rows.Count=1) And (Z.Columns.Count=1)

to

Z.Cells.Count

I don't see anything wrong with the construction. It's how VBA works.

- Jon

 

[Tim Williams]

Aside from changing

(Z.Rows.Count=1) And (Z.Columns.Count=1)

to

Z.Cells.Count

....particularly if there's any chance the range is not contiguous.

Tim

 

[Dietmar M. Kehrmann]

But I need

If TypeOf Z Is Range Then
If Z.Cells.Count > 1 Then
[Block 2]
Else
[Block 1]
End If
Else
[Block 1]
End If

and then [Block 1] appears two times. Ugly ^^

 

[Rob Bovey]

Hi Dietmar,

You can perform both tests simultaneously like so:

Dim bIsMultiCellRange As Boolean

On Error Resume Next
bIsMultiCellRange = (TypeOf Z Is Range) And (Z.Cells.Count > 1)
On Error GoTo 0

If bIsMultiCellRange Then
[Block 2]
Else
[Block 1]
End If

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Rick Rothstein \(MVP - VB\)]

If [Block1] is more than a few lines, I would put its code in a subroutine
and then call that subroutine from this code...

......
......
If TypeOf Z Is Range Then
If Z.Cells.Count > 1 Then
[Block 2]
Else
Call Block1
End If
Else
Call Block1
End If
.......
.......
End Sub|Function

Sub Block1()
<<code>>
End Sub

Include an appropriate argument list if the Block1 subroutine is dependent
on local variables.

Rick