HSC Chemistry® 6.0 27 - 1 Peter Bjorklund August 10, 2006 06120-ORC-T 27. Excel Add-In Functions 27.1 Getting started With HSC 6.0 Add-In Functions it is possible to use the HSC 6.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 may not work under earlier Excel versions!) 2. Select “Tools, Add-Ins...” from the menu. 3. Select “Browse…” and locate HSC6.XLL from your HSC6\AddIns folder. 4. Select “HSC 6.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 6.0 Functions under Excel Please do NOT select old HSC 5.1 Functions!. Activation Stage 2 (Optional): 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 HSC6.XLL from your HSC directory (for example C:\HSC6\HSC6.XLL). It may be necessary to restart Excel in order for the add-in functions to work. HSC Add-In functions locate in HSC6.DLL, however, Excel calls these functions through HSC6.XLL interface. 4. See also stage 3 in Chapter 27.2 if needed. The HSC installation routine automatically takes care of the HSC6.DLL registration. HSC Chemistry® 6.0 27 - 2 Peter Bjorklund August 10, 2006 06120-ORC-T Activation Stage 3 (Optional): 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 HSC6.XLL from the listbox and press “Change Source”. 4. Browse to your HSC6\AddIns folder and choose HSC6.XLL. 27.2 Updating XLS-Files which use old HSC 5.1 Add-In Functions Figure 2: Update XLS Files.xls macro may be used to update old Excel files with HSC 5.1 Add-In functions to HSC 6.0 Add-Ins. You must update old Excel files with HSC 5.1 Add-In functions. If you find following type formulas from your Excel spreadsheet then you must update: ='C:\HSC5\AddIns_BackUp\HSC5.xla'!H(D11;E11) HSC 5.1 use old HSC5.XLA interface, HSC 6.0 Add-Ins use better HSC6.XLL interface which makes the use of these files much more easier because the path to the HSC6.XLL files is not saved into the XLS-file cells! HSC Chemistry® 6.0 27 - 3 Peter Bjorklund August 10, 2006 06120-ORC-T 27.3 Brief Description of the Functions The " AddInSample.xls" sample file offers the fastest way to start using HSC 6 Add-In functions, you may found it from HSC 6 folder, such as: C:\HSC6\AddIns\AddInSample.xls 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 tables 1 and 2. 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 3 shows what the example file should look like using MS Excel 2000. 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. HSC Chemistry® 6.0 27 - 4 Peter Bjorklund August 10, 2006 06120-ORC-T HSC Add-In Functions - Species Based Functions Descriptions (Return values) General Description (Return value): UNITS(T;E) Changes the temperature and energy units BAL(Equation) Balanced reaction equation SPECIES(DBNo,Position) Species formula in given database and record number Species Description (Return value): H(Species;T) Enthalpy (per kmol) of species at given temperature HKG(Species;T) Enthalpy (per kg) of species at given temperature HNM3 or HCM(Species;T) Enthalpy (per Nm³) of species at given temperature HLAT(Species;T) Enthalpy excluding phase transformations (per mol) of species at given temperature S(Species;T) Entropy (per Mmol) of species at given temperature CP(Species;T) Heat capacity (per Mmol) of species at given temperature G(Species;T) Gibbs energy (per kmol) of species at given temperature Reaction equation Description (Return value): H(Equation;T) Enthalpy difference (per kmol) of reaction equation at given temperature. HKG(Equation;T) Enthalpy difference (per kg) of reaction equation at given temperature S(Equation;T) Entropy difference (per Mmol) of reaction equation at given temperature CP(Equation;T) Heat capacity difference (per Mmol) of reaction equation at given temperature G(Equation;T) Gibbs energy difference (per kmol) of reaction equation at given temperature K(Equation;T) Equilibrium constant of reaction equation at given temperature Iteration (reverse) Description (Return value): TATH(Species;H) Temperature of species at given enthalpy (per kmol) TATHKG(Species;H) Temperature of species at given enthalpy (per kg) TATHNM3 or TATHCM(Species;H) Temperature of species at given enthalpy (per Nm3) TATHLAT(Species;H) Temperature of species (per kmol) at given enthalpy excluding phase transformations TATS(Species;S) Temperature of species at given entropy (per Mmol). TATCP(Species;CP) Temperature of species at given heat capacity (per Mmol) TATG(Species;G) Temperature of species at given Gibbs energy (per kmol) Temp. independent Description (Return value): STRUCT(Species) Structural formula of given species CHNAME(Species) Chemical name of given species CONAME(Species) Common name of given species CAN(Species) Chemical abstract number of given species MW(Species) Molecular weight of given species [kg/kmol] DE(Species) Density of given species [kg/l] MP(Species) Melting point of given species BP(Species) Boiling point of given species PHASE(Species) Phase type of given species RGBCOLOR(Species) RGB color code of given species REF(Species) Reference of given species REL(Species) Reliability class of given species Percentage Description (Return value): MOLP(Species1;Species2) Species 1 content in Species 2 in mol-% WTP(Species1;Species2) Species 1 content in Species 2 in wt-% CP-function related Description (Return value): CPFUNCTION(Species) Heat capacity (Cp) polynomial function of given species CPA(Species) A coefficient in Cp-function of given species CPB(Species) B coefficient in Cp-function of given species CPC(Species) C coefficient in Cp-function of given species CPD(Species) D coefficient in Cp-function of given species TMIN(Species) Lower limit of Cp-function temperature range TMAX(Species) Upper limit of Cp-function temperature range Table 1: Description of all currently available HSC Species based add-in functions. HSC Chemistry® 6.0 27 - 5 Peter Bjorklund August 10, 2006 06120-ORC-T HSC Add-In Functions - Stream Based Functions Descriptions (Return values) Stream/Flow Description (Return value): STREAMH(Species;Amount;T) Enthalpy of the specified stream/flow (species in kmol) at given temperature FLOWH(Species;Amount;T) STREAMHKG(Species;Amount;T) Enthalpy of the specified stream/flow (species in kg) at given temperature FLOWHKG(Species;Amount;T) STREAMHNM3(Species;Amount;T) Enthalpy of the specified stream/flow (species in Nm3) at given temperature FLOWHNM3(Species;Amount;T) STREAMHLAT(Species;Amount;T) Enthalpy excluding phase transformations of the specified stream/flow (species in kmol) at given temperature FLOWHLAT(Species;Amount;T) STREAMS(Species;Amount;T) Entropy of specified stream/flow (species in Mmol) at given temperature FLOWS(Species;Amount;T) STREAMCP(Species;Amount;T) Heat capacity of specified stream/flow (species in Mmol) at given temperature FLOWCP(Species;Amount;T) STREAMG(Species;Amount;T) Gibbs energy of specified stream/flow (species in kmol) at given temperature FLOWG(Species;Amount;T) DensityA(Species;Amount;T) Density of aqueous solution (kg/m3) Arg. 2 = Weight fractions, max = 1, Arg. 3 = temperature °C Stream/Flow iteration (reverse) Description (Return value): STREAMTH(Species;Amount;H;Tmin;TMax) Temperature of stream/flow (species in kmol) at given enthalpy between Tmin and Tmax FLOWTH(Species;Amount;H;Tmin;TMax) Outside range returns #VALUE! STREAMTHKG(Species;Amount;H;TMin;TMax) Temperature of stream/flow (species in kg) at given enthalpy between Tmin and Tmax FLOWTHKG(Species;Amount;H;Tmin;TMax) Outside range returns #VALUE! STREAMTHNM3(Species;Amount;H;Tmin;TMax) Temperature of stream/flow (species in Nm3) at given enthalpy between Tmin and Tmax FLOWTHNM3(Species;Amount;H;Tmin;TMax) Outside range returns #VALUE! STREAMTHLAT(Species;Amount;H;Tmin;TMax) Temperature of stream/flow (species in kmol) at given enthalpy excluding phase transformations between Tmin and Tmax FLOWTHLAT(Species;Amount;H;Tmin;TMax) Outside range returns #VALUE! STREAMTS(Species;Amount;S;Tmin;TMax) Temperature of stream/flow (species in Mmol) at given entropy between Tmin and Tmax FLOWTS(Species;Amount;S;Tmin;TMax) Outside range returns #VALUE! STREAMTCP(Species;Amount;CP;Tmin;TMax) Temperature of stream/flow (species in Mmol) at given heat capacity between Tmin and Tmax FLOWTH(Species;Amount;H;Tmin;TMax) Outside range returns #VALUE! STREAMTG(Species;Amount;G;Tmin;TMax) Temperature of stream/flow (species in kmol) at given gibbs energy between Tmin and Tmax FLOWTG(Species;Amount;G;Tmin;TMax) Outside range returns #VALUE! Equilibrium Compositions Description (Return value): StreamEQ(Species;Input;Output, T, P) Equlibrium amounts of species based on given amounts, temperature and pressure. Table 2: Description of all currently available HSC Stream based add-in functions. HSC Chemistry® 6.0 27 - 6 Peter Bjorklund August 10, 2006 06120-ORC-T Figure 3: Example including all available Excel add-in functions (AddInSample.xls). HSC Chemistry® 6.0 27 - 7 Peter Bjorklund August 10, 2006 06120-ORC-T 27.4 Stream Equilibrium Function (Array Functions) The array functions are created in different way than normal spreadsheet functions. The most important array function of HSC-Sim is StreamEQ which calculates the amounts of species in the equilibrium state at given temperature and pressure. Fig. 4. Selection for array function. You may create array function in MS Excel and HSC-Sim using the same procedure: 1. Create continuos list of phases and species in one column. Important: The phase name string must always start with §-character, Fig. 4. 2. Specify temperatures of the phases into the next column on the §-phase-row. 3. Specify the input amounts (moles) of the species into the next column. 4. Select array function cell range, see Fig. 4. 5. Type array function: =StreamEQ(A4:A15;B4:B15;C1;C2), Fig. 5. 6. Keep Crtl + Shift keys down and press Enter, Fig. 6. 7. The array function is ready if it is within brackets, Fig. 6. Please note that the array function cell range must be continuous, it is like a solid and fixed block within spreadsheet. Do not brake off this cell range with uncontinuous copy- paste, insert rows, delete rows, etc. operations. HSC Chemistry® 6.0 27 - 8 Peter Bjorklund August 10, 2006 06120-ORC-T Fig. 5. Type array function. Fig. 6. Keep Crtl + Shift keys down and press Enter. HSC Chemistry® 6.0 27 - 9 Peter Bjorklund August 10, 2006 06120-ORC-T 27.5 HSC AddIn Functions Some of the AddIn functions are available on in HSC Chemistry streadsheets but not in MS Excel. Currently the StreamX is such a function: Function StreamX Syntax StreamX(StremName;ParticlesRange;Variable) Examples StreamX(E$6;$E$56:$E$97;$B8) StreamX(“ROM”;$E$56:$E$97;”SiO2”) Explanation Returns the value of the variable, e.g. SiO2 content of the stream calculated according to particles. Note that particle data can be corrected to be in harmony with chemical and mineral composition of the bulk feed. Applications StreamX functions is used in Mineral Based Models Other HSC AddIn functions are StreamX, RecoveryX, ParticleRecX, FractionX, MineralX. These are described in more details in chapter "57. Mineral Based Models". HSC Chemistry® 6.0 27 - 10 Peter Bjorklund August 10, 2006 06120-ORC-T 27.6 More about registering DLL Files The HSC 6.0 installation routine should take care of all necessary DLL registrations. When selecting/deselecting the HSC 6.0 add-in using Excel, HSC6.DLL is automatically registered/unregistered. However, it is also possible to register/unregister the HSC6.DLL file manually. The program to achieve this is called Regsvr32.exe*) and registration is completed using the following method: Note: For other HSC6 installation paths than C:\HSC6, simply use your path instead in the instuctions below. 1. Select “Start, Run...” from the Windows menu. 2. Type “regsvr32 C:\HSC6\HSC6.dll” for registering HSC 6.0 add-in functions. Alternatively it can be achieved using the following method: 1. Double-click the file “HSC6.DLL” in your HSC 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 C:\HSC6\HSC6.DLL” for unregistering HSC6.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