Creating a rectangle on an Excel sheet from VB

[Guest]

I am trying to create a rectangle on an Excel sheet witht the
following code. It fails with: "the specified value is out of range"
I am running VB6 and Excel 2000 on a Windows XP pro PC.

Thanks, Yagil
===
Dim MyXl As Excel.Application

Sub main()
Dim uiSheet1 As Worksheet
Dim uiRectangle1 As Shape

Set MyXl = CreateObject("excel.application")
MyXl.Workbooks.Add
Set uiSheet1 = Sheets.Add


Set uiRectangle1 = uiSheet1.Shapes.AddShape(msoShapeRectangle, _
10, 10, 100, 100) 'error: the specified value is out of range

End Sub

 

[Tom Ogilvy]

Set uiRectangle1 = uiSheet1.Shapes.AddShape(1, _
10, 10, 100, 100) 'error: the specified value is out of range

Replace
msoShapeRectangle

with the value 1 since you apparently don't have a reference to the library
that defines msoShapeRectangle. (the office library)

 

[Guest]

Thanks for the prompt answer.

I did have in my References: Microsoft Excel9.0 Object Library

When I now replaced msoShapeRectangle with 1, I get the error:
Type mismatch

 

[Guest]

I kept playing with the code, and (after adding the Office Library)
I got the following statement to work:

uiSheet1.Shapes.AddShape msoShapeRectangle, 10, 10, 100, 100

Since AddShape is supposed to return a Shape, I wonder why
setting uiRectangle1 caused an error.
By declaring uiRectangle1 as a Variant, I managed to run the statement:

Set uiRectangle1 = uiSheet1.Shapes.AddShape(msoShapeRectangle, 10, 10,
100, 100)

Go figure...

 

[Tom Ogilvy]

Sub Tester1()
Dim uiSheet1 As Worksheet
Dim uiRectangle1 As Variant
Set uiSheet1 = ActiveSheet
Set uiRectangle1 = uiSheet1.Shapes.AddShape(1, _
10, 10, 100, 100)
Debug.Print TypeName(uiRectangle1)
End Sub


worked fine for me. ( returned Shape)

msoShapeRectangle isn't defined in the Excel object library. Notice I said
**Office** library.


Maybe you need to dimension uiRectangle1 as Excel.Shape. Does VB6 have a
"Shape" object?

 

[Guest]

Using "Dim uiRectangle1 As excel.Shape" did the trick.

Thanks so much.