title: Edit Value In Excel Range
body: <p>I have an excel sheet with values in a range which I want to perform some calculations on in vb.net. I pass that range to vb.net using COM. When I try editing the values of the range, nothing happens. </p>
  
  <p>My question seems to be very similar to these questions, but I can't quite figure out what step I am missing.</p>
  
  <p><a href="https://stackoverflow.com/questions/17854317/how-to-edit-cell-value-in-vb-net-using-interop-excel">How to edit cell value in VB.net - Using .Interop.Excel</a>
  <a href="https://stackoverflow.com/questions/7266012/vb-net-office-solution-accessing-value-in-named-range-in-a-worksheet">VB.net Office Solution - Accessing value in named Range in a Worksheet</a></p>
  
  <p>VBA CODE:</p>
  
  <pre><code>Function MyTestRange (Byref myrng as range)
  
     Set classLib = New VBProject.CClass
     MyTestRange = classLib.MyTestRange(myrng)
  
  End Function
  </code></pre>
  
  <p>VB.NET code</p>
  
  <pre><code>Imports Microsoft.Office.Interop.Excel
  
  Public Class CClass
      Function MyTestRange(ByRef myrng As Range) As Double
       Dim newrng As Range
       Dim b As Integer = myrng.Rows.Count
       Dim i As Integer
  
       newrng = myrng
       For i = 1 To b
           newrng.Value2(i, 1) = myrng.Value2(i, 1) + 1
       Next i
       MyTestRange = newrng.Value2(1, 1)
      End Function
  End class
  </code></pre>
  
  <p>While this code doesn't generate an error, it doesn't change the values in newrng.</p>
  
  <p>Edit:</p>
  
  <p>I have tried many iterations based on the link provide, but always get the same error :</p>
  
  <p>An exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll but was not handled in user code</p>
  
  <p>Additional information: Exception from HRESULT: 0x800A03EC</p>
  
  <p>This is the simplest way to generate the error:</p>
  
  <pre><code>    Dim c As Object
  
      c = myrng.Value
      myrng.Value = c
  </code></pre>
  
  <p>Hence amending the values to something new, seems out of reach!</p>
  
  <p>Edit 2:</p>
  
  <p>After chatting with user Mat's Mug, I tried to edit a value in a range, purely in VBA.</p>
  
  <p>This sub works perfectly:</p>
  
  <pre><code>Sub rangesub()
   Dim example As Range
   Set example = Range("A1:A4")
   example.Value = Application.Transpose(Array(1, 2, 3, 4))
  End Sub
  </code></pre>
  
  <p>Excel functions also seem able to handle passing ranges:</p>
  
  <pre><code>Function SimpleCopyRange(myrng)
   SimpleCopyRange = myrng
  End Function
  </code></pre>
  
  <p>A very simple combination of this code works:</p>
  
  <pre><code>Function EditRange(myrng)
   Dim example As Range
   Set example = Range("A1:A4")
   EditRange = example
  End Function
  </code></pre>
  
  <p>However there is an unspecified error if I try to edit the range:</p>
  
  <pre><code>Function EditRange(myrng)
   Dim example As Range
   Set example = Range("A1:A4")
   example.Value = Application.Transpose(Array(1, 2, 3, 4))
   EditRange = example
  End Function
  </code></pre>
  
  <p>The application.transpose part obviously adds a layer of complexity, and can be replaced with example.Value = 8 without any changes in results.</p>