Application of self defined excel function in wate

Application of Excel custom function in water quality work

excel is a very familiar application software, which is very convenient to use. In particular, its powerful function brings us great convenience, but sometimes we may use several functions or adopt nested functions to realize a specified function, which is not convenient to apply; Or if you use a custom formula for calculation, there is no ready-made formula available. Fortunately, we can create a user-defined function (UDF), which runs exactly the same as the functions in Excel. There are several advantages in establishing user-defined functions: establishing functions with their own special functions and names; It can combine complex, nested and multiple original functions, give play to greater power, and make use and maintenance more convenient

for example, in the calculation of water quality, the calculation formula of permanganate index is very cumbersome, and the result must be rounded to 0.1, and the problem of every 5 odd entries and even rounding must be considered. It is troublesome to calculate step by step with a calculator; If you don't pay attention to using cell reference calculation in Excel, it may cause errors in calculation, and it has to be established every time, which is very inconvenient. Now we use the VBA device of Excel to build our own functions to solve this problem

first enter excel, In [tools → [macro → [visual basic device (you can also press the combination key [alt+f11), select [insert → [add module] in "visual basic device", change the name of the module to "permanganate index concentration calculation formula", enter the following common ball screws and trapezoidal screw functions in the code window:

public function cimn (C as single, V0 as single, V1 as single, V2 as single, V as integer) 'permanganate index calculation formula

dim dotlocation As Integer' defines decimal point operation. In addition, drainage and exhaust device position variables

'******************************************************************************* Reflector cover and tailgate

cimn = (((((10 + V1) * 10/V2 - 10) - (((10 + V0) * 10/V2 - 10) * (100 - V)/100)) * c * 8000/V

'**************************************************************************************************************, Automatically fill up the last digit 0

if cimn 1 then

cimn = 0 cimn

end if

'determine the position of the decimal point

dotlocation = instr (cimn, ".")

'judge whether the second digit after the decimal point is 5

if mid (cimn, dotlocation + 2, 1) = 5 then

if len (cimn) = dotlocation + 2 and mid (cimn, dotlocation + 1, 1) mod load is low 2 = 0 then

cimn = left (cimn, dotlocation + 1)' is 5 and there is no number after 5 and the previous digit of 5 is even, Round() function directly

else

cimn = round (cimn, 1)

end if

else

cimn = round (cimn, 1) 'not 5, round() function directly

end if

cimn = format (round (cimn, 1), "#0.0")

end function <./p>

we know that functions in Excel have a description to help use, and we also need to add a description to this function. Select the "object browser" in the toolbar (as shown in Figure 1), and select the "permanganate index concentration calculation formula" module we made, Add a description of this function in its [right click] → [attribute. This description will appear in the description of the function in Excel. If you want to keep the software confidential, right-click on the "module", and [vbaproject attribute] → [set password in protection, and others will not see your source program.

Figure 1 describes the function

at this time, exit, return to the excel interface, and save this file as: type is "Microsoft Excel add in", in Excel 2003, it will automatically change the save location to "c:documents and settingswzwemc (computer user name) application datamicrosoftaddins" (the system is installed in c: ＼ windows). Of course, you can also copy this file "permanganate index concentration calculation formula.Xla" directly to the office ＼ Library (under the installation path of office), In Excel 97, it can only be placed in the latter position. Using functions is simple, Click [tools → [add in], and check the "permanganate index concentration calculation formula" you created, as shown in Figure 2. Directly enter "=cimn()" in the cell, and select the corresponding parameters (as shown in Figure 3) 。 Is it a little professional like the functions provided by Microsoft office

Figure 2 add in

Figure 3 uses the user-defined function

if you copy the excel file that calls this macro to another machine to run, the prompt "the current document to be opened contains links to other documents, do you want to update the current workbook with changes in other workbooks" will appear, and the originally calculated data can be displayed, This is because in [tools → [options → [recalculation] in Excel, generally select "save external link data", but you can't recalculate, because you can't link this macro, and this function doesn't exist on other machines at all.

in addition, we can add relevant instructions for the "add in", which is more conducive to the maintenance of custom functions. (as shown in Figure 3), find the "permanganate index concentration calculation formula.Xla" , click [right click → [attribute → [Summary (as shown in Figure 4), and add the required content to the description to describe the function. The "title" part will appear in the box of "add in", and the "remarks" part will appear in the function description part below. At this time, see whether it is professional enough. In Excel