vb.net - Set Cell Formula in ExcelDNA UDF

[Wayne J.]

0down votefavorite

I am working on a UDF(User defined function) for Excel user, the task seems easy with the help of ExcelDNA. But While I test it in a cell of Excel with following two formula, both show #VALUE!. Need help to get this solved, thanks. =mySetCellFormula("Test", "") =mySetCellFormula("Test", "A1") Imports System.Net Imports System.Drawing Imports System.Windows.Forms Imports Microsoft.Office.Core Imports Excel = Microsoft.Office.Interop.Excel Imports ExcelDna.Integration Imports ExcelDna.Integration.XlCall Public Module MyFunctions Public Function mySetCellFormula(ByVal sFormuaR1C1 As String, ByVal cellAddress As String) As String ' Get the correct application instance Dim xlApp As Excel.Application Dim xlSheet As Excel.Worksheet Dim xlCell As Excel.Range xlApp = CType(ExcelDnaUtil.Application, Excel.Application) xlSheet = CType(xlApp.ActiveSheet(), Excel.Worksheet) If cellAddress = "" Then xlCell = xlApp.ActiveCell() Else xlCell = xlSheet.Range(cellAddress) End If 'xlCell.FormulaR1C1 = "=" & Chr(34) & sFormuaR1C1 & Chr(34) xlCell.FormulaR1C1 = "=" & sFormuaR1C1 mySetCellFormula = "" End Function End Module

[Govert van Drimmelen]

Hi Wayne,

Excel prevents you from performing certain operations from inside a UDF called from a worksheet. This is so that Excel can internally build a tree of dependencies between cells, which speeds the calculations and ensures that the calculation is correct and completes.

With your formula-setting UDF, Excel would find that the sheet has changed (since a cell's formula changed) and will recalculate. Then your function changes the sheet again, etc.

So Excel prevents this kind of thing in a calculation, causing the error you see. This limitation is in place whether you are using VBA or C++ or Excel-DNA to implement the UDF.

The best plan is to redesign the functions you provide into a 'functional' style that matches well with Excel's spreadsheet and calculation model. In the functional style, every formula just returns a value, and does not have other side-effects or hidden state.

Excel's spreadsheet model is not great as a meta-programming environment. If you want to generate an Excel sheet with various formulas, based on an input sheet, it's probably better to do that with a macro button rather than interleaved with the calculation part of Excel. Running a macro, you can inspect the sheet, change or build the new sheet with whatever formulas you want. This also matches the expectation that most users have of Excel, which is that a formula does not change or overwrite itself or other parts of the sheet when calculated. None of the built-in functions have this behaviour.

There are some really advanced cases where you might want to automatically schedule some work (typically a macro) to run at the end of the calculation. Excel-DNA allows you to schedule such a macro execution from inside a UDF using the ExcelAsyncUtil.QueueAsMacro helper. But that should be considered a dangerous and advanced feature, best avoided if possible.

-Govert

[Wayne J.]

Hi Govert,

First, thanks for the great product (ExcelDNA), which make my life much easier in handling UDF type of task.

I just start to use ExcelDNA about 2 weeks ago, your informative explanation really help me to understand the Excel Calculation Model and certain limitation (or rather to say it's the requirement) in the UDF.

 

Thank you!

Best Regards,

Wayne

[Wayne J.]

Hi Govert,

By the way, as you can see, I am using the below frame to write the UDF, but I also notice in C# example, class are used (UDF defined in a class), can we do the same in VB.NET? If yes, can you give a simple example? Thanks.

Public Module MyFunctions

End Module

B.G.
Wayne

[Govert van Drimmelen]

Hi Wayne,

In VB.NET you can put the functions in:

1. a "Public Module":

Public Module MyFunctions

    Public Function AddThem (val1 As Double, val2 As Double) As Double

        Return val1 + val2

    End Function

End Module

2. as "Shared" functions in a class:

Public Class MyFunctions

    Public Shared Function AddThem (val1 As Double, val2 As Double) As Double

        Return val1 + val2

    End Function

End Class

-Govert 

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Wayne J. [wayneon...@gmail.com]
Sent: 30 January 2016 03:15 AM
To: Excel-DNA
Subject: [ExcelDna] Re: vb.net - Set Cell Formula in ExcelDNA UDF

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Wayne J.]

Hi Govert,

Thanks for the enlightenment.

A lame question here, what's the difference between them? Especially, will it cause any difference for the user to use the UDF functions (defined in module vs. shared class) in formula? 

Also a side question from your earlier answer, now I know that Excel Calculation Model don't allow UDF to change other cell's content, but is it possible to insert an image to a cell (either current cell or other cell) by UDF?

What I really want to do is to display Qr code(or any other 1D/2D barcode) via UDF. I have seen example of 1D barcode through special font, which require to install font for each barcode type, I just think it would be better if we can make use of zxing function via UDF. 

B.G.

Wayne

To post to this group, send email to exc...@googlegroups.com.

[Govert van Drimmelen]

Hi Wayne,

Function in Module or Shared Function in Class will work the same.

I think Module is a bit clearer, and closer to VBA. Shared function in Class is more like C#.

For putting QR code in cell, you probably need to take ExcelAsyncUtil.QueueAsMacro approach. Will be tricky to get right, but should be possible.

-Govert

To post to this group, send email to exce...@googlegroups.com.

[Wayne J.]

[Wayne J.]

Hi Govert,

Below is the solution I worked out basing on your suggestion

It works for below formula

=mySetCellFormula("Test", "A1")

How can I make it work for reference also? Such as: 

=mySetCellFormula("Test", A1)

B.G.

Wayne

Imports System.Net
Imports System.Drawing
Imports System.Windows.Forms
Imports Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Imports ExcelDna.Integration
Imports ExcelDna.Integration.XlCall

Public Module MyFunctions
    Public Function mySetCellFormula(ByVal sFormuaR1C1 As String, ByVal cellAddress As String) As String
       

' Get the correct application instance

        Dim xlApp As Excel.Application
        Dim xlSheet As Excel.Worksheet
        Dim xlCell As Excel.Range
        xlApp = CType(ExcelDnaUtil.Application, Excel.Application)
        xlSheet = CType(xlApp.ActiveSheet(), Excel.Worksheet)
        If cellAddress = "" Then
            xlCell = xlApp.ActiveCell()
        Else
            xlCell = xlSheet.Range(cellAddress)
        End If

        ExcelAsyncUtil.QueueAsMacro( _
            Sub()

                xlCell.FormulaR1C1 = "=" & sFormuaR1C1

            End Sub)

[Iron Mask]

Govert,

This is very useful.

I just wonder how would you implement the same in c#?

Thanks,

Iron

[Iron Mask]

Thanks to Govert and Wayne;

I ended up implementing the same in C# which works for me:

   public static void SetCellFormula(string cellAddress, string sFormua)
   {
     try
     {
       Excel.Range xlCell;
       var xlApp = (Excel.Application)ExcelDnaUtil.Application;
       var activeSheet = xlApp.ActiveSheet;
       var xlSheet = (Excel.Worksheet)activeSheet;
       if (string.IsNullOrEmpty(cellAddress))
       {
         xlCell = xlApp.ActiveCell;
       }
       else
       {
         xlCell = xlSheet.get_Range(cellAddress);
       }
 
       ExcelAsyncUtil.QueueAsMacro(
         () =>
           {
             xlCell.Formula = $"={sFormua}";
           });
     }
     catch (Exception ex)
     {
       Debug.WriteLine(ex);
     }
   }

along with:

   public static void SetCellValue(string cellAddress, object cellValue)
   {
     try
     {
       Excel.Range xlCell;
       var xlApp = (Excel.Application)ExcelDnaUtil.Application;
       var activeSheet = xlApp.ActiveSheet;
       var xlSheet = (Excel.Worksheet)activeSheet;
       if (string.IsNullOrEmpty(cellAddress))
       {
         xlCell = xlApp.ActiveCell;
       }
       else
       {
         xlCell = xlSheet.get_Range(cellAddress);
       }
 
       ExcelAsyncUtil.QueueAsMacro(
         () =>
           {
             xlCell.Value2 = cellValue;
           });
     }
     catch (Exception exc)
     {
       Debug.WriteLine(exc);
     }
   }

which can be verified by this test function:

   [ExcelFunction(Name = "TestFormula", Description = "Test Set Formula on Cell")]
   public static string TestFormula()
   {
     SetMyCellValue("A1", 1);
 
     SetMyCellValue("A2", 2);
     SetMyCellValue("A3", 4);
     SetMyCellValue("A4", 8);
 
     SetMyCellValue("B1", -1);
     SetMyCellValue("C1", 3);
     SetMyCellValue("D1", 5);
 
     SetCellFormula("B2", "$A$1*B$1-$A2");
 
     return "=TestFormula";
   }

[Govert van Drimmelen]

I should warn that this approach of mutating different parts of the sheet from a UDF can be pretty dangerous, as it doesn’t integrate with Excel’s functional model and dependency tree at all.

 

For example, if you are working on another sheet and that causing recalculation of one of these functions, the behaviour from this code might be unexpected.

 

Anyone trying this should be really careful – it’s not an approach I support or encourage at all.

 

-Govert

 

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of Iron Mask
Sent: 24 March 2019 16:31
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] Re: vb.net - Set Cell Formula in ExcelDNA UDF

 

Thanks to Govert and Wayne;

--

[John Bylsma]

Hi Govert,

I’m working on a similar implementation to this, except the code I’m the UDF calls an async method which replaces the calling formula with a value, so no recalculation will occur. This will avoid all the dangers you warn of, right?

Best,
John

On Sunday, March 24, 2019 at 9:39:12 AM UTC-5, Govert van Drimmelen wrote:
> I should warn that this approach of mutating different parts of the sheet from a UDF can be pretty dangerous, as it doesn’t integrate with Excel’s functional model and dependency tree at all.
>  
> For example, if you are working on another sheet and that causing recalculation of one of these functions, the behaviour from this code might be unexpected.
>  
> Anyone trying this should be really careful – it’s not an approach I support or encourage at all.
>  
> -Govert
>  
>  
>  
> From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of Iron Mask
> Sent: 24 March 2019 16:31
> To: Excel-DNA <exce...@googlegroups.com>
> Subject: [ExcelDna] Re: vb.net - Set Cell Formula in ExcelDNA UDF
>  
>
> Thanks to Govert and Wayne;
>
>  
>
> I ended up implementing the same in C# which works for me:
>
>  
>    public static void SetCellFormula(string cellAddress, string sFormua)   {     try     {       Excel.Range xlCell;       var xlApp = (Excel.Application)ExcelDnaUtil.Application;       var activeSheet = xlApp.ActiveSheet;       var xlSheet = (Excel.Worksheet)activeSheet;       if (string.IsNullOrEmpty(cellAddress))       {         xlCell = xlApp.ActiveCell;       }       else       {         xlCell = xlSheet.get_Range(cellAddress);       }        ExcelAsyncUtil.QueueAsMacro(         () =>           {             xlCell.Formula = $"={sFormua}";           });     }     catch (Exception ex)     {       Debug.WriteLine(ex);     }   } 
>
> along with:
>
>  
>    public static void SetCellValue(string cellAddress, object cellValue)   {     try     {       Excel.Range xlCell;       var xlApp = (Excel.Application)ExcelDnaUtil.Application;       var activeSheet = xlApp.ActiveSheet;       var xlSheet = (Excel.Worksheet)activeSheet;       if (string.IsNullOrEmpty(cellAddress))       {         xlCell = xlApp.ActiveCell;       }       else       {         xlCell = xlSheet.get_Range(cellAddress);       }        ExcelAsyncUtil.QueueAsMacro(         () =>           {             xlCell.Value2 = cellValue;           });     }     catch (Exception exc)     {       Debug.WriteLine(exc);     }   } which can be verified by this test function:    [ExcelFunction(Name = "TestFormula", Description = "Test Set Formula on Cell")]   public static string TestFormula()   {     SetMyCellValue("A1", 1);      SetMyCellValue("A2", 2);     SetMyCellValue("A3", 4);     SetMyCellValue("A4", 8);      SetMyCellValue("B1", -1);     SetMyCellValue("C1", 3);     SetMyCellValue("D1", 5);      SetCellFormula("B2", "$A$1*B$1-$A2");      return "=TestFormula";   }  
> On Tuesday, March 19, 2019 at 9:39:02 PM UTC-4, Iron Mask wrote:
>
> Govert,
>
> This is very useful.
>
> I just wonder how would you implement the same in c#?
>
> Thanks,
>
> Iron
>
> On Thursday, January 28, 2016 at 12:59:57 AM UTC-5, Wayne Jin wrote:
>
>
> 0down votefavorite
>
>

> I am working on a UDF(User defined function) for Excel user, the task seems easy with the help of ExcelDNA. But While I test it in a cell of Excel with following two formula, both show #VALUE!. Need help to get this solved, thanks.=mySetCellFormula("Test", "")=mySetCellFormula("Test", "A1") Imports System.NetImports System.DrawingImports System.Windows.FormsImports Microsoft.Office.CoreImports Excel = Microsoft.Office.Interop.ExcelImports ExcelDna.IntegrationImports ExcelDna.Integration.XlCall Public Module MyFunctions    Public Function mySetCellFormula(ByVal sFormuaR1C1 As String, ByVal cellAddress As String) As String        ' Get the correct application instance        Dim xlApp As Excel.Application        Dim xlSheet As Excel.Worksheet        Dim xlCell As Excel.Range        xlApp = CType(ExcelDnaUtil.Application, Excel.Application)        xlSheet =

CType(xlApp.ActiveSheet(), Excel.Worksheet)        If cellAddress = "" Then            xlCell = xlApp.ActiveCell()        Else            xlCell = xlSheet.Range(cellAddress)        End If        'xlCell.FormulaR1C1 = "=" & Chr(34) & sFormuaR1C1 & Chr(34)        xlCell.FormulaR1C1 = "=" & sFormuaR1C1        mySetCellFormula = ""    End FunctionEnd Module

[Govert van Drimmelen]

Hi John,

I'm not sure.

Your users will type in a formula and the result will be a sheet with the formula gone and some numbers and no indication where they came from.
If your formula had some input parameters, and they change, there will be nothing that indicates to Excel or your users that some part of your sheet is now invalid and needs to be recalculated. So you're not using Excel's spreadsheet nature and dependency tracking at all.
The problem in doing this as part of a UDF _function_ is that you are breaking the expectation that many users might have about the workings of a spreadsheet, and how it works when you type formulas into the sheet, including compositionality of functions, the dependency tracking and the ability to experiment and try formulas until something works.
It would be more obvious that something else (other than the function model of a spreadsheet) is going on if you have a ribbon button or right-click context menu that reads some values on the sheet and executes a macro to write stuff on the sheet. It's perhaps harder to use, but less likely to break the Excel mental model, and thus make a mess later on.

But that's just my opinion.

-Govert