none
early binding and RANGE RRS feed

  • Question

  • I currently have

    Dim xlRange As Range
    xlRange = xlWs.Range("A1", "CV100")

    which works fine.

    I want to convert that to use numerical cell addresses and still use early binding.

    I tried

    Dim xlRange As Range
    Dim xlRange1 As Object = xlWs.Cells(1, 1)
    Dim xlRange2 As Object = xlWs.Cells(100, 100)
    xlRange = xlWs.Range(xlRange1, xlRange2)

    but when I use this code, Excel is left running in Task Manager.

    According to https://support.microsoft.com/en-us/kb/317109 this means that there is a reference that was created that I am not accounting for.

    How can I do this ?





    • Edited by IntenseNJ Wednesday, June 22, 2016 7:43 PM
    Wednesday, June 22, 2016 7:41 PM

Answers

  • Hi IntenseNJ,

    if you are writing the code in vb.net then also you can run the above code with some changes in syntax and it will work for vb.net also.

    the suggestion is also same that you need to set the objects to null after you finish your work.

    you are referring a correct link that you had mentioned in the original post.

    Follow the link and let us know it worked for you or not.

    Following are some external links that you can refer.

    How to properly release Excel COM objects

    The proper way to dispose Excel com object using VB.NET?

    Best Way to Release Excel Interop com ObjectDisclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    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.

    Friday, June 24, 2016 5:51 AM
    Moderator
  • Setting COM objects to Nothing (or even worse, calling Marshal.ReleaseComObject()) is not required at all. There is a lot of misinformation about this issue, including on MSDN and the links posted below.

    If you need to ensure that Excel quits after running your automation code, you can just force a garbage collection when you're done interacting with Excel.

    See my answers to this discussion: https://social.msdn.microsoft.com/Forums/office/en-US/e6faed91-32ff-46c4-b1d5-c9575c40b4a2/cannot-release-excelrange-object?forum=exceldev

    -Govert

    Excel-DNA - Free and easy .NET for Excel

    Wednesday, June 29, 2016 10:20 PM

All replies

  • Re:  range objects

    I doubt whether this is really working (it throws an error with VBA)

    Dim xlRange1 As Object = xlWs.Cells(1, 1)

    In any case set all objects to Nothing (a range is an object) before exiting the code.
    That should be done in child then parent order...
       Set xlRange = nothing
       Set xlWS = Nothing    
       xlWB.Close SaveChanges:=True    'your choice    
       Set xlWB = Nothing    
       xlApp.Quit    
       Set xlApp = Nothing    

    '---
    Jim Cone
    Portland, Oregon USA
    https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0


    • Edited by James Cone Wednesday, September 21, 2016 11:24 PM
    Thursday, June 23, 2016 12:55 AM
  • Hi IntenseNJ,

    yes as James Cone said you will get error on the following line.

    Dim xlRange1 As Object = xlWs.Cells(1, 1)
    Dim xlRange2 As Object = xlWs.Cells(100, 100)

    please visit the link below to know how you can set value in cell.

    The Complete Guide to Ranges and Cells in Excel VBA

    The other thing is that from your code its looks like you are developing VBA code.

    but you are referring the link is used for visual studio to automate the Excel.

    I am agree with James Cone you need to set all the objects to nothing after your work is completed.

    so make the changes according to suggestion and try to test it again. hope it solves your issue.

    if you still having a problem regarding your issue then please let us know so that we can provide you further suggestions.

    Regards

    Deepak

    Thursday, June 23, 2016 1:24 AM
    Moderator
  • I am writing in VB.net

    I hadn't realize that this forum is for VBA.

    Thursday, June 23, 2016 1:37 PM
  • Hi IntenseNJ,

    if you are writing the code in vb.net then also you can run the above code with some changes in syntax and it will work for vb.net also.

    the suggestion is also same that you need to set the objects to null after you finish your work.

    you are referring a correct link that you had mentioned in the original post.

    Follow the link and let us know it worked for you or not.

    Following are some external links that you can refer.

    How to properly release Excel COM objects

    The proper way to dispose Excel com object using VB.NET?

    Best Way to Release Excel Interop com ObjectDisclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    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.

    Friday, June 24, 2016 5:51 AM
    Moderator
  • Setting COM objects to Nothing (or even worse, calling Marshal.ReleaseComObject()) is not required at all. There is a lot of misinformation about this issue, including on MSDN and the links posted below.

    If you need to ensure that Excel quits after running your automation code, you can just force a garbage collection when you're done interacting with Excel.

    See my answers to this discussion: https://social.msdn.microsoft.com/Forums/office/en-US/e6faed91-32ff-46c4-b1d5-c9575c40b4a2/cannot-release-excelrange-object?forum=exceldev

    -Govert

    Excel-DNA - Free and easy .NET for Excel

    Wednesday, June 29, 2016 10:20 PM