EXCEL VBA code example

User 677b9c22ff

21-09-2009 09:15:31


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!




ChemAxon 0e37943a96

21-09-2009 17:31:53


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:


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.









User 677b9c22ff

22-09-2009 00:18:11


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. :-)





ChemAxon bd13b5bd77

10-03-2010 23:16:28


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