How can I make C# function(UDF) as a formula in excel with predictive search for its parameter in excel vsto application

See more:

C#

.NET

MS-Excel

VSTO

AsyncTask

, +

I have an Excel vsto application in c#, I want to insert a UDf defined in C# as a formula in Excel in such a way that as soon as user types in excel cell like Myfunction( ,i.e. as soon as she opens the parenthesis, predictive search should be shown for first parameter may be inside a winform in listbox or dropdown.

Please Suggest any way to do it , I have tried inserting formula using Excel-DNA , then I am trying to read the string as soon as user types in using async await on sheet_selectionchange event but I am getting null value using "cell.value2" probably because it is picking blank value when slection change event occured, not the value that I typed during Task.Delay(10000); execution.

What I have tried:

public static async void Worksheet_SelectionchangeAsync(Excel.Range Target)
      {
          string val = "";
          await Task.Delay(10000);
           Excel.Application XlApp = Globals.ThisAddIn.Application;
          //excelApp = new Excel.ApplicationClass();
          //excelApp.Visible = true;
          // System.Threading.Thread.Sleep(10000);
          string cellAdd = XlApp.ActiveCell.Left.ToString();
              Excel.Worksheet ws = (Excel.Worksheet)XlApp.ActiveWorkbook.ActiveSheet;
              int row = Target.Row;
              int col = Target.Column;
              var cell1 = Target.Value2;
              Excel.Range cell = (Excel.Range)ws.Cells[row, col];
              Excel.Range r = (Excel.Range)XlApp.Selection;
              var cell11 = r.Value2;// x.Cells[4, 7];
              // null reference error( debugger function requires all threads to run)
                // XlApp.Cells[1, 1] = cell.Value2; // null reference ( debugger function requires all threads to run)
            string i = (Globals.ThisAddIn.Application.Cells[row, col] as Excel.Range).Value2.ToString() ;
            XlApp.EnableEvents = false;
      }