range continually fails too set values in C# with interop excel

Question

I am relatively new to c#. My code continually fails to insert a value to a cell. I have been on this for multiple days now. I have tried almost everything (as you can see in the code) but keep getting unknown errors.

Usually this error:

System.Runtime.InteropServices.COMException (0x800A03EC): 0x800A03EC
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Object[] aArgs, Boolean[] aArgsIsByRef, Int32[] aArgsWrapperTypes, Type[] aArgsTypes, Type retType)
   at Microsoft.Office.Interop.Excel.Range.set__Default(Object RowIndex, Object ColumnIndex, Object )

I am using interop excel and trying to call functions in excel using excelDNA. Basically, I am trying to get values within a range (["A1", "A100"]) from a sheet called prefill_sheet. Then loop through each cell in the range, get it's value and pass it to openai api for processing. This part has worked so far.

My only issue is when trying to add the values to another sheet called summarySheet. I have tried adding the value cell by cell inside a for loop and it still gives me the same error. I have tried using a range to set the value. Same error. When I use range.set_Value(), I get a type mismatch error.

CODE:

public static class AIPrefillSummarization{

[ExcelFunction(Description = "Summarize Prefill", Name = "SummarizePrefill")]
public static string Summarize_Prefill()
{
    try
    {
        Application excelApp = Marshal2.GetActiveObject("Excel.Application") as Application;

        Worksheet? prefill_sheet = excelApp.Sheets["Prefill"] as Worksheet;
        Worksheet? summarySheet = excelApp.Sheets["Prefill Summary"] as Worksheet;
        Worksheet? configSheet = excelApp.Sheets["Config"] as Worksheet;


        if (prefill_sheet != null && configSheet != null && summarySheet != null)
        {
            String? apiKey = configSheet.Range["A1"].Value as String;
            prefill_sheet.Visible = XlSheetVisibility.xlSheetVisible;
            prefill_sheet.Unprotect();

            Excel_Range prefillRange = prefill_sheet.Range["A1", "A100"];

            if (apiKey == null || apiKey.Equals(""))
            {
                return "API Key not found in Config sheet A1!";
            }

            List<string> summaries = new List<string>();
            for (int i = 0; i < prefillRange.Rows.Count; i++)
            {
                Excel_Range cell = (Excel_Range) prefillRange.Cells[i + 1, 1];
                if (cell == null)
                {
                    continue;
                }
                object cellValue = cell.Value;
                string? value = cellValue?.ToString();
                if (value == null || value == "")
                {
                    summaries.Add("HELLO ALEX...");
                    continue;
                }
                else
                {
                    //Console.WriteLine($"cell_value: {value}");
                    //summaries.Add(SendToChatGPT(value, apiKey));
                    summaries.Add("HELLO ALEX...");
                }
            }

            Excel_Range allCells = summarySheet.Cells;
            allCells?.ClearContents();

            // Assuming 'summarySheet' is an Excel worksheet object and 'summaries' is a List<string>
            Excel_Range range = summarySheet.Range["A1"];
            var summariesArray = summaries.ToArray();

            range.ClearContents();
            summarySheet.Activate();
            

            // Create a two-dimensional array (1 column, N rows)
            int startRow = 1;
            object[,] outputArray = new object[summariesArray.Length, 1];
            for (int i = 0; i < summariesArray.Length; i++)
            {
                //Excel_Range cell = (Excel_Range)summarySheet.Rows.Cells[startRow + i, 1];
                try
                {
                    outputArray[i, 0] = summariesArray[i];
                    //cell.Value = "Hello";
                }
                catch (Exception ex)
                {
                    return $"Error writing to cell {startRow + i}, {1}: {ex}: range.Address: {range.Address}";
                }
            }

            // Resize the range to match the number of items in summaries
            range = range.Resize[summariesArray.Length, 1];
            StringBuilder debugOutput = new StringBuilder();
            for (int i = 0; i < summariesArray.Length; i++)
            {
                debugOutput.AppendLine($"Row {i + 1}: {outputArray[i, 0]}");
            }

            //return debugOutput.ToString();

            // Assign the values to the Excel range
            range.set_Value(outputArray);
            //range.Value2 = outputArray;

        }
        return "Prefill summarized!";
    }
    catch (Exception ex)
    {
        return ex.ToString();
    }
}

Answer 1

Looks like you're trying to update cells other than the cell where the function is running. that's not allowed using standard user defined functions as it breaks the execution model.

If you want to run a function in one cell that modifies values/layout of other cells the easiest way is to use the QueueAsMacro() function to run the code on the Macro thread after the UDF completes.