0

I have created an Excel Formula using help from this link: Canonical: How to call .NET methods from Excel VBA

It is working fine, but now I have a requirement of sending an Excel Range Object to this formula, to C# and operate on that range. Every time I try to do this, it gives me "#Value!" error.

How do I do this?

(I am writing the code for the formula in the C# end and not in the VBA. Please mind that.)

I have tried using normal "Microsoft.Office.Interop.Excel.Range" object.

namespace FormulaAddin
    {
    [Guid("29A34EFD-B8A3-48D8-847A-4BF402831617")]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ComVisible(true)]
    public class Formulae
        {

        public Formulae ()
            {
            }

        public int CountRange(Excel.Range nRange)
            {            
            //Some code working on the range
            }       

        #region Registering COM Object

        [ComRegisterFunction]
        public static void RegisterFunction (Type type)
            {
            Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));
            RegistryKey key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true);
            key.SetValue("", Environment.SystemDirectory + @"\mscoree.dll", RegistryValueKind.String);
            }

        [ComUnregisterFunction]
        public static void UnregisterFunction (Type type)
            {
            Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false);
            }

        private static string GetSubKeyName (Type type, string subKeyName)
            {
            System.Text.StringBuilder s = new System.Text.StringBuilder();
            s.Append(@"CLSID\{");
            s.Append(type.GUID.ToString().ToUpper());
            s.Append(@"}\");
            s.Append(subKeyName);
            return s.ToString();
            }

        #endregion
        }
    }
  • When you get the #VALUE! error, it means that an excel function (Range in your case), received inputs that it was unable to process. Therefore, i'd assume the error you're getting lies within the input for the Range function, instead of an error in your code. What's the input for this Range function, and what is the content of those cells? – Lennart Jan 21 '19 at 11:31
  • I am trying to parse an Excel Table and make some calculation based on it. The Excel Table is my Range input. – Sourish Atorthy Jan 22 '19 at 04:07

0 Answers0