Pool Values in Memory RRS feed

  • Question

  • Hi,

    I have recently created an Excel UDF (User Defined Function) with .NET. (For details:

    Whenever it is triggered it logs/appends the input text in a text file.


    And the output is:

    But whenever i trigger this function, it opens the textfile and closes it which is a performance hit over 500 records. So i thought to pool the values in memory. But i have absolutely no idea...

    Btw simple code is below:

    using System;
    using System.Runtime.InteropServices;
    using Microsoft.Win32;
    namespace AutomationAddin
     // Replace the Guid below with your own guid that
     // you generate using Create GUID from the Tools menu
     public class MyFunctions
      public MyFunctions()
      public bool MyDOTNETUDF(string TextToWrite)
    		// 1: Append single line to new file
    		using (StreamWriter writer = new StreamWriter("C:\\deneme.txt", true))
       return true;
      public static void RegisterFunction(Type type)
        GetSubKeyName(type, "Programmable"));
       RegistryKey key = Registry.ClassesRoot.OpenSubKey(
        GetSubKeyName(type, "InprocServer32"), true);
        System.Environment.SystemDirectory + @"\mscoree.dll",
      public static void UnregisterFunction(Type type)
        GetSubKeyName(type, "Programmable"), false);
      private static string GetSubKeyName(Type type,
       string subKeyName)
       System.Text.StringBuilder s =
        new System.Text.StringBuilder();
       return s.ToString();


    Senior BI Consultant & PM @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: Follow Me
    • Moved by CoolDadTx Thursday, August 18, 2011 1:26 PM Office related (From:Visual C# General)
    Thursday, August 18, 2011 11:02 AM


  • Hello,

    Instead of writing TextToWrite to the file, you can add it to a string defined on the class level (also add @"\n\r" or @"\n"). The resulting string can be writen to the file on a condition such as "the number of calls exceeds a predefined value" or "the length of the resulting string exceeds...".

    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Friday, August 19, 2011 6:50 AM