27 Excel Add-Ins

HSC Chemistry® 5.0 27 - 2 Peter Björklund June 28, 2002 02103-ORC-T 27. Excel Add-In Functions 27.1 Getting started With HSC 5.0 Add-In Functions it is possible to use the HSC 5.0 database directly under MS Excel 2000 and in that way carry out several thermochemical calculations. In order to use these functions in Excel they must, however, first be activated. Three stages may be needed to activate HSC functions in Excel. The number of stages needed depends on the computer settings and Windows and Excel versions. Activation Stage 1: 1. Open Excel 2000 (NOTE: HSC Add-Ins do not work under earlier Excel versions !) 2. Select “Tools, Add-Ins...” from the menu. 3. Select “Browse…” and locate HSC5.xla from your HSC5\AddIns folder. 4. Select “HSC 5.0 Functions” and press “OK”, see Figure 1. It may be necessary to restart Excel in order for the add-in functions to work. Figure 1: Adding/removing HSC 5.0 Functions under Excel. Activation Stage 2: If the installation is unsuccessful, the following steps may help: 1. Start the VBA Editor by pressing Alt+F11. (or select Tools, Macro, Visual Basic Editor, ...) 2. Select “Tools, References” from the menu. 3. Select “Browse…” and locate HSC5_Excel.dll from your Windows System directory (for example C:\Windows\System\HSC5_Excel.dll). It may be necessary to restart Excel in order for the add-in functions to work. 4. See also stage 3 in Chapter 27.2 if needed. The HSC installation routine automatically takes care of the DLL registration. 27.2 Opening and Moving Files that use HSC 5.0 Functions Once installed, as above, you are ready to start using the add-in functions. Sometimes it may be essential to change the links of a worksheet in order for Excel to find the HSC5.xla file. This may be the case if opening a file that uses HSC 5.0 functions for the first time or when moving these files from one computer to another. These steps should then be carried out: Activation Stage 3: 1. During opening, answer No when prompted by Excel to update all linked information (automatic link updates do not work due to some bug in MS Excel). 2. Select “Edit, Links” from the menu. 3. Choose the path containing HSC5.xla from the listbox and press “Change Source”. 4. Browse to your HSC5\AddIns folder and choose HSC5.xla. Alternatively, it is also possible to first remove HSC 5.0 Functions under “Tools, AddIns”, see Figure 1, and then by adding them again, to force an update of the worksheet links. If the cells conatining the functions return a “#VALUE!” error message, please save the file and restart Excel in order to make the functions work. Try opening the test example “AddInSample.xls”, where all available functions are demonstrated. To turn the add-in feature off, simply deselect it using the “Tools, Add-Ins...”dialog from the main menu. 27.3 Brief Description of the Functions The add-in functions are used the same way as functions in general under MS Excel. For example by writing “=H(A1;A2)” the enthalpy for the species in cell A1 and at the temperature in cell A2, is returned. To view all existing functions simply select “Insert, Function” from the main menu and then choose “User Defined” from the left listbox. The right listbox will now give show all available HSC functions and their arguments. A complete description of the functions is given in the following table (Table 1). General Functions UNITS(T; E) Changes the current units, T=”C” / ”K” and E=”Mcal” / “MJ” / “kWh”. Sheet NOT recalculated! BAL(Equation) Balances given equation. SPECIES(DBNo, Position) Returns species in database DBNo and given position. Species Functions H(Species;T) Returns the enthalpy (per mol) of the species at T (temperature). S(Species;T) Returns the entropy (per mol) of the species at T. CP(Species;T) Returns the heat capacity at constant pressure (per mol) of the species at T. G(Species;T) Returns the Gibbs energy (per mol) of the species at T. HKG(Species;T) Returns the enthalpy (per kg) of the species at T. HNM3 or HCM(Species;T) Returns the enthalpy (per Nm³) of the species at T. HLAT(Species;T) Returns the latent enthalpy (per mol) of the species at T. Reaction Equation Functions H(Equation;T) Returns the enthalpy (per mol) of the reaction equation at T. S(Equation;T) Returns the entropy (per mol) of the reaction equation at T. CP(Equation;T) Returns the heat capacity difference (per mol) of the reaction equation at T. G(Equation;T) Returns the Gibbs energy (per mol) of the reaction equation at T. HKG(Equation;T) Returns the enthalpy (per kg) of the reaction equation at T. K(Equation;T) Returns the equilibrium constant of the reaction equation at T. Iteration (reverse) Functions TATH(Species;H) Returns the temperature of the species with given enthalpy (per mol). TATS(Species;S) Returns the temperature of the species with given entropy (per mol). TATCP(Species;CP) Returns the temperature of the species with given heat capacity (per mol). TATG(Species;G) Returns the temperature of the species with given Gibbs energy (per mol). Flow Functions FLOWH(Species;Amount;T) Returns the enthalpy of a flow (Species and Amount as ranges) at T. FLOWS(Species;Amount;T) Returns the entropy of a flow (Species and Amount as ranges) at T. FLOWCP(Species;Amount;T) Returns the heat capacity of a flow (Species and Amount as ranges) at T. FLOWG(Species;Amount;T) Returns the Gibbs energy of a flow (Species and Amount as ranges) at T. Temperature-independent Functions STRUCT(Species) Returns the structural formula of the species. CHNAME(Species) Returns the chemical name of the species. CONAME(Species) Returns the common name of the species. CAN(Species) Returns the chemical abstract number of the species. MW(Species) Returns the molecular weight of the species. DE(Species) Returns the density of the species. MP(Species) Returns the melting point of the species. BP(Species) Returns the boiling point of the species. PHASE(Species) Returns the phase of the species. RGBCOLOR(Species) Returns the RGB color of the species. REF(Species) Returns the database reference of the species. REL(Species) Returns the reliability class of the species. Percentage Functions MOLP(Species1;Species2) Returns the mol-% of Species1 in Species2. WTP(Species1;Species2) Returns the wt-% of Species1 in Species2. Cp Data Functions CPFUNCTION(Species) Returns the complete heat capacity (cp) polynomial function (as a string) of the given species. CPA(Species) Returns the A-coefficient (as a number) of the cp-function. CPB(Species) Returns the B-coefficient (as a number) of the cp-function. CPC(Species) Returns the C-coefficient (as a number) of the cp-function. CPD(Species) Returns the D-coefficient (as a number) of the cp-function. TMIN(Species) Returns the minimum valid temperature of the cp-function. TMAX(Species) Returns the maximum valid temperature of the cp-function. Table 1: Description of all currently available HSC add-in functions. A useful Excel example file is located in the catalogue “\AddIns” in your HSC installation directory. The example, called “AddInSample.xls”, can be viewed after the add-ins have been made available (described in Chapter 27.1). Figure 2 shows what the example file should look like using MS Excel 2000. Figure 2: Example including all available Excel add-in functions (AddInSample.xls). The functions are all collected in the column “Function” and their return values under “Return value”. A red font indicates the input values with a short description of every function shown to the right. This example provides an easy method for testing the functions and also provides practice in learning how to use them. 27.4 More about registering DLL Files The HSC 5.0 installation routine should take care of all necessary DLL registrations. When selecting/deselecting the HSC 5.0 add-in using Excel, HSC5_Excel.dll is automatically registered/unregistered. However, it is also possible to register/unregister the HSC5_Excel.dll and HSC5.dll (used by the Heat and Material Balances module) files manually. The program to achieve this is called Regsvr32.exe*) and registration is completed using the following method: 1. Select “Start, Run...” from the Windows menu. 2. Type “regsvr32 HSC5_Excel.dll” for registering Excel 2000 add-in functions. 3. Type “regsvr32 HSC5.dll” for registering HSC 5.0 add-in functions. Alternatively it can be achieved using the following method: 1. Double-click the file “HSC5_Excel.dll” in your Windows System directory. 2. If the file is not registered automatically, which is indicated by an “Open With”-dialog box, choose “Other…” in the dialog window. 3. Select “Regsvr32.exe” from your Windows System directory. Now double-click the file again and it should register automatically. When unregistering files follow the same procedure, but add the /u switch before the file name, i.e. “regsvr32 /u HSC5_Excel.dll” for unregistering HSC5_Excel.dll. *Description of Regsvr32.exe To add .ocx and certain .dll files, it will be necessary to run REGSVR32.EXE from the Run option in the Start menu. The following are the commands and switches needed: regsvr32 [/u] [/s] [n] [i [:cmdline]] dll name or ocx name /u (unregistered server) /s (silent; display no message boxes) /c (console output) /i Call dll or ocx install passing it an optional [cmdline]; when used with /u calls dll or ocx uninstall /n Do not call dll RegisterServer; this option must be used with /i