Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Sep 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA code not working in VB (Excel 2002 and VB 6.0)

    I am writing VB code similar to following VBA code declaring appxl as Excel object.
    However VB does not identify msoDiagramPyramid (or for that matter any other diagram with a message unknow variable or type. Do I have to have any special reference to be used.

    Any tips will be very much helpful .

    Sub CreatePyramidDiagram()

    Dim dgnNode As DiagramNode
    Dim shpDiagram As Shape
    Dim intCount As Integer

    'Add pyramid diagram to current document
    Set shpDiagram = ActiveSheet.Shapes.AddDiagram( _
    Type:=msoDiagramPyramid, Left:=10, _
    Top:=15, Width:=400, Height:=475)

    'Add first diagram node child
    Set dgnNode = shpDiagram.DiagramNode.Children.AddNode

    'Add three more diagram child nodes.
    For intCount = 1 To 3
    dgnNode.AddNode
    Next intCount

    End Sub

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA code not working in VB (Excel 2002 and VB 6.0)

    Hi,
    You can replace msoDiagramPyramid with 4 and the code should work or you can set a reference to the Microsoft Office object library and the constants should then be recognised.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA code not working in VB (Excel 2002 and VB 6.0)

    You also need to prefix all of your objects with the library in which they reside. Your culprit is Shape, almost all of the libraries have one. Since you didn't specify, VB assumes that it is a VB shape, so...
    <pre>Dim dgnNode As Excel.DiagramNode
    Dim shpDiagram As Excel.Shape</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA code not working in VB (Excel 2002 and VB 6.0)

    Did Rory's and my suggestions fix your problem?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Lounger
    Join Date
    Sep 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code not working in VB (Excel 2002 and VB 6.0)

    I am sorry for delayed reply due to extreme work pressures . However both of your suggestions were very valuable .

    Rory's suggestion to check references and libraries worked . We could not try your suggestion to declare as EXcel.diagramnote ... due to the fact that checking out references worked and we could eliminate VBA and also due to the fact that there is Microsoft acknoweledged bug in the area of Diagram (which u pointed to me ) . I would like to check that also if I have some time free.

    So currently we are using VB code with word object (for diagram), doing processing internally and showing the results as Web page and XLS . This works fine(of course due to your voluntary suggestions).

    Now there is small problem. When the number of nodes are high (lets say 300) , the diagram does not re-size (which it does when the nodes are less say 20-25) . Any experience on this.

    Thanks in advance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •