AddStructuresToColumns via VBA - must be late-bound ?

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

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

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

Yes, Dispatch would be the correct.

Viktor

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.

 

 

 

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

:)

 

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

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

Thanks for the good news.

Viktor