5.1     Excel Features


22 Apr 99

This page describes features added or enhanced in ChemFinder for Excel 5.1.


Note: each of the testing suggestions for these features should start with the following steps to set up a useful worksheet.

  1. start with a fresh workbook
  2. New ChemFinder Worksheet
  3. "Import Table" using sample.sdf in the ChemFinder samples directory
  4. Add the ISSTRUCTURE column:
  1. Click on cell to right of "ACTIVITY" cell.
  2. Enter ISSTRUCTURE in the cell
  3. Click on the cell directly underneath
  4. Click Insert->Function
  5. Choose ChemFinder in the catagory list, it should be near the bottom.
  6. Choose ISSTRUCTURE from the function list on the right
  7. Click OK
  8. Move the resulting dialog box over a little so you can see the cells in the STRUCTURE column.
  9. Click the cell under the STRUCTURE cell.  The dialog box should show A2 in the edit box.  The "Formula Result" at the bottom of that box should read "TRUE"
  10. Click OK.  The first cell in the ISSTRUCTURE column should read TRUE
  11. Move the cursor over the little black square in the current cell, which is the first value in the ISSTRUCTURE column.  The cursor should change to a large white plus to a smaller black plus
  12. Click and drag the mouse down, which will create a box on the screen root at the first value in the ISSTRUCTURE column.  Drag down to last row which has a chemistry cell in it (sample.sdf ends at row 37) and let go of the mouse.  All of the cells in this column should now read TRUE in them.  If any of them is FALSE then there is a problem with reading in sample.sdf.

Picture Updating

When manipulating chemistry cells in ChemFinder for Excel, the pictures (if shown) should behave as if they are a part of the chemistry cell, not a seperate entity.   When a sort is performed the pictures stay with their cells.  When cells are hidden by filtering of hiding of the cells, the pictures do not show up.  When pasting a molecule with Paste Structure.  However, there are a few cases which are not covered.


Deleting a picture/chemistry when the text information is deleted

Since a chemistry cell is suppose to act like a normal cell, when the cell contents is removed with Edit->Delete or Edit->Cut, cell formulas referring to the cell shouldn't register any information in it.  And any picture associated with that cell should be removed.

Testing Suggestions:

Status: ready to test in 5.1d17


Delete a picture when the row or column that contains structure cells is deleted

When a row or column, which contains a structure cell that has a picture drawn, is deleted

Note:

Most of the time there is likely to be a small delay deleting the pictures from the screen.  The larger the number of structures in the spreadsheet (drawn or not) the longer it will take to remove the pictures.

Testing Suggestions:

Status: ready to test in 5.1d17


Drawing pictures for ranges of cells copied/pasted from Edit menu functions

ChemFinder for Excel's Cut Structure/Copy Structure/Paste Structure all handle single molecule cells.  And since we control the macros attached to those commands, they behave as they should.  However, when the user uses the standard Edit->Cut/Edit->Copy/Edit->Paste to cut/copy/paste ranges of cells, we don't trap on those errors.  When the structure cells have pictures drawn for them, this can cause a few problems:

Testing Suggestions:

Status: ready to test in 5.1d17


ChemDraw Editing

This feature allows the user to edit/add structures by double clicking on a cell.   Previously users had to use copy/paste to edit or add structures.

Note: ChemDraw editing from a ChemFinder for Excel cell  is more similar to OLE editing than what is used in ChemFinder Pro or the ChemFinder for Excel Filter Table or Similarity Table dialogs.  In these situations just giving focus back to the calling application will copy the data back to calling application automatically.   When editing from a ChemFinder for Excel cell, the user will have to either close the ChemDraw document, click File->Exit and return to Excel, or close ChemDraw.   However, the different between what ChemFinder for Excel does and what OLE normally does is the cannot do anything else in Excel while in an editting session escape cancel the editting session.  However, clicking File->Update will update the structure, and if a picture is drawn into the cell will update as well.

ChemDraw editting via a double-click only works in a ChemFinder for Excel worksheet.


Editing an existing structure

To use:

Feedback

Testing Suggestions:


Adding a new structure

When the user double clicks on a cell which doesn't have chemical information in it, Excel brings up a message that says: "Would you like add a molecule to the sheet?"  with a Yes and No button.  If the user presses Yes ChemDraw is launched.  When the user returns to Excel by closing the document window or clicking File->Exit and return to Excel or quits ChemDraw, which will return the user to Excel, the contents of the cell will be replaced and a new structure with a new name starting with "Structure".

To use:

Feedback:

Testing Suggestions:


Get ChemFinder List

Rather than import an entire ChemFinder database, which can be done via Import Table and choosing a .cfw file, this feature gives the user a chance perform a search in ChemFinder and then after creating the hitlist they desire importing those structures with data into Excel.

This command behaves different depending on the state of ChemFinder:

Testing Suggestions:

Status: ready to test in 5.1d17


Excel 2000 Support

ChemFinder for Excel should work identically with Excel 2000 as it does with Excel '97. Excel 2000 is not significantly different from Excel '97.

Testing Suggestions:


New Menu Commands

Convert SMILES To Molecule

This command should appear both on the ChemFinder menu and on the toolbar as "Convert SMILES To Molecule".  It converts cells that contain SMILES strings into ChemFinder for Excel structures with auto generated names, overwriting the previous contents of the cell.  If a string in a cell isn't considered to be valid SMILES sttring, it doesn't modify the contents of the cell.  If a user includes in the select ChemFinder for Excel chemistry cells, those cells are ignored, regardless of the contents of the cell.  When converting a SMILES string to a structure this command also has to supply coordinates and since CambridgeSoft products don't normally produce aromatic structures, convert any aromatic rings to a kekule representation.  

To use:

Feedback:

Testing Suggestions:

Status: ready to test in 5.1d17

Convert Name To Molecule

This command should appear both on the ChemFinder menu and on the toolbar as "Convert Name To Molecule".  It converts cells that contain names, stored in cells, acceptable to ChemDraw's Name=Struct  into ChemFinder for Excel structures.  The original name is still displayed, but the actual contents of the cell changed to the formula used to express ChemFinder for Excel names.  If a string in a cell isn't considered to be a name that Name To Struct can handle, it doesn't modify the contents of the cell.  If a user includes in the selection ChemFinder for Excel chemistry cells, those cells are ignored, regardless of the contents of the cell.  When converting a name to a structure this command also has to supply coordinates and since CambridgeSoft products don't normally produce aromatic structures, convert any aromatic rings to a kekule representation.

To use:

Feedback:

Testing Suggestions:

Status: ready to test in 5.1d17

Resize Picture

Pictures can be resized by selecting them with the mouse.  However, selecting multiple pictures with the mouse can be time consuming.

By using the Resize Picture command, a user can select the chemistry cells whose picture they want to resize.  Multiple cells can be selected at once.

Note:  When pictures are resized larger than their parent cells, an "Align Pictures" command must be executed to enlarge the cells.  "Align Pictures" will not make cells smaller.

To Use:

Testing Suggestions:

Status: ready to test in 5.1d17


Worksheet Function Changes

Added optional argument to CHEM.NUM.ATOMS

The CHEM.NUM.ATOMS worksheet function can now take an optional second parameter.  If the second parameter is left out, then this function performs as it always does:  it returns the total number of atoms in the molecule.  If the second parameter is present, it interprets the second parameter as an element's text symbol (ie "C" for carbon) and only returns the number of atoms of that element in the molecule.  If the second argument is a string which isn't found in the periodic table, then the function returns 0.

Usage:

=CHEM.NUM.ATOMS(structure, element)

structure - Cell reference of chemistry cell

element (optional)- String representing element symbol

Returns:

#N/A Error if 'structure' cell isn't valid ChemFinder for Excel Chemistry cell

if 'element' is not present, Count of total atoms in specified molecule

if 'element' is present, count of atoms of the type represented by the 'element' symbol.  0 is 'element' isn't a valid element symbol.

Testing Suggestions:

Status: ready to test in 5.1d17


Added CHEM.NUM.HBDONORS function

The CHEM.NUM.HBDONORS worksheet function returns the number of hydrogen bond donor candidates in a particular molecule.  It takes on parameter: the cell containing a structure.  If the cell isn't a valid structure, then the error code "#N/A" is returned.  A hydrogen bond donor candidate is defined as oxygens and nitrogens bearing one or more hydrogens, that are not negatively charged.

Usage:

=CHEM.NUM.HBDONORS(structure)

structure - Cell reference of chemistry cell

Returns:

#N/A Error if 'structure' cell isn't valid ChemFinder for Excel Chemistry cell

count of the number of hydrogen bond donor candidates

Status: ready to test in 5.1d17


Added CHEM.NUM.HBACCEPTORS function

The CHEM.NUM.HBACCEPTORS worksheet function returns the number of hydrogen bond acceptor candidates in a particular molecule.  It takes on parameter: the cell containing a structure.  If the cell isn't a valid structure, then the error code "#N/A" is returned. A hydrogen bond acceptor candidate is defined as oxygens and nitrogens that are not positively charged and not part of  esters or amides

Usage:

=CHEM.NUM.HBACCEPTORS(structure)

structure - Cell reference of chemistry cell

Returns:

#N/A Error if 'structure' cell isn't valid ChemFinder for Excel Chemistry cell

count of the number of hydrogen bond acceptors

Status: ready to test in 5.1d17


Added CHEM.COMPOSITION function

The CHEM.COMPOSITION worksheet function returns information about the ratios of the a molecule's elements' mass to the entire mass of the molecule.  It is similar to the CHEM.NUM.ATOMS worksheet function, in that it takes as its first argument the cell containing a structure and the second argument, which is optional, is a string representing an chemical element's symbol. 

If the second argument is omitted, the function returns a string which has pairs of element names and ratios, where the element and ratios are seperated by a comma and the pairs are seperated by semicolons.  The ratios are arranged from 0 to 100, with two decimal places of precision.  So a ratio of 0.9226 would be formatted in the string as 90.26. The elements are ordered as follows:

If carbon is present, carbon and hydrogen are presented first.  Otherwise elements are presented in alphabetical order.  For example, benzene would return: "C,92.26;H,7.74"

If the second argument is present, then this function returns a number which represents the mass of that element's ratio to the total mass of the molecule.  If the second argument doesn't represent a valid element symbol, then this function returns 0.  Note that numbers returned from functions are represented as decimals, ie 0.9226.  It is up to the user to format to their desires.   This is allowed to give the user flexibility in how these numbers are formatted, like most Excel functions.  If the number were similar to all atoms form (ie 92.26) and then the cell was formatted using the "Percent" format, it would come out as 9226.00%.

Usage:

=CHEM.COMPOSITION(structure, element)

structure - Cell reference of chemistry cell

element (optional)- String representing element symbol

Returns:

#N/A Error if 'structure' cell isn't valid ChemFinder for Excel Chemistry cell

if 'element' is not present, string representing the calculated ratios of elemental mass of each element in the structure to the mass of the entire structure.

if 'element' is present, the ratio of elemental mass of the specified element to the mass of the entire structure.

Testing Suggestions:

Status: ready to test in 5.1d17