EXCEL VBA code example

Hi,

is it possible to get a simple example how to access the functions from EXCEL VBA,

with late or early binding? For example a structure is in cell(2,2) how would I print

JCExactMass(workbookinfo, molecule) from JChemExcel.Functions in the EXCEL VBA

debug window? I assume molecules are already imported.

 

That would be really helpful for compute intensive functions like

JCConformerCount, JCDihedral, JCAngle, JCHasValidConformer because they could be

calculated in VBA and hardcoded into the EXCEL sheet. That sounds as it defies the

principle, but if you have 100 structures and you sort them with one of the above

functions it will autocalculate them with every sort (unless autocalc is off) and that will be a never

ending story (it will be very slow). So one solution is VBA, the other one is to turn

autocalcalculation off.

 

Thank you!

Tobias

 

 

Hi,

at the moment we do not officially support calling functions from VBA, but we might do it so in the future, and than publish the necessary information. It is mainly a licensing issue, we have to solve that these function are called from in-process (Excel VBA) code only.

We will create a small VBA example how to call our initial API methods.

Here is an example how to convert formulas to values in VBA:

http://www.ozgrid.com/Excel/convert-excel-formulas-values.htm

We might include it as an action later, since it might be useful to others as well.

 

By the way, would the functions JCDihedral, JCAngle important for you? We planned to remove certain functions which require ()atom number input in the future, and rather use an image image representation.

Please also note, that the atom numbers currently are not the same, as the atom number display, rather a zero based index. If these functions are required, we will fix this, because it is more convenient to use the atom number displayed.

We will also enhance our caching mechanism to include the result of computation intensive calculations. this would avoid the recalculation of these functions, if applied for the same structure hash.

Regards,

Tamas.

 

 

 

 

 

 

Hi,

yes, I have the VBA cells.value function references (or paste as value).

 

Regarding the functions which require Atom indices: I would say, definitely helpful,

but requires EXCEL VBA to loop through the atom index. Thatswhy it would be nice to

have the direct VBA access to the JChem ExcelFunctions. :-)

 

Thanks

Tobias

 

 

API Guide from 1.1.3 contains some direct VBA examples for our API.

https://www.chemaxon.com/download.php?d=/data/download/jchem4xl/JChem_for_Excel_API_Guide.zip