Home

User`s Manual and Reference Guide

Contents

1. Sheet Sheet1l sheets ExcelSheets Sheet Sheetl Sheet Sheet2 Sheet Sheet3 ExcelResize ExcelResize range rows cols resizes range to the specified number of rows and columns ExcelResize range All resizes range to include all contiguous data Details None specifies that range should not be resized in that dimension All specifies that range should be extended to include all contiguous data in that dimension Examples lt lt ExcelLink ExcelResize A1l 10 None Range A1 A10 ExcelResize A1 10 10 Range Al J10 data Table Random 10 3 ExcelWrite A1 C10 data ExcelResize Al All Range A1 C10 ExcelResize Al All None Range A1 A10 ExcelClear A1 C10 92 ExcelLink ExcelResult ExcelResult is an internal function used to return lengthy results to Excel Details Options ExcelResult gives a list of options that affect how results are returned SetOptions ExcelResult opts can be used to specify these options Examples lt lt ExcelLink Options ExcelResult MaxCharacters gt 255 NumberMarks gt False expr Expand x 8 9 0 134218 1 50995 x 7 54975 x 22 0201 x 41 2877 xf 51 6096 x 43 008 x StringLength ToString InputForm expr 205 ExcelForm expr 0 134218 1 50995 x 7 54975 x 2 22 0201 x 3 41 2877 x 4 51 6096 x 5 43 008 x 6 23 04
2. Strings and floating point numbers can now be specified using the DATA worksheet function Obsolete in Version 3 The MATH worksheet functions provided in Version 2 have been superseded by a more flexible set of functions in Version 3 The MATH worksheet functions still work in Version 3 However there may be slight differences in how results are returned If you would like to convert your existing formulas here are examples of how they can be mapped Version 2 Version 3 MATH Inverse A1 B2 EVAL Inverse A1 B2 MATHEXACT Inverse A1 B2 EVAL InputForm EXPR Inverse A1 B2 MATHVOLATILE Random CALC EVAL Random MATHDEFINE m A1 C3 EVAL Set m A1 C3 MATHCODE B1 B10 initialization code lives is a code box no formula required MATHFORMULA amp CELL functions experimental functions no longer supported MATH function mappings ExcelLink 5 The ReadExcel function has been superseded by a new suite of Excel functions Here is an example of how an improved ReadExcel function could be defined using the new Excel functions ReadExcel file_ sheet_ rng_ Module book data If Not ExcelCheck ExcelBook file book ExcelOpen file data ExcelRead ExcelRange file sheet rng If ValueQ book ExcelClose book data This ReadExcel function checks to see if the required file is already open in Excel If not it opens the file reads the required data and closes the book if
3. 0 ExcelLink 2 1 15 asl Example Creating a button for a Mathematica macro Notes a The default macro name Initialization is reserved for code you want to run automatically every time you connect to a kernel or click Evaluate To move a button or a code box hold down the Ctrl key before selecting it Code boxes can be located anywhere in a workbook They do not need to be on the same sheet as the button calling the code Evaluating an Expression You can use the Mathematica Clipboard window to evaluate Mathematica expressions the same way you would in a Mathematica notebook This may be useful for example to quickly check the state of a variable or experiment with a function you are using for the first time To evaluate an expression in the Mathematica Clipboard window Click Clipboard on the Mathematica toolbar Type the expression into the Mathematica Clipboard window Click Evaluate Mathematica Clipboard Version Copy Paste Put Get Evaluate Close Typing an expression into the Mathematica Clipboard window ExcelLink 31 Once the Mathematica kernel has evaluated the expression the answer replaces the contents of the Clipboard window Mathematica Clipboard 5 2 for Microsoft Windows June 20 2005 Copy Paste Put Get Viewing evaluation results in the Mathematica Clipboard window To restore the original input expression click Rest
4. SFailed You can also create your own error messages to inform the user about what went wrong triplot ___ Module Message triplot args Failed triplot args Arguments are incorrect Arguments are incorrect 18 ExcelLink triplot 1 2 3 triplot args Arguments are incorrect SFailed Code Box Deployment Once you have developed a set of Mathematica functions you would like to use in Excel you can collect cells that define the functions in one place to make it easier to transfer the code to Excel If VersionNumber gt 6 Needs ComputationalGeometry Needs DiscreteMath gt ComputationalGeometry Clear triplot triplot usage triplot n plots a random triangulation of n points Options triplot Frame gt False GridLines gt None triplot n_ opts__ Rule Check Module data g rules data Table Random n 2 rules Sequence Join opts Options triplot PlanarGraphPlot data rules Failed triplot ___ Module Message triplot args Failed triplot args Arguments are incorrect To deploy this code as an Excel function you will need to copy the contents of the notebook cells that define the function to an initialization code box in an Excel workbook Here is how to do this 1 Create an initialization code box in Excel Click Macros on the Mathematica Toolbar a Click New and name the macro Initialization
5. Show In Out Names to temporarily hide input labels a Select the cells to copy To select noncontiguous cells hold down the Control key m Press Ctrl C or choose Edit Copy 3 Paste the Mathematica code into the Excel code box a Click inside the code box you just created Click and drag to select all existing contents of the code box Press Delete to delete the previous contents Press Ctrl V or choose Edit gt Paste Needs ExcelLink m Excel B3 C4 m Inverse m Rename Excel B3 C4 m Source Delete Close 24 ExcelLink To create a button for the Mathematica macro Select the name of the macro from the Available Macros list Click Button Select a location for the button and click OK For more information on using macros you create in Excel see Working with Macros Notes a When running macro code from inside Excel it is not necessary to load the Exce Link package However you can still include the line in your macro Using the code box approach you can create workbooks that have no dependencies on other files Package Deployment Mathematica notebooks can automatically generate an associated package file This provides an easy way to export a set of Mathematica commands that can be used as a one click workbook processing macro To create a package file follow the steps outlined for creating a package file outlined in the Package Deployment section of C
6. 102 ExcelLink ExcelDialogs Range Open Save Files Folder ExcelDialog Folder Select a folder C Documents and Settings Anton My Documents ExcelDirectories SExcelDirectories gives a list of supported special directories that can be used with the ExcelDirectory function Details Installation is the directory where Excel is installed Home is the default file location in Excel This is specified in Excel under Tools Options General User is the directory where Excel user settings are stored Link is the directory where Mathematica Link for Excel is installed Examples lt lt ExcelLink SExcelDirectories Installation Home User Link ExcelDirectory S ExcelDirectories C Program Files Microsoft Office Officel0 C Documents and Settings Anton My Documents C Documents and Settings Anton Application Data Microsoft C Documents and Settings Anton Application Data Mathematica Applications ExcelLink ExcelGraphic SExcelGraphic is an internal counter used to display graphics in Excel Examples lt lt ExcelLink ExcelGraphic 0 ExcelLink 103 g Plot Sin x x Pi Pi ExcelForm g ExcelGraphic 1 ExcelGraphic 1 ExcelLink SExcelLink gives the LinkObject being used to communicate with Excel Examples lt lt ExcelLink SExcelLink ExcellInstall LinkObject C Documents and Settings Anton Application Data
7. Although most Mathematica evaluations can be interrupted some cannot If the kernel does not respond to an abort request after some time you can choose to close the kernel to end the evaluation To close a kernel during an evaluation and return control to Excel Hold down Shift and click Evaluate on the Mathematica toolbar or a Right click the Mathematica kernel in the task bar and choose Close Notes Pressing the Esc key does not interrupt Mathematica evaluations The Esc key is used by Excel to send interrupts to local Visual Basic code Aborted formulas return NULL thereby suppressing evaluation of dependent formulas Closing a Link To close a link with a Mathematica kernel you can a Hold down the Shift key while clicking Evaluate Click Close under Mathematica Options gt Kernel a Evaluate Quit in the Clipboard window a function or a macro Notes The kernel is automatically closed whenever Excel is closed or the MathematicaLink add in is uninstalled ExcelLink 45 Sharing Workbooks Unlinking a Workbook Before sharing a workbook containing Mathematica formulas or macros you may first want to unlink the workbook Unlinking a workbook will allow others to take a look at the results in the workbook without launching a kernel encoun tering pathnames from your hard drive or inadvertantly replacing cell values with NAME errors To unlink a workbook Click Unlink on the Mathematica Op
8. Keyboard shortcut Ctrl Alt O 58 ExcelLink Help Establishes a connection with a Mathematica kernel Reinitializes and or recalculates the active workbook based on workbook options Closes or resets the connection with a Mathematica kernel depending on modifier key Interrupts Mathematica evaluation if an evaluation is currently running Adobe Reader is required to view this version of the manual a Contents of this manual can also be viewed in the Mathematica Documentation Center under Add Ons amp Links Mathematica Link for Excel Location Mathematica toolbar Mathematica menu Keyboard shortcut Ctrl Alt H ExcelLink 59 Context Commands Expression Toggle command Applies or removes the Text number format from the selected range Contents of the range are automatically reentered after the format change If the number format for all cells is Text the format for all cells is changed to General If the number format for all cells is not Text the format for all cells is changed to Text Location Context Menu Keyboard shortcut Ctrl Shift E Copy Toggle command Applies or removes the Text number format from the selected range If the number format for all cells is Text strings are copied as Mathematica text expressions If the number format for all cells is not Text strings are copied as Mathematica strings The copy command uses Mathematica InputForm to
9. Sheet Sheet1 Sheet Sheet2 Sheet Sheet3 sheet ExcelInsert Sheet Sheet Sheet4 ExcelSheets Sheet Sheetl Sheet Sheet2 Sheet Sheet3 Sheet ExcelDelete sheet Sheet4 98 ExcelLink ExcelSize ExcelSize range returns the size of range aS rows cols Examples lt lt ExcelLink ExcelSize A1 C10 10 3 ExcelStatus ExcelStatus text displays text in the status bar in Excel ExcelStatus restores the status bar to its default state Examples lt lt ExcelLink ExcelStatus Performing analysis Pause 2 ExcelStatus Generating report Pause 1 ExcelStatus ExcelTypeset ExcelTypeset expr opts specifies options for how expr should be typeset in Excel Details Excel id ExcelTypeset expr opts displays expr with specified options Excel id form expr displays expr in the specified form using default options Options ExcelTypeset gives a list of available options SetOptions ExcelTypeset opts sets the defaults Examples lt lt ExcelLink expr Expand a B 4 at 4a B 602 B2 4083 Bt ExcelLink 99 Excel B3 TraditionalForm expr Options ExcelTypeset ImageSize gt Automatic ImageMargins gt Automatic ImageFormat gt Automatic ImageResolution gt Automatic TextStyle gt Automatic Excel B3 ExcelTypeset expr ImageFormat gt WMF ImageSi
10. TraditionalForm expr This clears the displayed expression associated with cell B3 Excel B3 ExcelLink 9 Working in Mathematica General Principles Function Overview Functions in the Exce Link package follow the general convention ExcelMethod ExcelObject Four kinds of Excel objects are supported Here is a list of the objects with some methods that apply to them Book New Open Refresh Save Close Sheet Insert Rename Delete Activate Range Read Write Clear Resize Offset Filter Select Shape Insert Rename Delete Read Write Select For detailed information on all the objects and methods provided by the ExcelLink package see the Mathematica Ref erence section Shorthand Notation Objects Most objects in Excel can be referenced directly by a unique identifier The identifier is typically the name of an object or in the case of ranges the address If an identifier is unique among all object types you do not need to specify what kind of object it is Here a range object is provided as a typed object ExcelRead ExcelRange A1 B10 Here the range object is specified only by its address the identifier implicitly identifies it as a range ExcelRead A1 B10 You can use this type of shorthand referencing in any function that requires an Excel object Methods Read and write operations are so common a shorthand has also been provided for them and for clearing a range Needs
11. V 6 925561600569615 9 236380718950266 9 4 211237336171509 zi 4 618855611516693 9 9 65828768391891 10 5 898798653022922 9 Toroi eni eae ei 1 6444138620930293 9 1 504179784975939 9 7 908768797511767 9 Copy Paste Put Get Evaluate Close The Mathematica Clipboard window before clicking Evaluate ExcelLink 51 You can see that additional formatting characters are present in the data In this case the fixes are minor so pasting directly into Excel does in fact still work However you can also convert the contents of the Clipboard to InputForm by clicking Evaluate Mathematica Clipboard 6 925561600569615 9 5 236380718950266 9 4 211237336171509 93 4 618855611516693 9 9 65828768391891 10 5 898798653022922 9 4 267373885387862 9 1 6444138620930293 9 1 504179784975939 9 7 908768797511767 9 Copy Paste Put Get Restore Close The Mathematica Clipboard window after clicking Evaluate Once the data has been standardized to InputForm you can continue to paste it into Excel using the standard Paste command or the Paste button on the Clipboard window Notes An alternative to copying and pasting is to assign and retrieve Excel data programmatically from Mathematica This can be signifi cantly faster if you are dealing with large datasets For more information see Getting Started 52 ExcelLink Excel Worksh
12. for example 34 ExcelLink Working with Functions The Link Functions There are five worksheet functions provided by the Mathematica Link add in Function Use EVAL perform Mathematica evaluations EXPR build Mathematica expressions DATA specify an argument as native Excel data RULE build a Mathematica rule CALC force a function to respond to the Excel calculate command F9 The Link functions Together these functions can be used to build up expressions and perform evaluations in Mathematica in very flexible ways For more detailed information on each worksheet function see Excel Worksheet Functions Using the EVAL worksheet function you can call any function defined in Mathematica This immediately extends the number of functions available inside Excel from a few hundred to several thousand Excel syntax Mathematica syntax EVAL f arg1 arg2 f arg1 arg2 The EVAL function Example Result EVAL Simplify x 2 2x 1 1 x 42 EVAL Random Integer 1 6 an integer between 1 and 6 EVAL examples As shown Mathematica syntax must be wrapped in quotes when directly typed into an Excel formula If this is not done Excel s formula parser will try to interpret these as Excel syntax To avoid having to do this you can create references to arguments in Excel cells This is discussed in more detail in Specifying Arguments In its single argument form the EVAL function can also be used to evaluate
13. ExcelLink Excel A1 B10 Table Random 10 2 Excel A1 B10 0 395543 0 335494 0 934515 0 304868 0 495912 0 426754 0 657647 0 398919 0 800834 0 159929 0 216486 0 109539 0 694347 0 157112 0 0578395 0 273708 0 750935 0 710237 0 760348 0 420566 Excel A1 B10 10 ExcelLink The above three lines of shorthand code are equivalent to the following ExcelWrite ExcelRange A1 B10 Table Random 10 2 ExcelRead ExcelRange A1 B10 0 779186 0 105253 0 954368 0 320287 0 383643 0 769759 0 0198529 0 0154194 0 887731 0 343005 0 362206 0 6165 0 0868974 0 183076 0 14572 0 506961 0 39255 0 025964 0 0878807 0 233253 ExcelClear ExcelRange A1 B10 Notes If more than one object has the same identifier e g a shape has the same name as a sheet the identifier most likely to be used by the calling method is returned Object Notation Using full object notation can be useful when referring to an object by index or providing context for the object Here are a few examples ExcelSheet 1 ExcelSheet Book1 Sheet1 ExcelSheet Bookl1 1 ExcelRange Report xls 1 Al D100 When no context is provided the active context is assumed Excel object references are resolved when they are passed to a method not before Until then they are just Mathematica expressions representing a location in Excel Needs
14. This is discussed in the next section Notes If you are familiar with pure functions you will notice the pure function indicator is not used here There is no need because in this context it is clear that if signs are present a pure function is being specified For more information on creating and using pure functions refer to the tutorial Pure Functions 36 ExcelLink Specifying Arguments While arguments can be typed directly into a formula it is generally more convenient to specify arguments as the contents of a cell or a range of cells Editing values contained in cells is much easier than editing values embedded in a formula And if entered in a cell Mathematica syntax does not need to be wrapped in quotes Method Notes EVAL Random Integer 1 6 function and its arguments entered as a single expression EVAL Random Integer 1 6 function arguments passed individually EVAL Random C2 1 6 symbol Integer now provided by cell C2 EVAL Random C2 D2 E2 list 1 6 now provided by range D2 E2 Specifying range arguments Using the last form of the example you can easily change the upper and lower bound of the random number by changing cells D2 and E2 Also by typing Real in cell c2 you can change the type of random number returned A single column or single row of cells is interpreted as a one dimensional Mathematica list if a range has multiple rows and multiple columns it is returned as a 2D
15. 3 1S ee ee ND eS EVAL EC PlOobyex 2a 1x O obs ExcelGraphic 1 Mathematica contains thousands of functions If you know precisely which Mathematica function you wish to use and the arguments it takes you can type it directly into your spreadsheet as shown If you are unsure of the name of a Mathematica function or how to use it or want to explore the functions Mathematica has to offer click Functions on the Mathematica toolbar This will launch the Mathematica Function Wizard ExcelLink 27 The first step of the wizard is designed to help you find and learn about Mathematica functions Mathematica Function Browser Look In Look For Look Up Loaded Libraries X Names Containing Prime Look Up Prime n gives the nth prime number PrimeFactorList Function PrimePi Function Context System PrimePowerQ Function PrimeQ Function Options PrimeQCertificate Function PrimeQcCertificateCheck Function Attributes Listable Protected PrimeQMessages Symbol Primes Symbol 7 functions and 2 symbols found Preview Close lt Bact Next gt Enter The Mathematica Function Wizard step 1 The next step is designed to help you interactively specify arguments to the function Mathematica Function Arguments Function Prime Primefn Args a n No options available Preview Close lt Back Next gt Enter The Mathematica Function Wizard step 2 28 ExcelLi
16. Mathematica expressions such as symbols or operator forms of expressions Excel syntax Mathematica syntax EVAL expression expression The single argument form of EVAL ExcelLink 35 Example Result EVAL Version the version of the kernel you are running EVAL 7 5 5160 EVAL single argument examples Specifying Function Heads When specifying a Mathematica function the function does not necessarily have to be a named Mathematica function The function can also be specified as a nameless pure function Using pure functions you can use multiple functions to create a new function on the fly The syntax of Mathematica pure functions is as follows Slots for individual arguments are specified as 1 2 All arguments can collectively be inserted at one point using Here a pure function with two arguments is created in a step by step way Method Notes EVAL Sum 1 x 3 x 10 this sums the first 10 terms in the series EVAL Sum 1 x 3 x 41 10 number of terms is now specified as a pure function argument EVAL Sum 1 x 2 x H1 10 3 exponent of x is now specified as a second pure function argument Creating a pure function Note that as shown arguments do not have to appear in sequential order inside the function The index specifies which argument goes where Once values that may be edited have been specified as arguments a reference to the cells containing the values can be made
17. When menus are disabled if you right click a range the default Excel Context menu will appear a Location Mathematica toolbar Mathematica menu Keyboard shortcut Ctrl Alt X Messages Establishes a connection with a Mathematica kernel Reinitializes and or recalculates the active workbook based on workbook options Closes or resets the connection with a Mathematica kernel depending on modifier key Interrupts Mathematica evaluation if an evaluation is currently running Using this window you can use view messages generated by the Mathematica kernel a You can also locate the source of messages browse through past messages and save messages to a log file Note the window is nonmodal You can leave it open and still work in Excel Location Mathematica toolbar Mathematica menu Keyboard shortcut Ctrl Alt G Options Establishes a connection with a Mathematica kernel Reinitializes and or recalculates the active workbook based on workbook options Closes or resets the connection with a Mathematica kernel depending on modifier key Interrupts Mathematica evaluation if an evaluation is currently running You can browse this window to explore link options available to you If you change an option but want to leave the window open click Apply to apply the changes Note the window is nonmodal You can leave it open and still work in Excel a Location Mathematica toolbar Mathematica menu
18. formulas Controlling Recalculation Forcing Recalculation Excel typically recalculates formulas only when the inputs to a formula have been changed However when you change the definition of a function or the value of a symbol in Mathematica there is no way for Excel to automatically know about this change In this case you may want to force formulas to recalculate To force repeated recalculation of a specific formula Wrap the formula with the CALC function You can then trigger recalculation of that formula by pressing F9 Example Description CALC EVAL Randon gives a new random number each time F9 is pressed Marking a formula for repeated calcuation using the CALC function To force one time recalculation of the current selection Press Ctrl Shift or choose Recalculate from the Mathematica context menu To force recalculation of all link functions in the current workbook Click the Evaluate button This is an option specified under Mathematica Options Workbook 40 ExcelLink Disabling Recalculation By temporarily storing formulas as comments you can disable recalculation on a formula by formula basis You may want to do this to preserve currently calculated values or to suppress unwanted recalculation To comment link formulas in the current selection Press Ctrl Shift or check Comment from the Mathematica context menu To uncomment link formulas in the current selection Press Ctrl Shif
19. it was not already open ExcelLink Working in Mathematica Getting Started Loading the Package To start using the link from inside Mathematica you must first load the ExcelLink package lt lt ExcelLink The ExcelLink package provides a library of functions and symbols relating to Excel ExcelLink W ExcelLink Excel ExcelActivate ExcelAddress ExcelBook ExcelBooks ExcelCalculate ExcelCall ExcelCheck ExcelClear ExcelClose ExcelContext ExcelDate ExcelDelete ExcelDialog ExcelDirectory ExcelFilter ExcelForm ExcelFormat ExcelGraphic Excellnsert Excellnstall ExcelName ExcelNew ExcelObject ExcelOffset ExcelOpen ExcelOutput ExcelPosition ExcelRange ExcelRanges ExcelRead ExcelRefresh ExcelRename ExcelResize ExcelResult ExcelRun ExcelSave ExcelSelect ExcelShape ExcelShapes ExcelShare ExcelSheet ExcelSheets ExcelSize ExcelStatus ExcelTypeset ExcelUninstall ExcelUnshare ExcelWrite ImageFormat MaxCharacters ToExcel ExcelDialogs ExcelDirectories ExcelGraphic ExcelLink ExcelOutput ExcelResult ExcelShare ExcelTypeset You can learn more about these functions and symbols by looking up its entry in the Mathematica Reference section of this information You can also access this information within Mathematica s help system by looking under Add ons Assigning and Retrieving Data Needs ExcelLink The top level Excel function provides an easy way to specify a l
20. list of lists Specifying String Arguments To specify a string in Mathematica you can wrap a text argument with the DATA function The following methods return a list of Mathematica functions that end in Solve Method Notes EVAL Print DATA Hello evaluates Print Hello EVAL Print DATA A1 string now provided in cell A1 Specifying string arguments Specifying Numeric Data Mathematica evaluations are performed at the precision of the inputs provided If you would like the kernel to perform evaluations numerically at floating point precision wrap your inputs with the DATA function Method Notes EVAL Eigenvalues A1 C3 performs evaluation using symbolic or numeric methods depending on the inputs provided in A1 C3 EVAL Eigenvalues DATA A1 C3 always performs evaluation using numeric methods Specifying numeric arguments ExcelLink 37 Specifying Subexpressions Using the EXPR function you can build up multi function expressions for a single evaluation Method Notes EVAL f EXPR g 1 EXPR g 2 evaluates f g 1 g 2 Specifying subexpressions Specifying Options Mathematica functions may have defined options associated with them To specify an optional argument you can use the RULE worksheet function Method Notes EVAL ListPlot A1 B100 RULE PlotJoined D2 specifies an option using the value in cell D2 Specifying an option Generating Graphics The EVAL worksheet functions can al
21. no dialog appears and nothing works you may need to adjust your Excel macro security settings to permit the macros in the add in to run Refer to the Help menu in your version of Excel regarding how to change your macro security settings Once you have adjusted your security settings close Excel and try loading the add in again Once the add in is loaded the Mathematica toolbar will appear In Excel 2007 or later the Mathematica toolbar can be found under the Add Ins ribbon tab The Mathematica toolbar If you would like a Mathematica menu instead you can click the Options button on the toolbar All commands in the Mathematica menu are identical to those on the Mathematica toolbar The Mathematica menu can be useful if you would like to use the Alt key to access menu based commands Once you have installed the add in the first time you no longer need to use the Start menu shortcut Instead you can use Tools Add ins dialog to load or unload the Mathematica Link add in Excel checks the settings in this dialog each time it starts and automatically loads any checked add ins Add Ins available Analysis ToolPak Analysis ToolPak VBA 7 Conditional Sum Wizard Cancel Euro Currency Tools Internet Assistant VBA I Lookup Wizard PA Mathematica Link Solver Add in The Excel add ins manager To access the add ins manager in Excel 2007 or later click the Office button File menu to the upper l
22. represent the contents of the range If the Mathematica Clipboard window is open the window is automatically updated allowing you to view what has been placed on the Clipboard Location Context Menu a Keyboard shortcut Ctrl Shift C Paste Toggle command Applies or removes the Text number format from the selected range If the number format for all cells in the target range is Text the contents of the Clipboard will be pasted as Mathematica text expressions If the number format for all cells is not Text the contents of the Clipboard will be converted to native Excel data types wherever possible If the Mathematica Clipboard window is open you can view the text expression before it is pasted The Paste command requires the contents of the Clipboard to be in Mathematica InputForm Note If the expression on the Clipboard is not in InputForm you can use the Mathematica Clipboard window to convert the expression to InputForm by clicking Evaluate 60 ExcelLink Location Context Menu a Keyboard shortcut Ctrl Shift V Clear Toggle command Applies or removes the Text number format from the selected range a When using this command the number format for the range is reset to General This is the only difference between this command and the Excel standard Edit Clear Contents command Location Context Menu Keyboard shortcut Ctrl Shift Delete Function Toggle command Ap
23. reset Ctrl Shift click closes and restarts the kernel Hard kernel reset Location Mathematica toolbar Mathematica menu Keyboard shortcut Ctrl Alt E Functions Establishes a connection with a Mathematica kernel Reinitializes and or recalculates the active workbook based on workbook options Closes or resets the connection with a Mathematica kernel depending on modifier key Interrupts Mathematica evaluation if an evaluation is currently running If no link formula is selected the Wizard starts at the Mathematica Function Browser step If an existing link formula is selected the Wizard starts at the Mathematica Function Arguments step and the Wizard is populated with the existing formula Using the Wizard you can search for learn about and interactively build and edit Mathematica function calls Note the Wizard is nonmodal You can leave it open and still work in Excel Location Mathematica toolbar Mathematica menu Keyboard shortcut Ctrl Alt F 56 ExcelLink Macros Establishes a connection with a Mathematica kernel Reinitializes and or recalculates the active workbook based on workbook options Closes or resets the connection with a Mathematica kernel depending on modifier key Interrupts Mathematica evaluation if an evaluation is currently running Using this window you can create code boxes and corresponding buttons that allow you to run Mathematica code as if it were
24. shortcut Ctrl Shift Recalculate Toggle command Applies or removes the Text number format from the selected range To force recalulation of all Mathematica formulas on a sheet click the top left corner of the header rows between A and 1 to select the entire sheet To force recalculation of all Mathematica formulas in the workbook you can click Evaluate if your options are set to do so Location Context Menu Keyboard shortcut Ctrl Shift 62 ExcelLink Keyboard Shortcuts Toolbar Shortcuts Keyboard shortcuts are available for all commands on the Mathematica toolbar These toolbar shortcuts all include the Alt key Evaluate Ctrl Alt E Functions Ctrl Alt F Macros Ctrl Alt M Clipboard Ctrl Alt C Contexts Ctrl Alt X Messages Ctrl Alt G Options Ctrl Alt O Help Ctrl Alt H Keyboard shortcuts for Mathematica toolbar commands For descriptions of these commands see the Toolbar Commands section in this reference guide Context Shortcuts Keyboard shortcuts are available for all commands in the Mathematica context menu Context shortcuts all include the Shift key They all operate on the current selection in Excel Expression Ctrl Shift E Copy Ctrl Shift C Paste Ctrl Shift V Clear Ctrl Shift Delete Function Ctrl Shift F Array Ctrl Shift A Comments Ctrl Shift Recalculate Ctrl S
25. the contents of the range were not converted into a Mathematica list verify Mathematica keyboard shortcuts are enabled in Excel You can do this under Mathematica Options Interface By default text cells are copied as Mathematica strings Only cells formatted as Text are considered to contain Mathematica expressions See Strings If needed you can convert strings to expressions using ToExpression data Empty cells are copied as the symbol Empty If needed you can convert these to a desired default value such as 0 or using ReplaceAll data Empty gt val ExcelLink 49 Pasting Data to Excel To paste the contents of a Mathematica list to an Excel range a Ina Mathematica notebook select the list you wish to copy a Press Ctrl C or choose Copy from the Edit menu a Switch to Microsoft Excel and select where to insert the contents of the list If a single cell is selected data will be pasted below and to the right of that cell a Press Ctrl Shift V or right click and choose Paste if Mathematica contexts are enabled Notes If nothing was pasted verify Mathematica keyboard shortcuts are enabled in Excel You can do this under Mathematica Options Interface For best results convert your data to Mathematica InputForm before pasting the data into Excel You can do this in Mathematica or using the Mathematica Clipboard window in Excel See Fixing Problematic Data Be sure the opening and closing bracket
26. your own whenever you wish If you write Mathematica code that automates Excel to perform a task you may want the Mathematica code to initiate opening an instance of Excel The ExcelInstall function provides a way of doing this Needs ExcelLink This opens a visible instance of Excel if one is not already open ExcelInstall Visible gt True LinkObject C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Binaries ExcelLink exe 2 2 Once an Excel automation routine has been developed you can set Visible gt False instead This will open a hidden private instance of Excel to perform the requested tasks Importing Workbooks Needs ExcelLink This specifies a file to import data from f ToFileName ExcelDirectory Link Examples Stocks xls C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Examples Stocks xls This opens the file ExcelOpen f Book Stocks xls This returns the sheets in the file ExcelSheets Sheet IBM Sheet CSCO Sheet AAPL Sheet MSFT Sheet BLDP Sheet AIG Sheet ADP Sheet JNJ Sheet SYY Sheet WMT This reads in all the data from one of the sheets data ExcelRead ExcelSheet IBM This previews the first five rows of data from the sheet Take data 5 Date Open High Low Close Volume Adj Close 36 886 88 97 89 53 82 14 85 6 0523x10 84 47 3
27. 12 00 00 dd mmm yy hh mm ss Examples of Excel date formats For more examples of Excel number formats browse the Custom category in the Format gt Cells Number pane in Excel ExcelLink 69 ExcelLink Functions ExcelActivate ExcelActivate book makes book the active book ExcelActivate sheet makes sheet the active sheet Examples lt lt ExcelLink ExcelActivate ExcelSheet Book1 Sheet1 ExcelContext Book1 Sheet1 ExcelActivate Sheet3 ExcelContext Book1 Sheet3 ExcelActivate Sheet1 ExcelContext Book1 Sheet1 ExcelAddress ExcelAddress range returns the Al style address of the specified range Examples lt lt ExcelLink ExcelAddress 1 1 10 3 A1 C10 70 ExcelLink ExcelBook ExcelBook id represents a workbook identified by id The id can be Active a name a full path or an index Details The id can be one of the following Active currently active workbook Bookl1 or Report xls the name of the workbook C Reports Report xls the full path of the workbook lor 1 a positive or negative position index The first workbook opened has position 1 The last workbook opened has position 1 Once a workbook is saved the xls suffix is required to identify the workbook by name or path Examples lt lt ExcelLink ExcelBooks Book Bookl ExcelName ExcelBook Active Bookl ExcelNew Book
28. 6878 88 39 94 41 80 07 89 7 6938x10 88 45 36871 96 49 98 23 87 32 87 81 6 1856x10 87 27 36864 96 01 104 74 93 13 97 5 5668x10 96 4 ExcelLink This defines the data in the last column adjclose Rest Part data All 1 This plots the data ListPlot adjclose Joined gt True Frame gt True Axes gt False 1z0 100 60 60 0 25 50 75 100 1z5 150 This closes the workbook once you are finished importing data from it ExcelClose Exporting Workbooks Needs ExcelLink You can provide any format to an exported workbook by referencing an existing template file This specifies an existing template file to use for exporting the file f ToFileName ExcelDirectory Link Templates Report xls C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Templates Report xls This creates the new workbook based on the template file ExcelNew f Book Reportl The template contains a named range that defines where to put data in the report ExcelRanges Range Trials_Range It also contains a named shape to display a graphic ExcelShapes Shape Histogram Graphic This simulates rolling two six sided dice 500 times roll Random Integer 1 6 trials Table roll roll 500 This writes the trial data to the report ExcelWrite Trials_Range trials 14 ExcelLink This displays a histogram to
29. Al1 General ExcelWrite Al 5 ExcelFormat Al 0 0 ExcelFormat Al1 0 0 oe ExcelFormat Al hh mm ExcelFormat Al1 hh mm ExcelFormat A1 General ExcelFormat A1 General ExcelClear A1 ExcelLink 79 ExcelForm ExcelForm expr prints expr as it would appear in Excel in General format ExcelForm expr format prints expr as it would appear in the specified format Details The format can be specified using special characters such as 00 0 0 or yyyy mm dd hh mm ss Examples lt lt ExcelLink v 3 1 5 15 1 2 3 51 Hello Empty True False 1 f3 1 5x10715 3 5i Hello Empty True False 2 Map ExcelForm v 3 1 5E 15 0 5 3 5xI Hello TRUE FALSE n ExcelDate Date 38 909 6 ExcelForm n dd mmm yy hh mm AM PM 11 Jul 06 01 36 PM ExcelForm 5 7 0 0 71 4 ExcelForm 12345 12 345 ExcelGraphic ExcelGraphic graphic opts specifies options for how graphic should be displayed in Excel Details Excel id ExcelGraphic graphic opts displays graphic with specified options Excel id graphic displays graphic using default options Options ExcelGraphic gives a list of available options a SetOptions ExcelGraphic opts sets the defaults 80 ExcelLink Examples lt lt ExcelLink g Plot Sin x x Pi Pi Excel
30. B3 g Options ExcelGraphic ImageSize gt Automatic ImageMargins gt Automatic ImageFormat gt Automatic ImageResolution gt Automatic TextStyle gt Automatic Excel B3 ExcelGraphic g ImageFormat gt GIF ImageSize gt 400 300 ImageMargins gt 20 TextStyle gt FontFamily gt Arial FontSize gt 9 Excel B3 Excellnsert ExceliInsert inserts a sheet in the active book Excelinsert name inserts a sheet with the specified name ExceliInsert ExcelSheet id inserts the specified sheet ExceliInsert ExcelShape id inserts the specified shape Details The id can be a name or Automatic Option Position can be used to specify the position where the new object is to be inserted a For sheets Position can be an existing sheet name or index For shapes Position can be a top left coordinate pair Option Format can be used to specify the format of the inserted object a For sheets Format can be Work Chart or a reference to an existing sheet to use as a template For shapes Format can be Image Text or a reference to an existing shape to use as a template ExcelLink sl Examples lt lt ExcelLink ExcelSheets Sheet Sheetl Sheet Sheet2 Sheet Sheet3 ExceliInsert Sheet Sheet4 ExcelSheets Sheet Sheetl Sheet Sheet2 Sheet Sheet3 Sheet Sheet4 ExcelDelete ExcelSheet 1 ExcelSheets Sheet Sheetl Sheet Sheet2 S
31. Book2 ExcelBooks Book Bookl Book Book2 ExcelName ExcelBook 1 Bookl ExcelName ExcelBook 1 Book2 ExcelClose ExcelBooks Book Book1l ExcelCheck ExcelBook 2 False ExcelLink 71 ExcelBooks ExcelBooks gives a list of workbooks currently open in Excel Examples lt lt ExcelLink ExcelBooks Book Book1 ExcelNew Book Book2 ExcelBooks Book Bookl Book Book2 ExcelClose ExcelCalculate ExcelCalculate causes all formula based calculations in Excel to update Details This function can be useful if Excel has been set to manual calculation mode or if volatile functions such as random number generators are being used Examples lt lt ExcelLink ExcelWrite Al RAND ExcelRead A1 0 0145436 ExcelCalculate ExcelRead A1 0 0438649 ExcelClear A1 ExcelCall ExcelCall is an internal function called by all functions that need to communicate with Excel 72 ExcelLink ExcelCheck ExcelCheck object checks if object is valid ExcelCheck object format checks if object has the specified format Details m ExcelCheck always returns True or False If object is a shape the format can be Image Text or Other u If object is a sheet the format can be Work or Chart If object is a range the format can be General Text a number format or
32. ExcelLink ExcelSheet My Sheet Sheet My Sheet ExcelRead ExcelSheet My Sheet ExcelRead source Sheet My Sheet is not a valid range shape or sheet Failed If you want to see if an object reference is valid you can do so by using the ExcelCheck function ExcelCheck ExcelSheet My Sheet False You can return collections of objects as a list by using the plural of an object name ExcelSheets Sheet Sheetl Sheet Sheet2 Sheet Sheet3 You can extract the name of a returned object ExcelName First ExcelSheets Sheet1 ExcelLink Il You can also return the embedded context information for the objects ExcelContext First ExcelSheets Book1 Expression Cells If the Number Format of an Excel cell is set to Text the contents of the cell are considered to be a Mathematica expression when transferring them to Mathematica via the clipboard or in a macro For more information see Strings in the Excel Reference guide Notes Cells should be formatted as Text before entering an expression To convert existing contents to Text you can reenter them manually or use the provided Expression command from the Mathematica Context menu Expressions such as 1 2 or x can only be entered in cells formatted as Text Otherwise Excel will attempt to interpret them as something else a When working with expression cells all cells in the range should be formatted as Text Partial
33. Mathematica Applications ExcelLink Binaries ExcelLink exe 2 2 SExcelLink LinkObject C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Binaries ExcelLink exe 2 2 ExcelUninstall SExcelLink ExcelOutput SExcelOutput is an internal counter used to display formatted output in Excel Examples lt lt ExcelLink SExcelOutput 0 104 ExcelLink expr BaseForm 10 2 1010 ExcelForm expr ExcelOutput 1 SExcelOutput 1 ExcelResult SExcelResult is an internal counter used to return lengthy results to Excel Examples lt lt ExcelLink ExcelResult 0 expr Expand x 2 100 ExcelForm expr ExcelResult 1 ExcelResult 1 ExcelShare SExcelShare gives the LinkObject being used to share a kernel session with Excel Examples lt lt ExcelLink ExcelShare LinkObject ExcelShare 2 2 ExcelShare LinkObject ExcelShare 2 2 ExcelLink 105 ExcelTypeset SExcelTypeset is an internal counter used to display typeset equations in Excel Examples lt lt ExcelLink SExcelTypeset 0 expr TraditionalForm Expand x 1 2 5 Se Soe Sas Sa 1 X 2 2 4 16 32 ExcelForm expr ExcelTypeset 1 SExcelTypeset 1
34. Mathematica Link for Excel 3 6 User s Manual amp Reference Guide Copyright 2014 Wolfram Research Inc Table of Contents COSA E eR eae ane ne SDC er SEERE Cnn OD en on IE OSD a CR SSCA OE NO aE on ORR E 1 Feature S gcc ta canes cosaeeediactmedeapestvesseeamos coaeeecgucstusecn pavsvenlachmoscduasbe lt ciastmeccn ests sstastincestumexcduastmeenpastees 1 Changes esas eases eae A A seca eats ae Sees A A O 2 Working in Mathematica G tting Started oreroraa a E T AAE EE onde tas ota sade E EE 6 General Principles cccceceeeesseeeeescececcecccccccccscscsceccecesecueueuuuusescssussssessssesseecseecececeeeeeseees 9 Automating EXE si5ci cass ch usasetesssasctsecedttanaedebauds vubsace auasihe E E a civ tassel eansndeheees 12 CreatingiExcel FUN CONS reier E ATEO EA EAA E E E 15 Creating Excel Matros csiciissississsyeoseisevdesioivessaasegnetesge0b5s5 E EERI EEE S EE 21 Working in Excel Getting Started st ssicesca resets cocessstossh EA 25 General Prine ip eS a aaa ra a Sa r aaia 32 Working with Functions ccccccccccccecsssssseceeeceeeceessessseceeseecesesessnsaeeeccesesesenssaeeeeeeceseneneaaees 34 Working with Macros i4 o6is sees lt acksisercasusenscndennsstangus IAAT A E ET AS 41 Link Management secesie a a aN 4 Sharing Workbooks ccccccccccececsesssssssececeecccsceeesessaussecseccecceccesecsnsaeeeseeseceeseesensssseeaseeeeeesens 45 Using the Clipboard LOAGING tHE Add in oni
35. Mixed Examples lt lt ExcelLink object ExcelRange Sheet1 A1 Range Al ExcelCheck object True object ExcelRange Bogus A1 Range Al ExcelCheck object False object ExcelSheet 1 Sheet 1 ExcelCheck object Work True ExcelCheck object Chart False ExcelLink 73 ExcelClear ExcelClear range clears data from the specified range in Excel ExcelClear sheet clears all data contained in the specified sheet Details ExcelClear All and ExcelClear Active both clear all data on the active sheet Examples lt lt ExcelLink ExcelWrite A A Path ExcelClear A A ExcelWrite Sheet1 Table Random 1000 10 ExcelClear Sheet1 ExcelClose ExcelClose closes the active workbook ExcelClose book closes the specified book Examples lt lt ExcelLink book ExcelNew Book Book2 ExcelBooks Book Bookl Book Book2 ExcelClose book ExcelBooks Book Book1 74 ExcelLink ExcelContext ExcelContext returns a list identifying the current active context ExcelContext object returns a list identifying the context of the specified object Examples lt lt ExcelLink ExcelActivate Book1 ExcelActivate Sheet1 ExcelContext Book1 Sheet1 object ExcelRange Book1 Sheet2 A1 C10 Range A1 C10 ExcelContext object Book1 Sheet2 ExcelDate Ex
36. This is the default if no other macros exist in your workbook Select a location for the code box and click OK 2 Copy the code from Mathematica Use Kernel gt Show In Out Names to temporarily hide input labels a Select the Input cells to copy To select noncontiguous cells hold down the Ctrl key a Press Ctrl C or choose Edit Copy 3 Paste the Mathematica code into the Excel code box Click and drag inside the code box to select all existing contents a Press Delete to delete the previous contents Press Ctrl V or choose Edit gt Paste ExcelLink 19 You can now use the Mathematica function you created inside Excel B4 X amp EVAL triplot B3 A yc D E F G 20 ExcelGraghic 1 CEES Ch a Kl la a Using the code box approach you can create workbooks that have no dependencies on other files Package Deployment Mathematica notebooks can automatically generate an associated package file This provides an easy way for you to export a set of Mathematica function definitions you would like to use in an Excel workbook With this in mind on the Excel side the MathematicaLink add in checks for a package file with the same name in the same directory when initializing a workbook If one is found the code in the file is considered the initialization code for the workbook Here is how to create a package file from a notebook then use the contents of the package file as initialization code in a wor
37. Write fills sheet ExcelWrite displays ExcelWrite displays ExcelWrite displays ExcelWrite displays ExcelWrite range data writes data to the specified range in Excel sheet data with data range graphic a graphic at the specified range location range form the expression form at the specified range location shape graphic a graphic to the specified shape shape form the expression form to the specified shape shape text writes text to the specified shape Details If data does not completely fill the range remaining cells are cleared Examples lt lt ExcelLink ExcelWrite Al 1 ExcelWrite A1 A3 1 2 3 ExcelWrite A1 C1 1 2 3 Excelwrite Al C3 1 2 3 4 5 6 7 8 9 ExcelWrite A1 C3 1 2 3 4 ExcelLink 101 data Table Random 30 30 ExcelWrite Sheet1 data ExcelClear Sheet1 ExcelWrite Al ListContourPlot data ContourLines gt False ExcelClear A1 ImageFormat ImageFormat is an option to ExcelGraphic ExcelTypeset and ExcelOutput MaxCharacters MaxCharacters is an option to ExcelResult ToExcel ToExcel is an internal function used by the link to convert expressions into a form suitable for use in Excel ExcelDialogs SExcelDialogs gives a list of supported named dialogs that can be used with the ExcelDialog function Examples lt lt ExcelLink
38. an Excel macro Using this window you can also create initialization code boxes for workbooks that contain code that is run automati cally when you connect to a kernel or reevaluate the workbook Note the window is nonmodal You can leave it open and still work in Excel a Location Mathematica toolbar Mathematica menu Keyboard shortcut Ctrl Alt M Clipboard Establishes a connection with a Mathematica kernel Reinitializes and or recalculates the active workbook based on workbook options Closes or resets the connection with a Mathematica kernel depending on modifier key Interrupts Mathematica evaluation if an evaluation is currently running a Using this window you can interactively copy and paste data to Excel ranges You can also type code directly into the Mathematica Clipboard window evaluate it and view the results Note the window is nonmodal You can leave it open and still work in Excel a Location Mathematica toolbar Mathematica menu a Keyboard shortcut Ctrl Alt C Contexts Establishes a connection with a Mathematica kernel Reinitializes and or recalculates the active workbook based on workbook options Closes or resets the connection with a Mathematica kernel depending on modifier key Interrupts Mathematica evaluation if an evaluation is currently running When menus are enabled if you right click a range a custom Mathematica Context menu will appear ExcelLink 57
39. ange Open Save Files Folder ExcelDialog Range Range B2 C6 ExcelDialog Open C Documents and Settings Anton Desktop Excel Data Types xls ExcelDialog Save C Documents and Settings Anton Desktop New File xls ExcelDialog Files C Documents and Settings Anton Desktop Manager xls C Documents and Settings Anton Desktop Temp xls C Documents and Settings Anton Desktop Test xls ExcelDialog Folder C Documents and Settings Anton My Documents ExcelDirectory ExcelDirectory name gives a directory specified by name ExcelDirectory book gives the directory of the specified book Details Supported named directories are given by ExcelDirectories If book is unsaved its directory is given as None Examples lt lt ExcelLink ExcelDirectory Installation C Program Files Microsoft Office Officel0 s ToFileName ExcelDirectory Link Examples Stocks xls C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Examples Stocks xls ExcelLink 77 book ExcelOpen s Book Stocks xls ExcelDirectory book C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Examples ExcelClose book ExcelFilter ExcelFilter range filter filter applies filters to header fields in range ExcelFilter range None turns off all filters for the specified range Details The first filter is applied to the f
40. anges or when the F9 key is pressed a Nonvolatile formulas recalculate only when a referenced input cell changes Excel functions RAND and Now are examples of built in volatile functions Volatile functions are typically used to run simulations in Excel You can use the CALC worksheet function to use Mathematica generated random numbers within Excel based simulations Example CALC EVAL Random Integer 1 6 You can use the CALC worksheet function to tag specific functions to respond when F9 is pressed or when ExcelCalculate is called from Mathematica code Example CALC EVAL Set stockprice G10 Note volatile functions recalculate frequently and can therefore significantly slow down recalculation and editing operations in your spreadsheet To avoid any potential problems use volatile functions sparingly and if needed disable them when making modifications to your spreadsheets See also EVAL ExcelLink 55 Toolbar Commands Evaluate Establishes a connection with a Mathematica kernel Reinitializes and or recalculates the active workbook based on workbook options Closes or resets the connection with a Mathematica kernel depending on modifier key Interrupts Mathematica evaluation if an evaluation is currently running Workbook options can be specified under the Mathematica Options Workbook tab Shift click closes the kernel Ctrl click clears the kernel s Global context Soft kernel
41. ape Details If range includes trailing empty cells they are ignored Examples lt lt ExcelLink ExcelWrite A1 C3 IdentityMatrix 3 ExcelRead A1 C3 1 0 O O le O O 0 1 ExcelRead A C 1 0 O 0 le 0 O 0 1 ExcelRead Sheet1 1 0 O 0 le O O 0 iF ExcelClear A1 C3 90 ExcelLink ExcelRefresh ExcelRefresh refreshes all external data queries and pivot tables in the active book ExcelRefresh book refreshes the specified book Details External data sources can include text files web pages or databases To set up an external data query in Excel use the Data Import External Data commands Examples lt lt ExcelLink s ToFileName ExcelDirectory Link Examples Stocks xls C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Examples Stocks xls ExcelOpen s Book Stocks xls ExcelRefresh data ExcelRead ExcelSheets ExcelClose s ExcelRename ExcelRename sheet name renames the specified sheet ExcelRename shape name renames the specified shape Examples lt lt ExcelLink sheets ExcelSheets Sheet Sheetl Sheet Sheet2 Sheet Sheet3 ExcelRename Sheet1 Summary Sheet Summary sheets ExcelSheets Sheet Summary Sheet Sheet2 Sheet Sheet3 ExcelLink ExcelRename Summary Sheet1
42. atica Context menu Expressions such as 1 2 or x can only be entered in cells formatted as Text Otherwise Excel will attempt to interpret them as something else When working with expression cells all cells in the range should be formatted as Text Partial expression ranges are not currently supported From Mathematica you can use the ExcelFormat function to apply or unapply Text format to a range Data Cells If the Number Format of an Excel cell is anything other than Text the cell is considered a data cell When transferring the contents of data cells from Excel to Mathematica a Data is transferred as it is natively stored in Excel This means for example all numbers will be returned as floating point doubles This includes dates that are numbers with special formatting properties When transferring Mathematica expression to Excel data cells Non native expressions are converted to an equivalent Excel data type whenever possible Expressions that do not have any possible Excel equivalent are converted to InputForm strings See the Data Types overview for more details Notes You can use the ExcelDate function and ExcelForm functions to work with date values once you get them into Mathematica Cell references in Excel based formulas such as EVAL A1 A2 A3 are an exception to this rule In this case you must wrap formula arguments with the provided DATA function to treat them as data cells EVAL StringJoin DATA A2 DATA A3
43. board Select where to put the code in the code box Press Ctrl V or choose Edit gt Paste Developing in Mathematica If you are familiar with the Mathematica notebook front end you can develop code in that environment then transfer it to a code box in Excel For more information on developing macros in Mathematica see Creating Excel Macros Protecting Your Code Once you have developed a macro you may want to protect your code This can be done by hiding or protecting the sheet containing the code To hide a sheet in Excel Click Format gt Sheet gt Hide To protect a sheet in Excel Click Tools Protection Protect Sheet See Excel s Help menu for more information on these commands 44 ExcelLink Link Management Opening a Link The first time you request a Mathematica evaluation in Excel a link to a Mathematica kernel will open automatically If you would like to force a link to open even if there is nothing to evaluate you can a Click the Evaluate icon on the Mathematica toolbar You can manually specify a kernel to connect to using the Mathematica Options Kernel tab Settings you specify will be used automatically the next time you connect Notes If you do not have a typical Mathematica installation you may be required to specify a kernel the first time you use the link Interrupting Evaluations To interrupt a Mathematica evaluation Click Evaluate on the Mathematica toolbar
44. celDate value converts an Excel date value to a date list y m d h n s ExcelDate y m d h n s converts a date list to an Excel date value Details a Excel Time of day information is provided by the fractional part of the date where 0 5 represents noon Excel dates can be printed in various formats using the ExcelForm function Examples dates count days from December 30 1899 lt lt ExcelLink d Date 2006 7 11 13 8 48 6900112 n ExcelDate d 38909 5 ExcelLink 75 n InputForm 38909 54778576389 ExcelForm n dddd mmm d yyyy Tuesday Jul 11 2006 ExcelForm n h mm AM PM 1 08 PM ExcelDate n 2006 7 11 13 8 48 69 ExcelDelete ExcelDelete sheet deletes the specified sheet ExcelDelete shape deletes the specified shape Examples lt lt ExcelLink ExcelSheets Sheet Sheetl Sheet Sheet2 Sheet ExceliInsert Sheet Sheet Sheet4 ExcelSheets Sheet Sheetl Sheet Sheet2 Sheet ExcelDelete ExcelSheet 1 ExcelSheets Sheet Sheetl Sheet Sheet2 Sheet ExcelDialog Sheet3 Sheet3 Sheet Sheet4 Sheet3 ExcelDialog type displays a dialog specified by type ExcelDialog type title displays a dialog with a custom title 76 ExcelLink Details Supported dialog types are given by ExcelDialogs Examples lt lt ExcelLink ExcelDialogs R
45. celRange A1 C5 ExcelAddress ExcelRange Selection A1 C5 ExcelAddress ExcelRange 1 1 Al ExcelAddress ExcelRange 1 1 5 3 A1 C5 ExcelSize ExcelRange Al1 65536 256 ExcelRanges ExcelRanges gives a list of named ranges in the active sheet ExcelRanges sheet gives a list for the specified sheet ExcelRanges book gives a list of all named ranges in book Examples lt lt ExcelLink s ToFileName ExcelDirectory Link Examples Stocks xl1s C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Examples Stocks xls book ExcelOpen s Book Stocks xls ExcelRanges Range Stock_History ExcelLink 89 ranges ExcelRanges book Range Stock_History Range Stock_History Range Stock_History Range Stock_History Range Stock_History Range Stock_History Range Stock_History Range Stock_History Range Stock_History Range Stock_History ExcelContext ranges Stocks xls AAPL Stocks xls ADP Stocks xls AIG Stocks xls BLDP Stocks xls CSCO Stocks xls IBM Stocks xls JNJ Stocks xls MSFT Stocks xls SYY Stocks xls WMT ExcelClose book ExcelRead ExcelRead range reads data from the specified range in Excel ExcelRead sheet reads all data contained in the specified sheet ExcelRead shape reads the contents of the specified sh
46. e a button that will call the code in the box The Mathe matica Macros manager can help you do this To create a macro code box Click Macros on the Mathematica toolbar Click New and specify a name In this case name the macro Example Select where to place the code box Click OK ExcelLink 29 The Example macro is added to the list Available Macros and a code box for the Example macro is inserted at the location you specified A Mmm c o E F c H Available Macros Button ttt Example ttt This template is custo Rename Define Inputs Source m Excel B3 C4 Delete Perform Analysis m Inverse m Close Return Results Excel B3 C4 m NMINI NININ N d d d d md md d ee amass Bea aaa aR aaeeei Running Mathematica code as an Excel macro The initial contents of the code box are specified by a customizable template In this case you can leave the default code as is Before running the code however you should type values into the cells referenced in the macro To run a macro Select the macro from the available macros list a Click Run To make it more convenient to run the macro you can create a button for the macro To create a macro button Select the macro from the available macros list Click Button a Select where to place the button Click OK we
47. ect a format in the Format gt Cell gt Number pane or use the Expression toggle command provided by the MathematicaLink add in Notes For worksheet functions such as EVAL or EXPR inputs are a ways assumed to contain text expressions even if Text formatting has not been applied to input ranges This assumption makes it easier to build Mathematica expressions To use data as it is natively stored in Excel wrap the reference to the data range with the DATA worksheet function When a number is stored as text in Excel an error flag may appear on the cell If you find these flags distracting you can turn this type of error checking off in the Tools Options Error Checking pane ExcelLink 65 Numbers Excel stores all numbers as machine precision doubles A number may appear to be an integer in Excel when no decimal point is displayed however internally the number is stored as a floating point Whether a decimal point is displayed is a matter of formatting See Excel Number Formats for more information Maximum and minimum numbers that can be natively stored in Excel are defined by machine precision limits MaxMachineNumber 1 7976931348623157 308 MinMachineNumber 2 2250738585072014 308 Excel number limits Outside of this range numbers can be stored as Mathematica text expressions Dates Excel dates are stored as numbers where the integer part represents the day and the fractional part represents the time o
48. eet Functions EVAL EVAL expr evaluates expr in a Mathematica kernel EVAL head arg1 arg2 builds an expression from the provided head and args and evaluates it EVAL head arg1 arg2 is equivalent to EVAL EXPR head argl arg 2 You can use EXPR head arg1 arg2 to see exactly what will be evaluated in Mathematica EVAL head argl arg2 evaluates only once head and all arguments have been provided If head or any argument refers to an empty cell or an error evaluation is suppressed and N a is returned See notes for EXPR See also DATA RULE EXPR EVAL expr evaluates expr in a Mathematica kernel EVAL head arg1 arg2 builds an expression from the provided head and args and evaluates it EXPR head argl arg2 builds the Mathematica expression head arg1 arg Example EXPR f x y returns x y You can build any expression using nested EXPR calls Example EXPR Sqrt EXPR Plus EXPR Power Al 2 EXPR Power A2 2 If head contains head is automatically assumed to be a pure function Example EXPR Sqrt 1 2 2 2 A1 A2 EXPR head arg arg2 returns N A if head or any arguments refer to an empty cell or an error EXPR range trims empty cells and errors from the end of range See also EVAL DATA RULE ExcelLink 53 DATA EVAL expr evaluates expr in a Mathematica kernel EVAL
49. eft corner click Excel Options then under Add ins next to the Manage Excel Add ins dropdown box click Go 26 ExcelLink Entering a Function Once the Mathematica Link add in is loaded you are ready to perform Mathematica calculations inside Excel One way of doing this is using the EVAL worksheet function This worksheet function allows you to call any Mathematica function from within an Excel formula Try entering the simple Mathematica function Prime in an empty worksheet cell This is done as shown Formula Result EVAL Prime 100 541 The value returned is the 100 prime number International verisons of Excel may require semicolon separated formulas such as EVAL Prime 100 a During your first calculation a Mathematica kernel will be launched as a computation server for Excel This new process may appear in your Windows task bar Now try creating an interactive prime number calculator by specifying a cell reference as an argument Formula Result EVAL Prime A1 HN A Unless you have already entered a value in cell a1 the function returns unevaluated and displays an error code The N A error code indicates that one or more inputs to the formula are not available Type any integer you choose in cell Al and a prime number will be calculated for you Here are some other examples Formula Result EVAL Factorial 10 3628800 EVAL Det A1 C3 determinate of matrix in cells A1 C3 EVAL Expand x 1
50. elShape Rectangle 1 shape2 ExcelInsert ExcelShape Rectangle 2 ExcelShapes Shape Rectangle 1 Shape Rectangle 2 ExcelName ExcelShape 1 Rectangle 1 ExcelName ExcelShape 1 Rectangle 2 ExcelSelect ExcelShape 1 ExcelName ExcelShape Selection Rectangle 1 ExcelCheck ExcelShape 5 False ExcelDelete shapel1 ExcelDelete shape2 ExcelShapes ExcelShapes gives a list of supported shapes in the active sheet ExcelShapes sheet gives a list for the specified sheet Excel1Shapes book gives a list of all supported shapes in book Examples lt lt ExcelLink ExcelShapes shape ExcelInsert ExcelShape Rectangle 1 Shape Rectangle 1 ExcelShapes Shape Rectangle 1 ExcelDelete shape 96 ExcelLink ExcelShare ExcelShare starts kernel sharing with Excel on a link named ExcelShare Details You must have the Mathematica Link add in loaded in Excel to share a kernel with Excel ExcelShare must be called before connecting from Excel In Excel if your Mathematica Connection is set to Automatic the Mathematica Link add in will first try to connect to a link named ExcelShare and then launch its own kernel if a link with that name is not available In Excel if your Mathematica Connection is set to Shared the Mathematica Link add in will try to connect to a link ExcelShare and fai
51. expression ranges are not currently supported From Mathematica you can use the ExcelFormat function to apply or unapply Text format to a range Data Cells If the Number Format of an Excel cell is anything other than Text the cell is considered a data cell When transferring the contents of data cells from Excel to Mathematica Data is transferred as it is natively stored in Excel This means for example all numbers will be returned as floating point doubles This includes dates that are numbers with special formatting properties When transferring Mathematica expressions to Excel data cells Nonnative expressions are converted to an equivalent Excel data type whenever possible Expressions that do not have any possible Excel equivalent are converted to InputForm strings See the Data Types Overview for more details Notes a You can use the ExcelDate function and ExcelForm functions to work with date values once you get them into Mathematica Cell references in Excel based formulas such as EVAL A1 A2 A3 are an exception to this rule In this case you must wrap formula arguments with the provided DATA function to treat them as data cells EVAL StringJoin DATA A2 DATA A3 for example 12 ExcelLink Automating Excel Opening Excel By default the Exce Link package automatically connects as needed to an open instance of Excel This provides easy on demand connectivity In this mode you can open or close Excel on
52. f day 0 0 December 30 1899 beginning of day 2 0 January 1 1900 beginning of day 2 January 1 1900 midday 36526 0 January 1 2000 beginning of day 3615265 January 1 2000 midday How Excel stores date and time information What makes a number appear as a date in Excel is a matter of formatting See Excel Number Formats for more information Notes a Excel cells only format dates properly after March 1 1900 Excel erroneously considers 1900 a leap year and negative numbers cannot be formatted as dates in Excel The ExcelForm function does not suffer from the same restrictions as Excel cells and can therefore be used to print out dates before March 1 1900 Strings In Excel textual data can be entered into cells specifically formatted as Text It can also be entered into cells with other formats such as General MathematicaLink for Excel uses this formatting difference to distiguish between text expressions and strings If a cell is formatted as Text the content of the cell is considered to be a Mathematica expression otherwise the contents are considered to be a Mathematica string xyz Text format xyZ xyz other format xyz Excel string mapping 66 ExcelLink In Excel the two versions of the string appear identical However there are significant behavioral differences between text cells and other cells Strings assigned to nontext cells pass through an Excel interpreter The interpreter chec
53. h the workbook to enable others to interact with the workbook During development be sure to save changes to your Mathematica notebook in order to update the package file before using it from the Excel side ExcelLink 21 Creating Excel Macros Developing Macros Setting Up a New Notebook When developing Mathematica code it is best to separate input and output definitions from the main analysis portion of the routine This way your analysis code can be easily adapted to obtain inputs and send outputs anywhere Here is a sequence of Mathematica commands that performs some analysis This section defines inputs m 1 2 3 4 This section performs your analysis m Inverse m This section displays outputs m 2 1 1 5 0 5 To use this code as an Excel macro you only need to load the Exce Link package to modify the input and output sections Before doing this open Excel and type in the same inputs into the workbook locations indicated in the following This section loads required packages Needs ExcelLink This section defines inputs from Excel m Excel B3 C4 This section performs your analysis m Inverse m This section returns outputs to Excel Excel B3 C4 m In this example the input and output range is the same This is a way of performing in place evaluation Modifying an Existing Notebook To convert an existing Mathematica notebook to be used as an Excel mac
54. he formula bar 38 ExcelLink Generating Messages If a message is generated during a kernel evaluation it is sent to the Mathematica messages window Here are some evaluations that generate kernel messages Example Result EVAL 7 Failed or HVALUE EVAL 1 0 ComplexInfinity EVAL Inverse 1 2 2 4 Inverse 1 2 2 4 EVAL single argument examples When a Mathematica evaluation returns Failed an Excel VALUE is returned This suppresses further evaluations that depend on this result However often when messages are generated the original calculation request may be returned unevaluated as shown in the last example Notes Options that control how the link responds to a kernel message can be specified under the Mathematica Options Message tab Using Array Formulas To return lists of values to multiple cells Mathematica functions can be entered as an array formula Some functions in Mathematica naturally return lists others can be mapped over lists Here are some examples Example Result EVAL Range 10 i r 57 a a y EVAL Map Factorial A1 A10 a list containing the factorial of each number in A1 A10 Formulas that can be entered as an array To enter a formula as an array Select all cells that will be in the array Enter the formula Press Ctrl Shift Enter Once entered array formulas appear surrounded by in the formula bar All cells in an array form
55. he next example uses a function defined in a standard package which you must load first If VersionNumber gt 6 Needs ComputationalGeometry Needs DiscreteMath ComputationalGeometry This is an example of a function that generates a graphic triplot n_ PlanarGraphPlot Table Random n 2 The function triangulates a set of n random coordinates triplot 10 Here is a version of the same function written in two steps triplot n_ Module data data Table Random n 2 PlanarGraphPlot data 16 ExcelLink In this definition a local variable is defined within the body of the function The two steps within the body of the function are separated using a semicolon The final line of code returns the value of the function triplot 10 Special Considerations Usage To help others know how to use your function you can define a usage message triplot usage triplot n plots a random triangulation of n planar points triplot n plots a random triangulation of n planar points The usage message is used by the Mathematica Function Wizard to automatically generate argument templates for the function To be fully compatible with the Function Wizard you should always use the following convention for your usage messages usage f x does one thing f list does another f list x does more f x does one thing f list does another f list x does more Options Y
56. head arg1 arg2 builds an expression from the provided head and args and evaluates it The DATA function is typically used inside EVAL EXPR or RULE calls Example EVAL FileNames DATA DATA C You can wrap numeric data in the DATA function to ensure numeric calculation methods are used instead of symbolic methods The former is often much faster Example EVAL Eigenvalues DATA A1 B2 Unlike EXPR ranges DATA ranges can contain empty cells or errors DATA range trims empty cells and errors from the end of range See also EVAL EXPR RULE RULE ry ry L L EVAL expr evaluates expr in a Mathematica kernel EVAL head arg1 arg2 builds an expression from the provided head and args and evaluates it RULE lhs rhs builds the Mathematica expression lhs gt rhs Example RULE x 1 returns x gt 1 The RULE function can be used to specify options for the Mathematica function in EVAL or EXPR calls Example EVAL Plot A1 A2 A4 RULE PlotTitle Al RULE PlotStyle Blue If Ihs and rhs are references to multicell ranges of the same size a list of rules is returned Example EVAL ReplaceAll A1 RULE A2 A4 B2 B4 See also EVAL EXPR DATA 54 ExcelLink CALC EVAL expr evaluates expr in a Mathematica kernel EVAL head arg1 arg2 builds an expression from the provided head and args and evaluates it Volatile functions recalculate when any cell ch
57. heet Sheet3 ExcelInsert New Position gt 1 Sheet New ExcelSheets Sheet New Sheet Sheetl Sheet Sheet2 Sheet Sheet3 ExcelDelete ExcelSheet 1 ExcelSheets Sheet Sheetl1 Sheet Sheet2 Sheet Sheet3 Excellnstall ExcelInstall starts communications with Excel Details Option Visible specifies if a visible instance of Excel is used The default is Visible gt Automatic If Visible is Automatic a visible instance of Excel must already be open to start communications If Visible is False a hidden instance of Excel is launched for private use by Mathematica By default ExcelInstall is called the first time communication with Excel is required Examples lt lt ExcelLink ExcelInstall Visible gt True LinkObject C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Binaries ExcelLink exe 2 2 82 ExcelLink ExcelNew Book Book1l ExcelBooks Book Book1 ExcelClose ExcelUninstall ExcelInstall Visible gt False LinkObject C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Binaries ExcelLink exe 3 2 ExcelNew Book Book1l ExcelBooks Book Book1 ExcelUninstall ExcelName ExcelName object returns the name of the specified object Examples lt lt ExcelLink objects ExcelSheets Sheet Sheet1 Sheet Sheet2 Sheet Sheet3 Exce
58. hift Keyboard shortcuts for Mathematica context menu commands For descriptions of these commands see the Context Commands section in this reference guide ExcelLink 63 Workbook Shortcuts Keyboard shortcuts are available for the following workbook level operations Reevaluate initialization code Ctrl Enter Recalculate link formulas Ctrl HS Unlink Relink workbook Ctrl Keyboard shortcuts for workbook operations The functionality provided by some of these commands can optionally occur automatically as part of the Evaluate toolbar command Related options and buttons can be found on the Mathematica Options Workbook tab Notes Keyboard shortcuts can be disabled and re enabled using the Mathematica Options Interface tab 64 ExcelLink Data Types Overview Excel cells can contain six different types of data Numbers machine precision double Dates machine precision double with special formatting Strings unicode text Booleans TRUE FALSE Errors HVALUE HREF Empty empty cell Native Excel data types Mathematica data types not natively supported by Excel can be stored as strings in a range formatted as Text Integer 1 Rational 1 2 Complex 1 2I1 Symbol X Expression x y Mathematica data types that can be stored in text cells To specify the format of a range you can use the ExcelFormat function provided by the Exce Link package Or from within Excel you can sel
59. his displays the Range dialog ExcelDialog Range Range B3 C4 Notes When running code from Mathematica you need to activate Excel first to interact with an Excel dialog Notebook Deployment When developing Excel macros you do not need to transfer code to a workbook The Mathematica code can remain stored in a notebook file In this case any time you want to run a macro on a particular Excel workbook open the notebook that contains the macro and with the workbook open in Excel evaluate the code from the notebook Code Box Deployment To create a standalone workbook interface you can transfer the Mathematica macros you have developed in a notebook to code boxes in the Excel workbook Once this is done you can create buttons for the macros To deploy Mathematica code as an Excel macro you will need to copy the notebook cells that define the macro to a code box in an Excel workbook ExcelLink 23 Here are the notebook cells that contain the code you want to use as a macro Infij Needs ExcelLink In 2 m Excel B3 C4 In 3 m Inverse m In 4 Excel B3 C4 m Here is how to transfer the code to Excel 1 Create a code box for the macro in Excel Click Macros on the Mathematica Toolbar Click New and name the macro whatever you like Spaces in the macro name are permitted Select a location for the code box and click OK 2 Copy the code from Mathematica Use Kernel gt
60. ink prompt will not appear Macro buttons are relinked without prompting since this has no other impact on the workbook Fixing Broken Links If a workbook was not unlinked prior to opening it on another machine broken path based formula links can be fixed automatically by clicking Relink under Mathematica Options Workbook This is possible of course only if the Mathematica Link add in is available on that machine Notes Fixing broken links recalculates all Mathematica formulas in the workbook overwriting the existing set of results ExcelLink 47 Loading the Add In Before you can transfer data between Mathematica notebooks and Excel workbooks via the clipboard you first need to load the Mathematica Link add in in Excel To do this see Working in Excel Getting Started Once installed the Mathematica Link add in adds special copy and paste commands to Excel These commands convert Excel data to and from Mathematica lists 48 ExcelLink Copying Data from Excel To copy the contents of an Excel range to a Mathematica notebook a In Excel select the range you want to copy a Press Ctrl Shift C or right click and choose Copy if Mathematica Contexts are enabled This will copy the contents of the range onto the Clipboard as a Mathematica list a Switch to your Mathematica notebook and place the cursor where you would like to insert the list Press Ctrl V or choose Paste from the Edit menu Notes If
61. irst field the second filter to the second field and so on Filters can be value strings such as Q4 or comparison strings such as gt 100 Ifa filter is A11 no filter is applied to the field at that position Examples lt lt ExcelLink s ToFileName ExcelDirectory Link Examples Cities xls C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Examples Cities xls ExcelOpen s Book Cities xls rng ExcelRange USA A11 Range All ExcelFilter rng All Nevada ExcelRead rng City State Lat Lon Carson City Nevada 39 1667 119 767 Elko Nevada 40 8333 115 783 Ely Nevada 39 2833 114 85 Las Vegas Nevada 36 0833 115 167 Lovelock Nevada 40 0667 118 55 Reno Nevada 39 5 119 783 Tonopah Nevada 38 0667 117 083 Winnemucca Nevada 40 9 117 8 ExcelFilter rng None ExcelClose s 78 ExcelLink ExcelFormat ExcelFormat target returns the format of target ExcelFormat target format sets the format of the target Details The target can be a range or a sheet The format can be General Text or a number format such as 00 0 0 or yyyy mm dd hh mm ss The format can also be AutoFit to adjust column widths in target to display all contents If multiple formats exist in target ExcelFormat target returns Mixed Examples lt lt ExcelLink ExcelFormat
62. ist of available options ExcelLink a SetOptions ExcelOutput opts sets the defaults Examples lt lt ExcelLink expr Expand x y 5 x 5 x y 10x y 10x y 5xy y Excel B8 OutputForm expr Options Excel0utput ImageSize gt Automatic ImageMargins gt Automatic ImageFormat gt Automatic ImageResolution gt Automatic TextStyle gt Automatic Excel B8 ExcelOutput expr ImageMargins gt 10 TextStyle gt FontSize gt 16 Excel B8 ExcelPosition ExcelPosition range returns the position of the top left cell of range as a row col index pair Examples lt lt ExcelLink ExcelPosition C10 10 3 ExcelPosition A1 C10 1 1 ExcelRange ExcelRange id represents a range identified by id in the active context ExcelRange sheet id represents a range in the specified sheer ExcelRange book sheet id represents a range in the specified book Details The id can be one of the following Selection the currently selected range All all cells in the sheet Data_Range a defined range name 88 ExcelLink A1 C10 an A1 style address 15 13 a row col index pair 1 1 10 3 a list of two index pairs top left bottom right defining a rectangular range See ExcelSheet for a listing of valid id values for sheet See ExcelBook for a listing of valid id values for book Examples lt lt ExcelLink ExcelSelect Ex
63. it is displayed a Click the X in the upper right hand corner The dialog returns to a default location and state the next time it is displayed Notes a Occasionally changes you make in Excel may not be reflected in the contents of an open dialog If this happens redisplay the dialog to refresh its contents The Context Menu Mathematica Link commands that operate on the current selection are available from a Mathematica context menu To display this context menu right click a range when Mathematica contexts are enabled Expression Copy Du Paste Clear fia Function Array Comment Recalculate The Mathematica context menu To enable or disable Mathematica contexts click Contexts on the Mathematica toolbar Notes All commands on the Mathematica context menu also have a keyboard shortcut See Keyboard Shortcuts for a listing ExcelLink 33 Expression Cells If the Number Format of an Excel cell is set to Text the contents of the cell are considered to be a Mathematica expression when transferring them via the clipboard or in a macro For more information see Strings in the Excel Reference guide Notes The number format of a range can also be changed using Excel s built in Format Cells Number tab Cells should be formatted as Text before entering an expression To convert existing contents to Text you can reenter them manually or use the provided Expression command from the Mathem
64. kbook a Create nb and xls files with the same name in the same directory Select the cells that contain code that will be used in the workbook Click Cell Cell Properties Initialization Cell to specify the selected cells as initialization cells a Save the notebook When you do this you will be prompted to create a package file with the contents of the initialization cells Click Create Auto Save Package 20 ExcelLink Z4 Auto Save Package The notebook you asked to save contains Initialization cells If you want to be able to load this notebook into the kernel directly using the Get or Needs functions you should click Create Auto Save Package This will cause the front end to automatically maintain a package m file in the same directory as the notebook The package will contain all the initialization cells from the notebook it will be updated each time the notebook is saved Create Auto Save Package Don t Create Auto Save Package You should now have nb m and xls files in the same directory with the same name In the future every time you save changes to the notebook the package file is automatically updated In turn the next time you evaluate in Excel the new set of function definitions will be automatically loaded and used Notes Using the package approach you can easily develop and update function definitions for a workbook However you must remember to send the package file along wit
65. ks to see if the string being assigned appears to be a data type it knows about If so the string is converted to the identified data type and an appro priate cell format is automatically applied if needed Strings assigned to text cells do not pass through the Excel interpreter They can therefore contain exact numbers rationals or anything else that can be stored as a Mathematica expression As an example if 1 2 is assigned to a cell with the General format the rational is interpeted as January 2 of the curent year and a date format is automatically assigned to the cell When 1 2 is assigned to a text cell the fraction remains as entered Notes For worksheet functions such as EVAL or EXPR input ranges are a ways assumed to contain text expressions even if the input range has not been formated as Text This assumption makes it easy to build Mathematica expressions You can wrap data ranges with the DATA worksheet function to treat text contained in the range as Mathematica strings Booleans Excel Booleans are mapped to corresponding Mathematica symbols TRUE True FALSE False Excel Boolean mapping Errors Excel errors are converted to Mathematica strings HN A N A HREF HREF HVALUE VALUE HNULL NULL HNAME HNAME HNUM HNUM HDIV 0 HDIV O Excel error mapping ExcelLink 67 Of these errors four may be returned by Mathematica worksheet functions In this case the error
66. l if a link with that name is not available a Anytime you close a connection from the Excel side you need to call ExcelShare again in Mathematica before starting a new shared kernel session ExcelShare only works in Mathematica 6 0 or later Examples lt lt ExcelLink ExcelShare Once you have connected from the Excel side you can share definitions of data between Excel and Mathematica The above assumes you defined x as 100 in Excel using a formula macro or the clipboard ExcelSheet ExcelSheet id represents a sheet identified by id in the active context ExcelSheet book id represents a sheet in the specified book Details The id can be one of the following Active the current active sheet Sheet1 the name of the sheet lor 1 a positive or negative position index See ExcelBook for a listing of valid id values for book The first sheet in the workbook has position 1 The last sheet in the workbook has position 1 ExcelLink 97 Examples lt lt ExcelLink ExcelSheets Sheet Sheet1 Sheet Sheet2 Sheet Sheet3 ExcelName ExcelSheet 1 Sheet1l ExcelName ExcelSheet 1 Sheet3 ExcelName ExcelSheet Active Sheet1 ExcelCheck ExcelSheet 5 False ExcelSheets ExcelSheets gives a list of sheets in the active workbook ExcelSheets book gives a list for the specified book Examples lt lt ExcelLink ExcelSheets
67. lName objects Sheet1 Sheet2 Sheet3 ExcelLink 83 Excel Excel id identifies a location to read or write from in Excel Excel id reads the contents of id Excel id expr writes expr to id Excel id clears the contents of id Details The location identified by id can be a range shape or sheet The id can be a name Ai style address All Selection or an Excel object reference Assigning graphics typeset equations or formatted output to a location displays a graphic Excel id is equivalent to ExcelRead id Excel id expr is equivalent to ExcelWrite id expr Excel id is equivalent to ExcelClear id ExcelRange ExcelShape or ExcelSheet can be used to create an Excel object reference Excel object references can be used to provide a specific context for id or to specify a different type of id Examples Excel A1 C10 Excel Sheet1 and Excel ExcelSheet 1 Examples lt lt ExcelLink Excel A1 C3 IdentityMatrix 3 Excel A1 C3 1 0 O O le O O 0 1 Excel A1 C3 data Table Random 10000 10 Excel Sheet1 data data Excel Sheet1 Dimensions data 10000 10 Excel Sheet1 84 ExcelLink ExcelNew ExcelNew creates a new workbook with the default number of sheets ExcelNew i creates a book with i sheets ExcelNew name namez creates a book with the s
68. nd xlsb files are also supported Examples lt lt ExcelLink book ExcelNew Data Book Book2 ExcelWrite Data Table Random 100 10 s ToFileName ExcelDirectory User Temporary xls C Documents and Settings Anton Desktop Temporary xls book ExcelSave book s Book Temporary xls ExcelClose book DeleteFile s 94 ExcelLink ExcelSelect ExcelSelect range selects the specified range ExcelSelect shape selects the specified shape Examples lt lt ExcelLink ExcelContext Book1 Sheet1 ExcelSelect ExcelRange Sheet3 B2 D5 ExcelContext Selection Book1 Sheet3 ExcelAddress Selection B2 D5 ExcelSelect ExcelRange Sheet1 A1 ExcelShape ExcelShape id represents a shape identified by id in the active context ExcelShape sheet id represents a shape in the specified sheet ExcelShape book sheet id represents a shape in the specified book Details The id can be one of the following Selection the currently selected shape Rectangle 1 the name of the shape 1 or 1 a positive or negative position index The first shape inserted on a sheet has position 1 The last shape inserted on a sheet has position 1 See ExcelSheet for a listing of valid id values for sheet See ExcelBook for a listing of valid id values for book ExcelLink 95 Examples lt lt ExcelLink shapel ExcelInsert Exc
69. nge should be offset to the end of contiguous data in that dimension None specifies that range should not be offset in that dimension Examples lt lt ExcelLink ExceloOffset Al 5 None Range A6 ExcelOffset A A None 5 Range F F 86 ExcelLink Exceloffset A1 C3 1 1 Range B2 D4 ExcelWrite A A Range 10 ExceloOffset Al All None Range A11 ExcelClear A A ExcelOpen ExcelOpen book x1s opens the specified book in Excel Details File name book xls must be specified using a full path If it is already open in Excel the workbook is activated a Newer Excel file formats such as xlsx xlsm and xlsb files are also supported Examples lt lt ExcelLink dir ToFileName ExcelDirectory Link Examples C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Examples files FileNames xls dir books ExcelOpen files Book Cities xls Book Highways xls Book Metals xls Book Stocks xls Book Waves xls Book Wind xls ExcelClose books Null Null Null Null Null Null ExcelOutput ExcelOutput expr opts specifies options for how expr should be output in Excel Details Excel id ExcelOutput expr opts displays expr with specified options Excel id form expr displays expr in the specified form using default options Options ExcelOutput gives a l
70. nge the size of the button or move it To do this you will need to select the button To select a button Hold down the Ctrl key and then click the button Once you have a button selected you can work with it as you would any other shape You can for example move it or resize it change its caption copy and paste it to another location or delete it Right clicking a button will also select it and work with it in various ways Grouping d Order gt Assign Macro y Format Control f Right clicking a macro button ExcelLink 43 Developing on the Clipboard The Mathematica Clipboard window can evaluate multiple lines of code at a time When multiple lines are evaluated on the Clipboard only the result from the last line is returned You can use this behavior to progressively build up code that can be used as a macro by alternatively clicking Evaluate and Restore Mathematica Clipboard data Excel Selection g ListPlot data PlotStyle gt Blue Excel My Plot g Copy Paste Put Get Evaluate Close Developing code on the Clipboard When used this way the Mathematica Clipboard window becomes a floating macro code box The Clipboard Evaluate button becomes a Run button for the macro during development When you are done developing you can transfer the code to a code box To transfer code from the Mathematica Clipboard to a code box Click Put on the Mathematica Clip
71. nia dels ine sechaedeSansafoudbadvodbaaadoeubadsdus ig agaedaaetectses shosasgouueenaees 47 Copying Data from Excell raees snanar n aa E E AI EO ENOTA 48 Pasting Data to Excel anonanse a e a a a A a dabtean aes 49 Fixing Problematic Data cc ccccccessccccsssseeecessseeeceessssececssssececsesseeeceessssececsssseeeessaeesesenenaecs 50 Excel Reference Excel Worksheet Functions nssnnesseeensesessssessssssssseesssssrssseessssesssseesssessssseessseessssesssserssseeeess 52 Toolbar Command sicc cssaccsccsssccevagssevasancecedanecedas ayndwag axdebaes EEE EEAS EE a EE iaia 53 Context Commands S 55 5 hasasciasascedanssscamwnsadeisnnscuineaasienadnisubsnesdsesedeisbiaandiuababindumitadacmdossiaehosawidantawns 59 Keyboard Shortcuts ccccccccesssscecceececsesssssseceseceescsensseceeececcecessesssseeesececsesenestsseeeeesseseseenees 62 D ta TYPES icgais rene acts a tasacs sice esate a a R 64 Number Formats ccccsssccssscesssecssscssscsseccssecesscesssscesscesscessecesscecsscesssseeescsssesseeesseessacensesons 68 ExcelLink Reference ExcelLink Function Index ccccsscseeseccccccccususscecccccessesssececcccessseuensecceseceusaesseecesseeeuuensnssces 69 ExcelLink Introduction Overview Mathematica Link for Excel consists of two main components The ExcelLink package The MathematicaLink add in These components work together to provide full two way connectivity between Mathema
72. nk An optional third step is available to select and specify options for functions such as Plot that have a defined set of options Mathematica Function Options Available Options Pot o amer fs pectRato Misin GoldenRatio f a value GoldenRatio 1 ic Default GoldenRatio 1 AxesLabel None ault GoldenRatio AxesOrigin Automatic Usage AspectRatio is an option for AxesStyle Automatic Graphics and related oO o O o O O Background Automatic o O o O m functions which specifies the ratio of height to width for a ColorOutput Automatic plot Compiled True DefaultColor Automatic DefaultFont DefaultFont DisplayFunction DisplayFunctior 30 options available No options selected Preview Close lt Back Next gt Enter The Mathematica Function Wizard step 3 Notes Unlike Excel Mathematica uses case sensitive syntax Therefore be sure to capitalize Mathematica function names like Prime If you do not include a character in your name search the wizard defaults to a non case sensitive search for name You cannot interact with a Mathematica front end that is in server mode If you would like to work in the Mathematica front end you can launch another standard instance of the front end Creating a Macro To call Mathematica code as an Excel macro the Mathematica code must be contained in a named code box inside Excel Once you have created the named code box you can then creat
73. ocation in Excel as if it were a variable then assigns or retrieves data from it Excel A1 hello Excel A1 hello You can also clear data from the Excel location Excel A1 ExcelLink When assigning data to Excel ranges one dimensional data can be assigned either to single rows or single columns of cells Excel A1 C1 1 2 3 Excel A1 A3 1 2 3 Two dimensional data can be assigned to a rectangular range of cells Excel A1 C3 1 2 3 4 5 6 7 8 9 Excel A1 C3 Displaying Graphics Needs ExcelLink This displays a graphic in Mathematica g Plot Sin a 2 a 4 4 PlotStyle gt Hue 8 Frame gt True 1 This displays the same graphic in Excel Excel B3 g As specified the graphic is displayed at cell B3 This cell will serve as an anchor point for the graphic however you can move it anywhere you like You can also resize the graphic as needed Subsequent assignments update and redraw the existing graphic g Plot Sin a 2 a 3 3 PlotStyle gt Hue 8 Frame gt True 1 Excel B3 g This clears the graphic associated with cell B3 Excel B3 ExcelLink Displaying Expressions Needs ExcelLink This defines an expression to be displayed expr Sin i Pi 2 2 Here is a typeset form of the expression TraditionalForm expr 1 sin i r 2 This displays the typeset form in Excel Excel B3
74. ore Notes To paste evaluation results to a location in Excel click Paste You will be prompted to specify where to paste the results To paste evaluation results to another program click Put This puts the evaluation results onto the global Clipboard You can then paste them into another program Unloading the Add in If you are finished using the link you can uncheck Mathematica Link in the Add Ins manager and click OK This unloads the add in from Excel Add Ins Add Ins available Analysis ToolPak Analysis ToolPak VBA I Conditional Sum Wizard Euro Currency Tools Internet Assistant VBA I Lookup Wizard Mathematica Link Solver Add in Cancel Browse Automation ud Unloading the Mathematica Link add in When you unload the link you will be prompted to delete your personal settings Click No to preserve your settings until the next time you use the link Click Yes to restore default settings the next time you use the link Notes If you do not uncheck Mathematica Link in the Add Ins manager the link is automatically loaded the next time you start Excel 32 ExcelLink Working in Excel General Principles Non Modal Dialogs All Mathematica Link dialogs are non modal You can leave dialogs open and still work in Excel There are two ways of closing a link dialog Click Close or press the Esc key The dialog remembers its current location and state the next time
75. ou can also define a set of default options for your functions if needed Options triplot Frame gt False GridLines gt None Frame gt False GridLines gt None This clears the previous definition for the function then defines it with options Clear triplot triplot n_ opts__ Rule Module data g rules data Table Random n 2 rules Sequence Join opts Options triplot PlanarGraphPlot data rules ExcelLink 17 triplot 10 Frame gt True GridLines gt Automatic Errors When you are developing a function that will be used in Excel you should consider returning the symbol Failed if something goes wrong in your function You can do this using the Check function triplot n_ opts__ Rule Check Module data g rules data Table Random n 2 rules Sequence Join opts Options triplot PlanarGraphPlot data rules J Failed The symbol Failed is converted to a VALUE error in Excel that will suppress further dependent calculations triplot hello Table iterb Iterator hello does not have appropriate bounds More SFailed To be complete you should also create a catch all function definition that will handle the case where users provide argu ments that do not match the pattern you specified By default the function returns unevaluated triplot 1 2 3 triplot 1 2 3 This traps the error triplot ___ Failed triplot 1 2 3
76. oying Mathematica macros A workbook package file should be in the same directory and have the same name as a workbook with the m extension replacing xls Working with Code Boxes Code boxes are simply text boxes that have been formatted for writing Mathematica code and given a special name They are in every other way ordinary text boxes There are two ways of selecting a text box selecting the box itself or selecting text inside the box The selection border of the text box will be different in each case To do anything other than edit the text inside the box you will need to make sure the box itself is selected Once the box itself is selected you can for example move it or resize it copy and paste it to another location or delete it 42 ExcelLink When typing in a text box be aware that Excel goes into a different mode Most toolbar buttons for example are no longer available H File Edit View Insert Format Tools Data Window Dae BP Ga Bea Sonona Courier New 8 BZU g Plot Code v f data Excel Selection g ListPlot data PlotStyle gt Blue Excel My Plot g Excel while in edit mode As with many toolbar buttons Mathematica macros do not work while Excel is in this mode To get out of this edit mode press the Esc_ key or click out of the box Working with Buttons By default the caption of the button will be the same as the macro name You can change this You can also cha
77. pecified named sheets ExcelNew book xls creates a book using the specified existing book as a template Details Template names must be specified using a full path Examples lt lt ExcelLink ExcelBooks Book Book1 ExcelNew Book Book2 ExcelClose v Map ToString Range 2000 2010 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 ExcelNew v Book Book3 ExcelClose s ToFileName ExcelDirectory Link Templates Report xls C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Templates Report xls ExcelNew s Book Reportl ExcelClose ExcelLink 85 ExcelObject ExcelObject type id represents an object of the specified type identified by id in the active context ExcelObject type context ExcelObject type id provides a context for the object id Details All Excel objects are represented internally in this form Examples lt lt ExcelLink object ExcelRange Sheet1 A1 Range Al InputForm object ExcelObject Sheet Sheet1 ExcelObject Range Al object ExcelRange Book1 Sheet1 A1 Range Al InputForm object ExcelObject Book Book1 ExcelObject Sheet Sheet1 ExcelObject Range Al ExcelOffset ExcelOffset range rows cols offsets range by the specified number of rows and columns Details All specifies that ra
78. plies or removes the Text number format from the selected range If the selected cell does not contain a link formula the Wizard goes directly to the Mathematica Function Arguments step This is the only difference between this command and the Toolbar Command Functions command Use this command if you know what function you would like to enter and you do not need to browse for it a See also Toolbar Command gt Functions Location Context Menu Keyboard shortcut Ctrl Shift F Array Toggle command Applies or removes the Text number format from the selected range When converting an array formula to a single cell formula you can select the top left cell or the entire array When converting a single cell formula to an array formula the array formula is automatically sized correctly or you can preselect the cells you would like to be included in the array Location Context Menu a Keyboard shortcut Ctrl Shift A ExcelLink 6l Comments Toggle command Applies or removes the Text number format from the selected range Commenting formulas allows you to temporarily disable evaluation of the selected formulas To comment all Mathematica formulas on a sheet click the top left corner of the header rows between A and 1 to select the entire sheet To comment all Mathematica formulas in a workbook click Unlink under Mathematica Options Workbook Location Context Menu Keyboard
79. reating Excel Functions The only difference is in this case you will save a sequence of macro commands to the package file instead of a set of function definitions You should now have nb m and xls files in the same directory with the same name In the future every time you save changes to the notebook the package file is automatically updated In turn the next time you click Evaluate in Excel the new workbook processing macro will be used Notes If you would like the kernel to close after workbook processing is complete include Quit as the last line of your macro ExcelLink 25 Working in Excel Getting Started Loading the Add in After installing the link you will see a Mathematica Link for Excel folder in your Start All Programs menu The add in in this folder is required if using the link from the Excel side or when copying and pasting data between programs a Mathematica Link For Exc d w Mathematica Link Add In Microsoft Developer Network gt f Mathematica Link Manual M Microsoft Office Tools gt The Mathematica Link for Excel start menu folder If you did not install the add in when you installed the software click Mathematica Link Add In to do so now The add in will install itself when it is loaded the first time This may take a moment depending on your anti virus security settings If you are prompted to do so choose to Enable Macros in the security warning dialog If
80. right click a range to quickly display the Mathematica Context menu without turning on Mathematica Contexts Improved in Version 3 5 Updated toolbar and menu icons Improved Mathematica connection management self healing link new connection options window Improved handling of Mathematica connection exceptions and evaluation interrupts Improved Mathematica messaging messages now returned in real time Improved Display Message Box option now only applies to Print output Improved workbook initialization code evaluation and management Improved common multi workbook initialization now supported using an init m file in same directory Improved support for long running Mathematica macros Improved automatic workbook relinking and addin startup logic Other minor improvements and fixes Version 3 2 New in Version 3 2 Compatibility with Mathematica 7 0 including Mathematica 7 0 based icons ExcelOpen ExcelSave and ExcelDialog support for Excel 2007 xlsx xlsm xlsb files ExcelWrite support for writing Grid 1 2 3 4 5 6 opts f ex ANOVATable output ExcelFormat A C AutoFit to automatically adjust column width Support for 64 bit Windows and 64 bit Mathematica connecting to 32 bit Excel ExcelLink 3 Improved in Version 3 2 Improved message print format in Mathematica 6 and 7 Fixed a bug where some workbooks with macro buttons did not relink correctly Improved ExcelInstall Visible gt True me
81. ro a Locate the cells in your notebook that define inputs to your analysis a Modify those cells to use values contained in Excel a Likewise locate the cells in the notebook that display outputs of your analysis a Modify those cells to return results to Excel For more information see Code Box Deployment 22 ExcelLink Special Considerations Status If your analysis takes a while to complete you may want to provide some feedback to the user on how the analysis is proceeding You can do this by using the Excelstatus function Needs ExcelLink ExcelStatus Processing data Pause 3 ExcelStatus Analyzing data Pause 5 ExcelStatus Generating report Pause 1 ExcelStatus This writes status information to the status bar at the bottom left hand side of the Excel window In the final line ExcelStatus is called without arguments in order to return the status bar to its default state Notes Writing status messages makes the analysis section of your notebook Excel specific However this may be required for longer routines Writing status messages can also be a good way to see which part of your analysis is taking up the most time Dialogs If you would like to ask the user to select a range or specify a file name during a macro you can do so using the ExcelDialog function The symbol ExcelDialogs gives a list of available dialogs S ExcelDialogs Range Open Save Files Folder T
82. s have the following meanings HN A error or empty found in inputs HREF connection error or invalid kernel path HVALUE evaluation returned Failed HNULL evaluation returned Aborted or link was closed Mathematica worksheet function errors Notes For worksheet functions such as EVAL or EXPR the N A error suppresses further evaluation To force a range containing empty cells or errors to be evaluated in Mathematica you can use the DATA function as an argument wrapper DATA range is a data range that may contain empty cells or errors You can then handle the empty values and errors in your Mathematica code Empty Mathematica Link for Excel handles empty cells differently depending on where the cells are located non trailing empty Empty trailing empty trimmed from range Excel empty cell mapping For empty cells to be trimmed an entire trailing row or column within the range must contain nothing but empty cells Notes The trimming of trailing empty cells allows you to easily work with entire rows and columns of data 68 ExcelLink Number Formats Excel number formats allow you to define how a number will appear in your spreadsheet 1000 General 1 0E 03 0 0E 00 1 000 00 H HHH 00 100000 0 Examples of Excel number formats Excel number formats are also used to format dates and time information that has been stored as a number January 1 2000 mmmm d yyyy 12 00 hh mm 01 Jan 00
83. s of the Mathematica list are selected Excel will not recognize the Clipboard contents as a Mathematica list unless these brackets are present A convenient way to select an entire Mathematica list is to place the cursor at the beginning of the list and triple click 50 ExcelLink Fixing Problematic Data If you run into problems pasting data into Excel it is likely the copied data is not in the proper form The Paste command requires data to be in Mathematica InputForm Depending on how you copied the data and from what kind of notebook cell you copied it you may end up with data in a format other than InputForm The Paste command can fix certain common problems with the data automatically however in some cases you may still need to convert to InputForm on your own You can do this in two ways a In Mathematica before copying use Cell Convert gt InputForm In Excel after copying use the Evaluate button in the Mathematica Clipboard window If you do not want to modify your source notebook the second approach is the most convenient Here is an example of some data to paste data Table Random 10 8 10 6 92556x10 5 23638x10 4 21124x10 4 61886x10 9 65829x10 5 8988x10 4 26737x10 1 64441x10 1 50418x10 7 90577x10 To view this data in the Mathematica Clipboard window copy the output then in Excel open the Mathematica Clipboard window and click Get Mathematica Clipboard
84. so be used to return Mathematica graphics Example Result EVAL ListPlot Table Random 50 ExcelGraphic 1 EVAL Graphics Polygon 0 0 9 3 3 5 ExcelGraphic 2 EVAL Plot3D Sin x Cos x y y x 0 4 y 0 4 ExcelGraphic 3 EVAL graphics examples Mathematica graphics are displayed as picture objects in Excel By default these pictures are rendered as Windows metafiles This format scales reasonably well since it is a vector format Font sizes however may become a bit distorted when a graphic is resized If this happens you can force the graphic to be re rendered at its new size by recalculating its formula Re rendering the graphic will reapply any font size you have specified as a graphics option Notes Itis the name of a graphic that associates it with a particular Excel cell For example if a formula in cell E10 returns graphics the picture named Graphic E10 will be updated on the same sheet If no picture named Graphic E10 exists on that sheet a new graphic will be created This is the extent to which the picture is linked When cutting and pasting a Mathematica generated picture to another application or worksheet this name based link is not maintained The copied graphic is simply a static picture By modifying the name of a picture you can change which cell updates that graphic To modify the name of a picture you can select the picture and click in the Name box on the left hand side of t
85. t again or uncheck Comment on the Mathematica context menu Commenting a formula essentially freezes the formula in its currently calculated state Commenting formulas for an entire workbook is discussed as a way of sharing a workbook with others in Sharing Workbooks Notes To select an entire sheet click the upper left hand corner of the header row between a and 1 You can then apply selection commands to the sheet The CALC function can be used to generate random numbers for Excel based simulations ExcelLink 41 Working with Macros Macro Types There are three types of Mathematica macros that you can create Macro Type Description Definitions Macro defines Mathematica functions used in a workbook Processing Macro processes a workbook when the Evaluate button is clicked Task Macro performs a task when an associated button on a spreadsheet is clicked Types of Mathematica macros The difference between the first two is that a workbook definitions macro does not perform actions on a workbook a workbook processing macro does All Mathematica macros can be deployed by creating a code box to store the Mathematica code inside a workbook For workbook level macros however you can also store the Mathematica code in a package file Macro Type Deployment Definitions Macro initialization code box or workbook package file Processing Macro initialization code box or workbook package file Task Macro macro code box Depl
86. the named shape If VersionNumber lt 7 If VersionNumber 2 6 Needs Histograms Needs Graphics ExcelWrite Histogram Graphic Histogram trials 60 60 40 zo This defines the file name for the report workbook f ToFileName ExcelDirectory Home Autogenerated Report xls C Documents and Settings Anton My Documents Autogenerated Report xls This saves the report workbook to disk ExcelSave Active f Book Autogenerated Report xls This closes the workbook once you have finished exporting data to it ExcelClose This cleans up by deleting the exported workbook file DeleteFile f Closing Excel Needs ExcelLink Once your automation routines are completed you can use the ExcelUninstall1 function to close Excel ExcelUninstall By default ExcelUninstall only closes visible instances of Excel if no workbooks remain open This avoids accidental data loss You can force a visible instance of Excel to close even if workbooks are open by specifying Visible gt True as an option to ExcelUninstall ExcelLink 15 Creating Excel Functions Defining Functions Here is how to define a Mathematica function that adds two numbers addtwo x_ y_ x y Arguments for the function are specified using pattern _ indicators The delayed assignment operator indicates that the body of the function is evaluated only once the values for the arguments are known addtwo 2 2 4 T
87. thod for launching visible instance of Excel Improved Mathematica Macros dialog method of inserting of code boxes and buttons No kernel connection closed dialog if initialization code ends with Quit Other minor improvements and fixes Version 3 1 New in Version 3 1 Compatibility with Mathematica 6 0 Compatibility with Excel 2007 Keyboard shortcuts for Excel toolbar commands Additional Excel message related options Restored backwards compatibility with Excel 2000 Improved in Version 3 1 Improved editing of existing functions using the Mathematica Function Wizard Improved compatibility with workbooks originally built with version 2 x of the link Improved printable PDF documentation Version 3 0 New in Version 3 Display of typesetting and formatted output in Excel Creating Mathematica based macros A suite of Mathematica functions to interact with and automate Excel Improved in Version 3 Start End Link button is now an Evaluate button This button can be used to interrupt current evaluations and re evaluate the workbook once changes have been made To end a link hold down the Shift key and click the Evaluate button 4 ExcelLink You can now use the Function Wizard to edit existing formulas select and assign options more easily and browse for built in functions by category Standard packages are automatically declared by default and can be browsed directly within the Function Wizard This functionali
88. tica and Excel To use the link from Mathematica you load the Exce Link package To use the link from Microsoft Excel you load the MathematicaLink add in Features The Exce Link package provides A set of Mathematica functions that allow you to Read and write data to Excel ranges Display graphics typeset equations and formatted output in Excel Create open modify and close Excel files Develop Excel worksheet functions in Mathematica a Develop Excel macros in Mathematica The MathematicaLink add in provides A set of worksheet functions that allow you to use Mathematica functions in Excel formulas A Mathematica Function Wizard to help you learn about and enter Mathematica functions A Mathematica macros window that allows you to turn Mathematica code into Excel macros A special Clipboard window that allows you to easily copy and paste data between programs A searchable PDF based manual 2 ExcelLink Changes Version 3 5 New in Version 3 5 Compatibility with Mathematica 8 0 Compatibility with Excel 2010 including Excel 2010 64 bit version ExcelShare function allows sharing a kernel between Excel and Mathematica VBA support routines MathematicaSet MathematicaRun and MathematicaGet Support for reading and writing Excel comments including writing to a range using CellLabel my comment Shift click the Mathematica Evaluate button to close link and bring up the Mathematica connection window Shift
89. tions Workbook tab Mathematica Options Interface Kernel Message Workbook Evaluation V Reevaluate workbook initialization code V Recalculate workbook formulas Sharing n co Unlinking a workbook When unlinked formulas are stored as cell comments and macro buttons display a macro cannot be found error when clicked Once a workbook is unlinked it can easily be viewed without problems on other machines even machines where Mathe matica Link is not installed Those who do have the link will have the option of relinking the workbook and interacting with it Notes In Excel cell comments are typically indicated by a small red triangle in the upper right corner of a cell This is an option that can be specified under Tools Options View To view a comment place your cursor over the cell 46 ExcelLink Relinking a Workbook When opening an unlinked workbook link users are automatically prompted to relink formulas in the workbook Mathematica Link 2 j Relink Mathematica Formulas in Example xls a Click Yes to relink all formulas contained in the workbook Click No to leave the workbook as is You can relink formulas later at any time by clicking Relink on the Mathematica Options Workbook tab Notes Relinking formulas recalculates all Mathematica formulas in the workbook overwriting the existing set of results If a workbook contains only Mathematica macros the rel
90. ty replaces the Libraries Dialog Data Copy and Paste commands are now available from a Mathematica Context Menu You can now see the data you are copying and pasting using the Mathematica Clipboard window Kernel Dialog has now been incorporated into the Mathematica Clipboard window You can type Mathematica expressions directly into the Clipboard window evaluate them and paste the results somewhere if you choose Messages are now displayed and stored to a nonmodal window Using the window you can scroll through multiple mes sages find the source of a message and even save the messages to a log file Formula Activate Deactivate Recalculate commands are available from the Mathematica Context Menu as a Com ments toggle and Recalculate command Increased worksheet function speed Worksheet functions now calculate up to eight times faster Increased worksheet function reliability Worksheet functions are now robust enough for the most demanding spreadsheet applications Automated tests have performed billions of continuous evaluations without errors Improved worksheet function error handling Dependent evaluations are now suppressed through the use of native Excel error codes To share workbooks with others you can unlink the workbooks formulas and macro buttons using the Unlink button in the Mathematica Options Workbook tab If colleagues have the link they will be automatically prompted to relink formulas when they open the workbook
91. ula range share a single formula each element of the array returned to a separate cell The Mathematica Link add in provides two tools to help make using array formulas easier The Mathematica Function Wizard can help you automatically enter and edit array formulas ExcelLink 39 The Array command on the Mathematica context menu provides a way to easily toggle between single cell and array formulas B1 z f EVAL Map Factorial A1 A10 1 1 P 2 Expression 3 3 Copy f 4 4 E 5 Paste 6 6 Clear L Z fa Eunction f 9 9 Array l ve 10 Comment f q2 Recalculate 13 The Array toggle command on the Mathematica context menu Notes a Ifyou manually edit an array formula but forget to select all cells in the array beforehand an error message will appear when you try to reenter the formula If this happens press Esc to cancel any changes you made Then select all cells in the array before making the changes again Array formulas can significantly reduce calculation times if the same operation is being performed on a large number of cells If you have filled a range with the same formula by dragging the formula across the range it may be much faster to perform these calculations using an array formula There is a transaction time associated with each call to Mathematica If a single array formula is used to return values for 100 cells recalculation could be up to 100 times faster than calling 100 individual
92. x 7 7 2 x 8 x 9 SetOptions ExcelResult MaxCharacters gt 100 MaxCharacters gt 100 NumberMarks gt False ExcelForm expr ExcelResult 1 SetOptions ExcelResult MaxCharacters gt 255 MaxCharacters gt 255 NumberMarks gt False SetOptions ExcelResult NumberMarks gt True MaxCharacters gt 255 NumberMarks gt True ExcelForm expr 0 13421772800000006 1 5099494400000009 gt x 7 549747200000004 x 2 23 04x 7 7 2x x 22 02009600000001 gt x 3 41 287680000000016 x 4 51 609600000000015 x 5 43 00800000000001 gt x 6 23 040000000000006 x 7 7 2 gt x 8 x 9 SetOptions ExcelResult NumberMarks gt False MaxCharacters gt 255 NumberMarks gt False ExcelLink ExcelRun ExcelRun macro runs the specified VBA macro in Excel ExcelRun macro arg arg runs the macro with the specified arguments Examples If the ExcelLink add in is currently loaded in Excel this displays the Mathematica Clipboard window lt lt ExcelLink ExcelRun MathematicaClipboard ExcelSave ExcelSave saves changes to the active workbook ExcelSave book saves changes to the specified book ExcelSave book book x1s saves changes as book x1s Details If book has never been saved a file name must be specified File name book xls must be specified using a full path Newer Excel file formats such as xlsx xlsm a
93. ze gt 300 50 TextStyle gt FontSize gt 20 Excel B3 ExcelUninstall ExcelUninstall ends communications with Excel Details a Option Visible specifies if visible instances of Excel should be closed when the link is uninstalled The default is Visible gt Automatic If Visible is Automatic visible instances of Excel are closed if no workbooks are open a If Visible is True visible instances are always closed and unsaved changes to open workbooks are discarded If Visible is False visible instances are never closed Examples lt lt ExcelLink ExcelInstall Visible gt False LinkObject C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Binaries ExcelLink exe 2 2 SExcelLink LinkObject C Documents and Settings Anton Application Data Mathematica Applications ExcelLink Binaries ExcelLink exe 2 2 ExcelUninstall SExcelLink ExcelUnshare ExcelUnshare ends kernel sharing with Excel Details Calling ExcelUnshare from a Mathematica session is not required There is no harm in leaving the shared link open once it is established ExcelUnshare is automatically called when Excel closes a shared kernel session 100 ExcelLink Examples lt lt ExcelLink ExcelShare LinkObject ExcelShare 2 2 ExcelShare LinkObject ExcelShare 2 2 ExcelUninstall ExcelShare ExcelWrite ExcelWrite Excel

Download Pdf Manuals

Related Search

Related Contents

Olympus SP-320  Philips HybridShell DLA71012  STECKERLEUCHTEN PLUG LAMP LAMPE DE FICHE  TrueGrid ® Output Manual for LS-DYNA  Manuel d`instructions de service EFOY Pro Series  Maîtrisez votre budget santé Boutique santé en ligne Soins à domicile  2005 - MSA World  

Copyright © All rights reserved. Failed to retrieve file