Set value of cell in add in

Question

I will explain everything that I have found but in short I just want to set the value of cell A1 in code behind. I have tried Create Excel Add-in - get cell value plus other links and all those techniques work only if I run them on a macro but I want execute them from a function.

So let me start explaining:

I do not understand why I get a wierd behaviour when I run the same code on a function versus a Sub. Take the following example:

Dim TimeToRun

Sub Macro1()

    'Dim addIn As COMAddIn
    'Dim automationObject As Object
    'Set addIn = Application.COMAddIns("ProbeAddIn")
    'Set automationObject = addIn.Object

    'automationObject.ImportData

    MsgBox "Hello world"

End Sub

Sub Macro2()

    TimeToRun = Now + TimeValue("00:00:01")

    Application.OnTime TimeToRun, "Macro1" ' run Macro1 on 1 seconds

End Sub


Function Test()

    TimeToRun = Now + TimeValue("00:00:01")

    Application.OnTime TimeToRun, "Macro1" ' run Macro1 on 1 seconds

End Function

Note that Macro2 and function Test have the same code. Why is it that if I run Macro2 it works ok (message box shows up). But if I go to a a cell and type =Test() then I get an error even though I have the same code !

The reason why am I showing this example is because the code that I have commented out on Macro1 works great if I run it by running the macro directly. If I place that code inside the function Test it does not work. That code is executing the following method in my add-in project on visual studio:

enter image description here

The exception is:

System.Runtime.InteropServices.COMException was unhandled by user code HResult=-2146827284 Message=Exception from HRESULT: 0x800A03EC
Source="" ErrorCode=-2146827284 StackTrace: at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) at Microsoft.Office.Interop.Excel.Range.set_Value2(Object value) at ReadWrite.ImportData() in C:\Users\Antonio\Dropbox_Temp\visual studio\Probe add in test\ProbeAddIn\ProbeAddIn\Class1.cs:line 82 InnerException:

I got that exception because I ran:

Function Test()

    Dim addIn As COMAddIn
    Dim automationObject As Object
    Set addIn = Application.COMAddIns("ProbeAddIn")
    Set automationObject = addIn.Object                

    automationObject.ImportData

End Function

Instead of

Sub Test()

    Dim addIn As COMAddIn
    Dim automationObject As Object
    Set addIn = Application.COMAddIns("ProbeAddIn")
    Set automationObject = addIn.Object

    automationObject.ImportData

End Function

How can I make the code inside a macro and function run the same way? I want to trigger the execution of that method when user types in a formula not when the user runs a macro. Even if I have the function run the macro I get the same exception The only way I do not get an exception is if the first thing that runs is a macro...

Excel VBA Sub doesn't return, but a Function does. So your `Function Test` doesn't... — bonCodigo, Dec 19 '12 at 20:53
what about the last `Function Test()`? that one returns. It calls the ImportData method. That method is not able to change the value of cell A1 why? If I run that same method from a macro it works fine. Thanks for the help! — Tono Nam, Dec 19 '12 at 20:58
What you really want to do, make an add-in to change the cell value on a particular time? after every second? — bonCodigo, Dec 19 '12 at 21:04
I have a board: http://am.renesas.com/products/tools/introductory_evaluation_tools/renesas_demo_kits/yrdkrx63n/index.jsp I am able to read the value of a variable. I want to read the value every 500 milliseconds. I am able to do it with a macro but I will like to create a function in excel that does that. The signature of my method looks like `Read(memAddress as String, numberOfBytesToRead as integer)` — Tono Nam, Dec 19 '12 at 21:15
Variable? or cell value? Can you please show us your current macro? Why are you using `range1.Value2`? why not `value`? — bonCodigo, Dec 19 '12 at 21:19

score 2 · Accepted Answer · answered Dec 19 '12 at 21:44

Not sure, but the issue you have may be due to the fact that when your function is fired the Excel application is in edit mode and so cannot accept any input from outside hence the exception when you try to insert data.

If you want to update a cell in a continuous way in a clean manner what you need is a RTD server.

From your cells you will simply call :

=RTD("my.super.rtdserver",,"mydata")

From time to time your RTD server will notify Excel of new data and lets Excel call-back to retrieve them when it is ready.

Set value of cell in add in

1 Answers1