Problem .Net 6 COM and excel VBA revisitied

SilverShaded

Well-known member
Joined
Mar 7, 2020
Messages
93
Programming Experience
10+
Hi, i've setup a project that uses .Net 6 and is call from excel VBA. Some of the functions work fine but others are not. Its really not clear to me why they dont work. I've tried everything i can think of many times over.


The interface;


The Interface:
namespace COMThermoClass
{
    [Guid(ContractGuids.ServerInterface)]
    [ComVisible(true)]
    [InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]

    public interface IThermo
    {
        double TestCritT();
      //  double LiqEnthalpyReal(object comps, object X, double T, double P, int method);
        double StreamEnthalpyReal(object comps, object X, double T, double P, int method);
        double StreamTemperatureReal(object comps, object X, double H, double P, int method);
        double CritP(double Tb, double SG, int method);
        double CritT(double Tb, double SG, int method);
        double DistillationPoint(object comps, object X, object SG, object BP, int method, string DistType, string distpoint);
        double EnthalpyFormationReal(object comps, object X);
        double LiqComponentEnthalpy(string comp, double T, double P, int method);
     //  double VapComponentEnthalpy(string comp, double T, double P, int method);
    }
}


The class;


The class:
namespace COMThermoClass
{
    [Guid(ContractGuids.ServerClass)]
    [ClassInterface(ClassInterfaceType.None)]
    [ComVisible(true)]
    public partial class COMThermoClass : IThermo
    {
        private enumCalcResult cres;

        public COMThermoClass()
        {
            Thermodata data = new Thermodata();
        }

        public double TestCritT()
        {
            //Debugger.Launch();
            return 99.9;
        }
    }
}


some function are in other files such as;




Part Partial Class code:
  public partial class COMThermoClass
    {
        public double LiqComponentEnthalpy(string comp, double T, double P, int method)
        {
            Debugger.Launch();
            double res = 0;
            BaseComp sc = Thermodata.GetRealComponent((string)comp);
            sc.molefraction = 1;
            Components cc = new Components();
            cc.Add(sc);
            cc.T = T;
            cc.P = P;

            cc.thermo.Enthalpy = (enumEnthalpy)method;

            res = ThermodynamicsClass.BulkStreamThermo(cc, cc.P, T, enumMassOrMolar.Molar, enumFluidRegion.Liquid, ref cres).H;
            return res;
        }

        public double StreamEnthalpyReal(object comps, object X, double T, double P, int method)
        {
            double res;
            Components cc = new Components();
            cc.T = T;
            cc.P = P;
            BaseComp sc;

           // Debugger.Launch();

            string[] c = (string[])comps;
            double[] x = (double[])X;

            double sum = 0;
            for (int i = 0; i < x.Length; i++)
            {
                sum += x[i];
            }

            if (sum != 1)  // normalise
            {
                for (int i = 0; i < x.Length; i++)
                {
                    x[i] /= sum;
                }
            }

            cc.thermo.Enthalpy = (enumEnthalpy)method;

            for (int i = 0; i < x.Length; i++)
            {
                sc = Thermodata.GetRealComponent(c[i]);
                if (sc == null)
                {
                    System.Windows.Forms.MessageBox.Show("Component " + c[i].ToString() + "Not Found In Database");
                }
                else
                {
                    sc.molefraction = x[i];
                    cc.Add(sc);
                }
            }

            BasicPropList bpl = new BasicPropList();

            bpl.T.BaseValue = T;
            bpl.P.BaseValue = P;

            bpl.T.origin = SourceEnum.Input;
            bpl.P.origin = SourceEnum.Input;

            FlashClass.Flash(cc, bpl, false);

            res = cc.BulkEnthalpy();
            return res;
        }
        public double StreamTemperatureReal(object comps, object X, double H, double P, int method)
        {
            double res;
            Components cc = new Components();
            cc.P = P;
            BaseComp sc;

            //Debugger.Launch();

            string[] c = (string[])comps;
            double[] x = (double[])X;

            double sum = 0;
            for (int i = 0; i < x.Length; i++)
            {
                sum += x[i];
            }

            if (sum != 1)  // normalise
            {
                for (int i = 0; i < x.Length; i++)
                {
                    x[i] /= sum;
                }
            }

            cc.thermo.Enthalpy = (enumEnthalpy)method;

            for (int i = 0; i < x.Length; i++)
            {
                sc = Thermodata.GetRealComponent(c[i]);
                if (sc == null)
                {
                    System.Windows.Forms.MessageBox.Show("Component " + c[i].ToString() + "Not Found In Database");
                }
                else
                {
                    sc.molefraction = x[i];
                    cc.Add(sc);
                }
            }

            BasicPropList bpl = new BasicPropList();

            bpl.H.BaseValue = H;
            bpl.P.BaseValue = P;

            bpl.H.origin = SourceEnum.Input;
            bpl.P.origin = SourceEnum.Input;

            FlashClass.Flash(cc, bpl, false);

            res = cc.T;
            return res;
        }
        public double StreamComponentEnthalpy(string c, double T, double P, int method)
        {
            double res;
            Components cc = new Components();
            cc.T = T;
            cc.P = P;
            BaseComp sc;

            cc.thermo.Enthalpy = (enumEnthalpy)method;
            sc = Thermodata.GetRealComponent(c);

            if (sc == null)
            {
                System.Windows.Forms.MessageBox.Show("Component " + c.ToString() + "Not Found In Database");
            }
            else
            {
                sc.molefraction = 1;
                cc.Add(sc);
            }
          
            BasicPropList bpl = new BasicPropList();

            bpl.T.BaseValue = T;
            bpl.P.BaseValue = P;

            bpl.T.origin = SourceEnum.Input;
            bpl.P.origin = SourceEnum.Input;

            FlashClass.Flash(cc, bpl, false);

            res = cc.BulkEnthalpy();
            return res;
        }


The IDL file

C:
// Generated .IDL file (by the OLE/COM Object Viewer)
//
// typelib filename: COMThermo.tlb

[
  uuid(A5996396-7DB2-44B4-BD98-4D769792DE2D),
  version(1.0),
  custom(90883F05-3D28-11D2-8F17-00A0C9A6286C, "COMThermo, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null")

]
library COMThermo
{
    // TLib : mscorlib.dll : {BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}
    importlib("mscorlib.tlb");
    // TLib : OLE Automation : {00020430-0000-0000-C000-000000000046}
    importlib("stdole2.tlb");

    // Forward declare all types defined in this typelib
    interface ICOMThermo;

    [
      odl,
      uuid(B221E497-C862-4C40-B4E6-5D79E7AB28D5),
      version(1.0),
      oleautomation,
      custom(B10B0742-6D84-415D-894C-798ED2D76C1D, "COMThermo.ICOMThermo")

    ]
    interface ICOMThermo : IUnknown {
        [id(0x60020000)]
        HRESULT _stdcall TestCritT([out, retval] double* pRetVal);
        [id(0x60020001)]
        HRESULT _stdcall StreamEnthalpyReal(
            [in] VARIANT comps,
            [in] VARIANT X,
            [in] double T,
            [in] double P,
            [in] long method,
            [out, retval] double* pRetVal);
        [id(0x60020002)]
        HRESULT _stdcall StreamTemperatureReal(
            [in] VARIANT comps,
            [in] VARIANT X,
            [in] double H,
            [in] double P,
            [in] long method,
            [out, retval] double* pRetVal);
        [id(0x60020003)]
        HRESULT _stdcall LiqEnthalpyReal(
            [in] VARIANT comps,
            [in] VARIANT X,
            [in] double T,
            [in] double P,
            [in] long method,
            [out, retval] double* pRetVal);
        [id(0x60020004)]
        HRESULT _stdcall LiqComponentEnthalpy(
            [in] BSTR comps,
            [in] double T,
            [in] double P,
            [in] long method,
            [out, retval] double* pRetVal);
    };
    [
      uuid(BE532448-9A5A-4273-B93C-0573545D36C4),
      version(1.0),
      custom(B10B0742-6D84-415D-894C-798ED2D76C1D, "COMThermo.COMThermo")
    ]
    coclass COMThermo {
        interface _Object;
        [default] interface ICOMThermo;
    };
};




The Excel VBA


Excel Code:
Dim test As New COMThermo.COMThermo

Function Test1()
    Test1 = test.TestCritT()
End Function

Sub LiqComponentEnthalpy()

Dim res As Double

res = test.LiqComponentEnthalpy("n-Butane", 25 + 273.15, 1#, 5)

'End Function
End Sub

Function StreamEnthalpy(comp As Range, Xfractions As Range, T As Double, P As Double, Method As Integer) As Double
Dim Names() As String
Dim x() As Double

No = comp.Cells.Count
ReDim Names(No - 1)
ReDim x(No - 1)

For i = 1 To No
    Names(i - 1) = comp(i).Value
    x(i - 1) = Xfractions(i).Value
Next

StreamEnthalpy = test.StreamEnthalpyReal(Names, x, T + 273.15, P, Method)

'End Function
End Function



When i run the VBA "TestTcrit" works fine as does "StreamEnthalpy" but "LiqComponentEnthlapy" give an error saying "Runtime error -2147467261 "Object Reference not set to an instance of an object".


Any possible ideas why?
 
Last edited:
Just guessing, could it be Debugger.Launch() in LiqComponentEnthalpy?
 
It shouldn’t be but right now i guess anything is possible. Ive succesfully debugged some other calls in exactly that way and i don’t think the code is getting that far. I’ll try taking it out and see though…

EDIT: I tired it, same problem. Beginning to think this part of .net 6 is a bit buggy.
 
Last edited:
When I used to this kind of stuff for Outlook add-ins, sometimes the version that I thought that I had edited and compiled doesn't quite match the behavior of what I was expecting to happen when run from Outlook, but works correctly when run from within the debugger with my unit tests and other test harnesses. More often than not, it turned out that I had not installed the latest version into the GAC, or there was some kind of failure while trying to install into the GAC, but I didn't notice. Anyway, what I would usually do is clear the GAC, do a clean build in Visual Studio, and then install the COM component.
 
So you are registering your COM object using one of the last two registration options?

 
So you are registering your COM object using one of the last two registration options?


My understading is for .Net 6, you generate an extra DLL (in my case called "COMThermoClass.comhost.dll") by adding a line to the project file "<EnableComHosting>true</EnableComHosting>". You then register the created DLL using regsvr32.
 
OK, i have no idea what im doing but after a load of trial and error, mostly error, i found that you can have more methods declared in your C# class interface than you have in the IDL/TLB file, but if you do the order you put them becaomes very important. Even more than that, in any case, the methods decalred in the C# Intreface file, must be in the same order as they are in the IDL file...

Maybe obvious to some of you but wasn't to me, anyway i hope that information saves somebody from some serious hair pulling...
 
As I recall, there is a way to assign ids (dispatch IDs as I recall they are called) to each of the methods in the IDL and in the C# methods so that you don't have to have the C# methods be in the exact order as the IDL. Good side is more redundancy. Bad side more magic numbers and things that could go wrong. So it's pick your poison: fragile because the order changed by someone modifying the code a few months or years from now; or fragile because some added a new method and just copied and pasted the IDs.
 
Thanks, ill stick with the keeping them in the same order approach, there was about 80 method calls but i managed to sort them and the Interface out quickly in excel and paste them back in the right order so they seem to be working ok now. That was the last of my conversion issues to .Net 6.
 
Back
Top Bottom