Proxy Remote Object?

Proxy Remote Object?

• Nicolai

  Hi Misha,
  
  Basically, I'm using the approach from Paul and invoke the managed code method from UDF by using VBA, and then, the method in my ComVisibile class will be triggered and the corresponding method will assign a .Value2 to a cell (or a couple of cells). However, the .Value2 property failed now..

  Details:

  In my excel workbook, I added a Module in VBA containing:

  Dim m As Object

  Public Sub RegisterCallback(callback As Object)
      Set m = callback
  End Sub

  Then, in my c# managed code:

      [System.Runtime.InteropServices.ComVisible(true)]
      public class MyClass {

  ..

    public MethodName {

              ws = (Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet;

              ((Excel.Range)ws.Cells[3, 1]).Value2 = "123";
  

  }

  }
  
  
  If you need more information, I can mail the code file to you directly.
  
  Thanks!
  

  
  

• dmcnulty

  ah... Sorry to hear that, is it by design or we can make it work programmatically now in VSTO BTW, how can the sample work as shown in my prevoius post It just like 'inject' a block of cell into the ActiveCell after querying the database...
  
  Or do you have any other approach in doing this
  
  Thanks,
  
  

• CavemanUK

  Hey Peter,

  From what I have read, Excel just doesn't allow UDFs to modfy cell attributes (except of course the cell being edited).

  Most likely there is no reasonable workaround, but the best place to ask this question would be the Excel programming newsgroup:

  http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.excel.programming&lang=en&cr=US

  
  
  

• Tom Minka

  I still need more information to understand your scenario.

  What invokes methods on your ComVisible class Is it a UDF method Then what kind of UDFs are you using: automation add-ins our XLLs

  
  
  

• spike76

  Yes. There are other ways of calling into managed code from VBA that allow the managed code to make calls on the Office OM.

  I outline one solution below. The VBA function that makes the callback to the managed object doesn't have to be called as a UDF. It can simply be called from (1) other VBA code in the document or (2) as "OnAction" macro for an Office control  such as a commandbarbutton.

  In this situation, you are free to make calls on the Excel OM from the managed method. And indeed it seems to work.

  VBA code (in a new module in the VBA project):

  Dim managedObject As Object
  Dim cbButton As CommandBarButton

  Public Sub RegisterCallback(callback As Object)
      Set managedObject = callback
     
      '' Add command bar button that calls CallVSTOFunction macro
      Set cbButton = Application.CommandBars("Standard").Controls.Add(MsoControlType.msoControlButton, , , , True)
      cbButton.Caption = "Test"
      cbButton.Style = msoButtonCaption
      cbButton.Visible = True
      cbButton.OnAction = "CallVSTOFunction"
  End Sub

  Public Function CallVSTOFunction() As Integer
      CallVSTOFunction = managedObject.SetCellText()
  End Function

  C# code in VSTO Excel project Sheet1.cs:
  public partial class Sheet1
  {
      SomeClass callbackObj = null;
      private void Sheet1_Startup(object sender, System.EventArgs e)
      {
          callbackObj = new SomeClass();
          this.Application.Run("RegisterCallback", callbackObj, missing,  /* 29 other missings! */ );
      }
  }

  [System.Runtime.InteropServices.ComVisible(true)]
  public class SomeClass
  {
      public int SetCellText()
      {
          Excel.Worksheet wsActive = (Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet;
          Excel.Range firstCell = (Excel.Range)wsActive.Cells[1, 1];
          firstCell.Value2 = "Test";
          return 0;
      }
  }

  
  
  

• Joey Bradshaw

  Hi Misha,
  
  Thanks for your verification!
  
  You're right, I missed to post a line of code:
  [System.Runtime.InteropServices.ComVisible(true)]
  
  I'd added ComVisible at the top of this class file, because I want to use it in a UDF written in VSTO.
  
  Thanks,
  
  

• Peter Goode

  Hi Misha,
  
  I've found one more strange problem:
  
  I originally think that's it's a kind of 'threading' problem... because I can't assign a value to the 'hot thread' of the ThisWorkbook class from a [ComVisible]Class file. However, I just found that I can successfully run this line of code:
  ((Excel.Range)wsActive.Cells[1, 1]).AddComment("Test");
  
  So the problem now, more specificially, is in the .Value2 method. Do you have any idea
  
  Thanks,
  
  
  

• bizbuz

  Hi,
  
  Thanks for your answer at first, I'm actually used the same technique in my VSTO solution, but the problem that I face now is I can't assign a value to "other" cell in my C# method (which is trigger by a UDF written in VBA in fact).
  
  Do you know what's the resolution
  
  Thanks,
  
  

• rtnews0423

  Hi Misha,

  Before I pull all of my hair out, I googled a thread about assigning value in UDF:

  UDF (no matter written in VBA or indirectly invoke the C# method) can only return "a" single value to the calling cell, but can't assign value to any other cell on the active worksheet within the UDF... what do you think

  (But I did see an example that returning a matrix or assign value to other cell, how can I do the same in C# + VSTO )

  Thanks,

  (Update: An error code for this error from google, that is: 1004 Application Defined or Object Defined Error)

  
  

• Ahmad_ADSC

  That is correct - UDFs are not supposed to talk to the OM - I think Excel just blocks calls when you attempt to set values from UDF. Same thing repros if you try to change value of a cell in VBA code.
  
  

• MatthewRWatson

  Exact same code works fine for me in the simplest case:

  1. I created VSTO Excel workbook project

  2. Added a button to the ActionsPane and an event handler for button click

  3. Event handler performs the exact 2 lines as in your snippet.

  Is there anything besides this that would repro the problem you are seeing

  
  
  
• Proxy Remote Object?