Problem .Net 6 COM and excel VBA revisitied

S

SilverShaded

Well-known member
Joined
Mar 7, 2020
Messages
110
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:
Sort by date Sort by votes
Just guessing, could it be Debugger.Launch() in LiqComponentEnthalpy?
 
Upvote 0 Downvote
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:
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
So you are registering your COM object using one of the last two registration options?

docs.microsoft.com

Registering Assemblies with COM - .NET Framework

Register or unregister assemblies with COM using the Assembly Registration Tool (Regasm.exe), which adds information about the class to the system registry.
docs.microsoft.com
 
Upvote 0 Downvote
Skydiver said:
So you are registering your COM object using one of the last two registration options?

docs.microsoft.com

Registering Assemblies with COM - .NET Framework

Register or unregister assemblies with COM using the Assembly Registration Tool (Regasm.exe), which adds information about the class to the system registry.
docs.microsoft.com
Click to expand...

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.
 
Upvote 1 Downvote
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...
 
Upvote 1 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote

Similar threads

SiamIT
  • Question
Issue With Unicode Dictionary
Replies
8
Views
731
SiamIT
SiamIT
M
  • Question
Add a class to a console app?
Replies
4
Views
965
JohnH
JohnH
S
  • Question
Need Help in Compiling C# Simple Multithreading Program
Replies
9
Views
2K
cjard
cjard
R
  • Solved
EF Core 6 Identity value generation problem
Replies
2
Views
3K
jmcilhinney
J
Anwind
  • Question
I have to implement GetSchemaTable
Replies
6
Views
2K
cjard
cjard

Share this page

Latest posts

Back
Top Bottom