none
Excel host User Control issue RRS feed

  • Question

  • Hi,
    I created my own User Control for Excel environment, used Visual Studio 2015 Excel 2013 and 2016 VSTO Workbook Project...  the concept is to show animated text message (like in the stock market news).
    The way that I'm implemented the code is simple to add a new instance of label control at the run time to the main 'window messages' in which is the user control form. So far everything works fine. essentially the animation is a consequence of the timer work, each tick (10 msec) invoked a method that increment the left property of the label by 1. After added some more messages, the GC start to work intensively and everything was crashed…

    Snippet of the code that add the messages...

    Public Sub AddMessage(ByVal Message As String, Optional ByVal Priority As Priority = Priority.Normal)
            If Message <> Nothing Then
                Dim RollerMsg As New Label
                NumMsg += 1
                If NumMsg <> 1 Then QueueMng.Enqueue(NumMsg)
    
                With RollerMsg
                    If CType(Priority, Boolean) Then
                        .BackColor = Color.FromArgb(255, 192, 192)
                    Else
                        .BackColor = Color.White
                    End If
                    .AutoSize = True
                    .TextAlign = ContentAlignment.MiddleCenter
                    .Text = Message
                    .RightToLeft = RightToLeft.Yes
                    .Name = "RollerMsg" & NumMsg
                    .Font = IntFont
                End With
    
                Controls.Add(RollerMsg)
    
                Select Case IntDirection
                    Case 4
                        RollerMsg.Left = Size.Width
                    Case 6
                        RollerMsg.Left = -RollerMsg.Width
                End Select
    
                RollerMsg.Top = CType(Top + (Height - RollerMsg.Height) / 2, Integer)
                RollerMsg.BringToFront()
            End If
        End Sub


    • Edited by Refael1001 Saturday, July 23, 2016 8:47 PM
    Saturday, July 23, 2016 8:25 AM

All replies

  • Hi Refael,

    >> After added some more messages, the GC start to work intensively and everything was crashed

    What do you mean with this? Do you mean your Excel crashed or anything did not work? In my option, GC will collect garbage when there is no reference to it. If you are using the object, it will not be collected automatically.

    Based on your description, I don’t know how to reproduce your issue with your code. It would be helpful if you could share us your detailed steps to reproduce your issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, July 25, 2016 9:58 AM
  • Thanks Edward for your quick answer

    I mentioned The GC in regard to the intensive of operations behind the scenes, which seems to me suspicious. In the bottom line, after added a few (5-10) labels (represent text messages) the Excel application crashed – meaning, not with exception error (I run the app in debug mode), however all the ribbon icons and the moving labels started to smear, some of them disappeared immediately and nothing response after that...

    I just want to point that the labels are part of custom UserControl that I created and added to excel worksheet in run time (as host object). On the other hand, if I add the UserControl to Windows Form (not directly to the worksheet) and show the dialog later everything works fine and nothing happens.

    Tuesday, July 26, 2016 1:02 AM
  • Hi Refael,

    I am glad you have found a work around.

    If you insist on the issue adding label to excel worksheet in runtime, it would be helpful if you could share us a simple code which could reproduce your issue. Based on your description, I am not clear how to reproduce this issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, July 26, 2016 6:48 AM
  • Thanks again Edward,

    I prefer to understand what causes the issue. The following code (in shortly) with an explanation produces the labels on run time:

    'At first I added the UserControl (new untouched design) to the worksheet at run time.
    'Roller means the UserControl class name.
      Globals.Sheet1.Controls.AddControl(New Roller, 0, 2, 1000, 30, "Roller1")
    
    'Start is a method that starts the timer
      Globals.Sheet1.Controls("Roller1").start
    
    
      Public Sub Start()
      Timer1.Start()
      End Sub
    
    
    'after the control is added to the worksheet and the timer started, it’s where the AddMessage method comes in. 
    'the full code provided In the original post. The most important lines are those eventually produces the 
    'labels at the run time with in the UserControl (the Me keyword doesn't written because the Name can be simplified).
    
      Dim RollerMsg As New Label
    
      Controls.Add(RollerMsg)
    
    
    'the code line that moves the label in 1 step each tick
    Controls("RollerMsg" & x).Left -= 1
    


    Tuesday, July 26, 2016 8:59 AM
  • Hi Refael,

    With these not complete code, I am not able to reproduce your issue.

    >> Globals.Sheet1.Controls("Roller1").start

    How did this code run? Where did you add “Public Sub Start()”?

    >> Controls("RollerMsg" & x).Left -= 1

    Where did you define x?

    More detailed information is needed to reproduce your issue. And it would be helpful if you could share us your simple demo which could reproduce your issue through oneDrive.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, July 27, 2016 6:20 AM