# Application of self defined excel function in wate

2022-08-21
• Detail

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