CODEWITHSUNDEEP
vbaexcel
BruceWayne
BruceWayne

Reputation: 23283

Excel VBA - "Type Mismatch" on second SET shape

I have a small macro, but get a Type 13 mismatch (run-time error 13) on only the second 'Set shape' line. I get no errors on the first one, but can't figure why I'm getting an error on the second. AFAIK it's doing the exact same thing?

Sub myMacro()
Dim mapWS, dataWS As Worksheet
Dim fromX, fromY, toX, toY As Integer
Dim fromShape, toShape As Shape

Set mapWS = Sheets("World Map")
Set dataWS = Sheets("Data")


Dim shapeNames(2, 2) As String
shapeNames(0, 0) = "USA"
shapeNames(0, 1) = "USA2"
shapeNames(1, 0) = "Germany"
shapeNames(1, 1) = "DEU"

i = 0
Set fromShape = mapWS.Shapes.Range(Array(shapeNames(i, 1)))
Set toShape = mapWS.Shapes.Range(Array(shapeNames(i + 1, 1)))
...
End Sub

The error occurs when at the second line ("Set toShape = mapWS..."). Why does it occur there - or the inverse, why doesn't it occur on the first one? Can you only set one shape range at a time?

Thanks for any help or any ideas!

Upvotes: 0

Views: 1889

Answers (2)

Rory
Rory

Reputation: 34055

Since you actually want Shape objects, it would, IMO, be better to use them:

Dim fromShape As Shape, toShape As Shape
...

i = 0
Set fromShape = mapWS.Shapes(shapeNames(i, 1))
Set toShape = mapWS.Shapes(shapeNames(i + 1, 1))

Upvotes: 0

Rubik
Rubik

Reputation: 1471

Beware, with the syntax below, in VBA, your first object fromShape is variant, and the second one is Shape:

Dim fromShape, toShape As Shape

If you want both Shape, you have to write: Dim fromShape As Shape, toShape As Shape

So, in your first line:

Set fromShape = mapWS.Shapes.Range(Array(shapeNames(i, 1)))

You affect the result of a Range object to a variant. That's fine.

But in your second line:

Set toShape = mapWS.Shapes.Range(Array(shapeNames(i + 1, 1)))

You affect the result of a Range object to a Shape object... and then "Type Mismatch".

You'd rather let VBA decide and then declare both variables as Variant:

Dim fromShape, toShape

Thanks to Rory comment, if you want true typed variable, you'll have to use:

Dim fromShape As ShapeRange, toShape As ShapeRange

Upvotes: 2

Related Questions