2 years ago
#56978
user17946664
Calling .Net 6 from Excel VBA, some calls are running ok others aren't
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:
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;
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
// 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
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 deas why?
.net
vba
com
0 Answers
Your Answer