Dutch Gemini's Weblog Pages

February 17, 2010

Properly declare variable when working with Shapes.Range in Excel

Filed under: Excel,Programming,VBA — dutchgemini @ 12:39 pm

If you work with shapes and VBA, Excel provides a method called .Shapes.Range() with which you can manage an object or a collection called ShapeRange that represents a subset of the shapes in a Shapes collection.

From Excel help:

Although you can use the Range property to return any number of shapes, it's simpler to use the Item method if you only want to return a single member of the collection. For example, Shapes(1) is simpler than Shapes.Range(1).

To specify an array of integers or strings for Index, you can use the Array function. For example, the following instruction returns two shapes specified by name.

Code example:

Dim arShapes() As Variant
Dim objRange As Object
arShapes = Array("Oval 4", "Rectangle 5")
Set objRange = ActiveSheet.Shapes.Range(arShapes)

I tookover the idea and was developing a small routine that would select a variable number of shapes and finally group them together. Instead of using the Array function where I had to statically specify the elements, I elected to declare a Variant and ReDim it to allocate the names of the desired shapes and pass it to the .Range property.

Dim wShapes As Variant
Dim wShapeRange As Object
Dim wNumOfShapes As Long, i As Long

wNumOfShapes = (ActiveCell.Value - 1)   ' Just an example, i.e. the number of Rectangles
ReDim wShapes(wNumOfShapes) As Variant
For i = 0 To (wNumOfShapes - 1)
    wShapes(i) = "Rectangle " & (i + 1) ' Load the rectangles, these start with "1"
Next i
Set wShapeRange = ActiveSheet.Shapes.Range(wShapes) ' <----- Generates an error
wShapeRange.Group

The second last line generated an error. After some investigation I discovered that the wShapes array was not really an array, although it did look like one.
With a little modification in the declaration section the problem was resolved.

Dim wShapes() As Variant        ' <----- Initial declaration must include ()
Dim wShapeRange As Object
Dim wNumOfShapes As Long, i As Long

wNumOfShapes = (ActiveCell.Value - 1)   ' Just an example, i.e. the number of Rectangles
ReDim wShapes(wNumOfShapes) As Variant
For i = 0 To (wNumOfShapes - 1)
    wShapes(i) = "Rectangle " & (i + 1) ' Load the rectangles, these start with "1"
Next i
Set wShapeRange = ActiveSheet.Shapes.Range(wShapes) ' <----- This now works
wShapeRange.Group

I was not aware that omitting the brackets in the declaration of a Variant would have an impact of the code. Probably the array resulting from the ReDim is not exactly the same object returned by the Array function.

Lesson learned.

Dutch.

About these ads

1 Comment »

  1. Nice Lesson Man. It helped me alot.

    Comment by Pallav Raj — December 14, 2012 @ 5:03 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: