none
How to set referenced variable to nothing RRS feed

  • Question

  • I would like to set some variables to nothing.

    I do not want to repeat code, so I would like to add each var to an array and use the same routine to release all vars

      Public Sub DesconectarVars()
            Dim Objetos As Object() = {ExcelWorksheet, ExcelWorkbook, ExcelApp}
            For Each Objeto In Objetos
                Marshal.ReleaseComObject(Objeto)    
                Objeto = Nothing
            Next Objeto
        End Sub

    The problem is that when I write "Objeto = nothing", Objeto is set to nothing, and not the referenced variable (I want to set ExcelApp=Nothing, not Objeto=Nothing).

    There is any way to do it?

    Tuesday, February 20, 2018 1:50 PM

All replies

  • The problem is that it does nothing. 

    VB is since version 7 an OOP program language, so what you do with setting it to nothing the address becomes empty. 

    Therefore have you a reason you want to do it, in the current method it has AFAIK no sense. 

    (In 1998 there was a bug I thought in SP5 from VB6 and then became the myth that everything had to be set to nothing, it never went away although SP6 solved the bug).

    .Net means managed program language, all things around releasing memory is done by .Net. However, some want to do it themselves and than lock it up instead of releasing it. 

    Therefore, what does not go as it in your opinion has to go? (For instance comes there an out of memory message and shuts the computer down?)


    Success
    Cor

    Tuesday, February 20, 2018 2:23 PM
  • High level, Excel can be a challenge to release objects even when you think you've done it correctly.

    For example, the order you create and dispose of objects can make a difference in if they dispose properly. If you use more than one '.' to reference an object with objects for Excel that can make or break disposal of an object.

    If you look at the following (Module1.vb) code sample note how I dispose of objects.

    The following is in C# (I wrote this) that is relevant in going a bit farther to dispose of Excel objects.  As is can not be translated via a translator as it uses local functions yet by removal of the local function to a private function it can be translated.

    No matter which code samples above are looked at, they all dispose of all objects properly.

    For a really exploratory example see the following vb.net code sample


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, February 20, 2018 3:16 PM
    Moderator
  • Well did you create the objects somwhere else? That would be why they are not set to nothing. Maybe the below untested code would be more inline with what u want. Create New excel objects for adding to a list. Clear the list to dispose of objects.

    Dim Temp As New List(Of Object) Temp = {New ExcelWorksheet, New ExcelWorkbook, New ExcelApp}.ToList Temp.Clear

    Or insutead of clearing all the objects

    For i = 0 To Temp.Count - 1

    Temp(i) = Nothing or Temp(i).Dispose

    Next



    La vida loca

    Tuesday, February 20, 2018 3:34 PM
  • Well did you create the objects somewhere else? 

    yes
    Wednesday, February 21, 2018 1:11 PM
  • High level, Excel can be a challenge to release objects even when you think you've done it correctly.

    The problem is that if I create too many variables referencing excel objects, I need to repeat, for each one, the FinalReleaseComObject and set to Nothing. (Sorry for the font change. There seems to not exist a way to control formatting here on this editor)

    So I want to make a routine that does if for all of them.

    Wednesday, February 21, 2018 1:19 PM
  • Hi lixava,

    Do you try  Mr. Monkeyboy's post? It can resolved your issue now? Or you can share your solution here.

    Thanks for your understanding.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 28, 2018 8:16 AM
    Moderator
  • The only way to do this would be to add each Excel object reference to a Collection or List after it has been created. Is this what you are doing? If not, there is no other way to destroy them other than to do so directly.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, February 28, 2018 2:43 PM
  • High level, Excel can be a challenge to release objects even when you think you've done it correctly.

    The problem is that if I create too many variables referencing excel objects, I need to repeat, for each one, the FinalReleaseComObject and set to Nothing. (Sorry for the font change. There seems to not exist a way to control formatting here on this editor)

    So I want to make a routine that does if for all of them.

    I don't have a vb.net version and no time to convert but this one I did in C# can provide a pattern to work from.

    public void ReleaseObjects(List<object> pAnnihilationList)
    {
    
        for (var indexer = 0; indexer < pAnnihilationList.Count; indexer++)
        {
            try
            {
                if (pAnnihilationList[indexer] != null)
                {
                    Marshal.ReleaseComObject(pAnnihilationList[indexer]);
                    pAnnihilationList[indexer] = null;
                }
            }
            catch (Exception)
            {
                pAnnihilationList[indexer] = null;
            }
        }
    }

    I create the list for holding the objects e.g.

    var annihilationList = new List<object>();

    Then add objects to the list

    xlWorkBooks = xlApp.Workbooks;
    annihilationList.Add(xlWorkBooks);
    
    xlWorkBook = xlWorkBooks.Open(pFileName);
    annihilationList.Add(xlWorkBook);
    
    xlApp.Visible = false;
    
    xlWorkSheets = xlWorkBook.Sheets;
    annihilationList.Add(xlWorkSheets);

    Sorry that it had to be in C# yet I'm only showing a pattern to work from.

    Last note, the object should be added as last used from to first created.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, February 28, 2018 3:13 PM
    Moderator
  • Okay, here is a vb.net version of what I was talking about in my last reply but showed it in C#.

    There are two support classes, one is not used but can easily be used which is the Exception class while the other class is responsible for removal of com objects for 90 percent of the disposal operations. Of course for some will appear overkill yet that is all dependent on the size of the app and Excel operations, here i't a simple demo.

    https://1drv.ms/u/s!AtGAgKKpqdWjiXUZl8bmMp46Kwjl


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 1, 2018 12:58 AM
    Moderator
  • Try this too:

    Public Sub DesconectarVars()
       DesconectarVar(ExcelWorksheet)
       DesconectarVar(ExcelWorkbook)
       DesconectarVar(ExcelApp)
    End Sub
    
    Public Sub DesconectarVar(Of T)(ByRef objeto As T)
       Marshal.ReleaseComObject(objeto)
       objeto = Nothing
    End Sub
    

    • Proposed as answer by Mr. Monkeyboy Saturday, March 3, 2018 4:10 AM
    Thursday, March 1, 2018 7:35 AM