AddStructuresToColumns via VBA - must be late-bound ?

User af3e34cbec

28-05-2012 22:23:44

I am having a minor issue using AddStructuresToColumn from Excel VBA.

If I have a molfile string in the variable AMolfile and I try to call AddStructuresToColumn like this:


  Dim JChemComAddIn As Office.COMAddIn
  Set JChemComAddIn = Application.COMAddIns(
"JChemExcel.JChemExcelAddin")
  Set JChemApi = JChemComAddIn.Object

  Dim Molfiles() As String
  ReDim Molfiles(0 To 0)
 
  Molfiles(0) = AMolfile

  JChemApi.AddStructuresToColumn Molfiles, "mol", Col, "", ActiveWorkbook.Name, ActiveSheet.Name, Row

I get a VBA error "Compile error: Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic".

This error only occurs if the JChemApi variable is declared as JChemExcelCOMInterfacesLib.IJChemExcelAPI.
If the variable JChemApi is of type Object, and I therefore go through the late-bound dispatch interface, it works as expected.

This is not a big issue at all but it would be nice if AddStructuresToColumn worked from VBA through the typed interface. Perhaps changing the structures() String Array to a variant array in the AddStructuresToColumn signature could solve the issue and still keep it backwards compatible ?

Cheers


-- Jan

ChemAxon bd13b5bd77

29-05-2012 06:12:40

Hi Jan,


 


we do not prefer variant only for optional parameters. This APi is not only for VBA, but C#, C++ and so forth.


Typed parameters expected in general. And we cannot break the compatibility changing a not optional parameter.


Viktor

User af3e34cbec

29-05-2012 07:18:41

Hi Viktor,


No problem for me. However, I got slightly misled by


http://www.chemaxon.com/jchem4excel/developerguide/welcome_topic.html


where it says "call it natively (recommended)". I got the impression that the typed interface was the recommended way because that is to me the most "native" way of doing it :-). And you are really saying quite the opposite - I think.


Using "native" here is probably confusing. "Dispatch", "late-bound" or "via an Object variable" is probably better ?



Cheers


-- Jan

ChemAxon bd13b5bd77

29-05-2012 16:28:09

Yes, Dispatch would be the correct.


Viktor

ChemAxon bd13b5bd77

29-05-2012 16:30:56

Typed interface is the easiest for parameter evaluation, it is faster at the same time. However VBA might have the limitation to understand what pretty obvious for .NET.


Variant looks object in C#, so other way around when C# programmer writes criticism on the forum, it would say that object is a bit confusing to him/her, why do not we use more typed interface.


 


 

User af3e34cbec

29-05-2012 21:58:51

 


Variant looks object in C#, so other way around when C# programmer writes criticism on the forum, it would say that object is a bit confusing to him/her, why do not we use more typed interface.


Ha :-) - yes, point taken :-).


Cheers


-- Jan

ChemAxon bd13b5bd77

30-05-2012 07:38:45

:)


 


Jan,


http://www.chemaxon.com/download.php?d=/data/download/jchem4xl/Development/JChem_For_Excel_5.10.0.586.msi


please check the new API IsLoaded with this early adaptor.


Thanks,


Viktor

User af3e34cbec

30-05-2012 18:11:47

Hi Viktor,


I installed it and it seems to work just as it should.


I can repeatedly query IsLoaded without unwanted side effects. IsLoaded will start to report True once the JChem splash screen has shown for a few seconds - e.g. if I add structures to the sheet or if I touch the EnableDrawing property.


So, this will allow me to write a much better JChem integration layer. Thanks :-).


Cheers


-- Jan

ChemAxon bd13b5bd77

30-05-2012 19:01:03

Thanks for the good news.


Viktor