MIT License
Copyright (c) 2021 - to present Denise Wolrab, Eva Cífková, Pavel Čáň, Miroslav Lísa, Ondřej Peterka, Michaela Chocholoušková, Robert Jirásko, and Michal Holčapek
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
********

ThisWorkbook - 1 
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call start_clear 
End Sub 
Private Sub Workbook_Open() 
End Sub 


Module1 - 1 

Option ExplicitOption Base 1Private c_poc, r_poc, kon As LongPrivate rozp_l, rozp_h As SinglePrivate list As StringPrivate aDB(), aIS() As SinglePrivate aDB_name(), aIS_name() As String 

Sub Start_start()
Dim i, j, c, r, x, x_sub As Integer 

list = Sheets("support").Cells(2, 2).Valuerozp_l = Sheets(list).Cells(3, 1).Valuerozp_h = Sheets(list).Cells(5, 1).Value

 'Creating internal standard databaseReDim aIS(3, 3)
ReDim aIS_name(3)
'Creating internal standard 2D database - individual standards = column

 ' 1st row poition of DB' 2nd row M+H 
' 3rd row concentration

 For i = 1 To 3 
aIS(i, 1) = Sheets(list).Cells(2 + i, 3).ValueaIS(i, 2) = Sheets(list).Cells(2 + i, 4).ValueaIS(i, 3) = Sheets(list).Cells(2 + i, 5).ValueaIS_name(i) = Sheets(list).Cells(2 + i, 2).Value

Next i 

x = 1 
ReDim aDB_name(1)
'Creating the database name

Do Until Sheets(list).Cells(x + 9, 1) = ""
ReDim Preserve aDB_name(x)
aDB_name(x) = Sheets(list).Cells(x + 9, 2).Value

x = x + 1 
Loop 

ReDim aDB(UBound(aDB_name, 1), 6)

'Creating 2D database of imported values - individual components = rows : 
' 1st column - M+H ;
' 2nd column - Isotope correction M+2' 3rd column - Isotope correction M+1,
' 4th column - IS 
' 5th column - Sum of values 
' 6th column - Sum of close values 

For x = 1 To UBound(aDB_name, 1)

 i = 1 
aDB(x, i) = Sheets(list).Cells(x + 9, i).ValueFor i = 3 To 5 
aDB(x, i - 1) = Sheets(list).Cells(x + 9, i).ValueNext i 
Next x

 c_poc = 0r_poc = 0

 For c = 1 To 10 
For r = 1 To 10 
If Sheets("start").Cells(r, c).Value = "m/z" Then

c_poc = cr_poc = rc = 10 
Exit For

 End If

 Next r 
Next c 
If c_poc = 0 ThenMsgBox ("Program nenalezl buňku obsahující m/z") 


Module1 - 2

 Exit Sub 
End If

 'Control of filter 
If Sheets("start").AutoFilterMode = True Then Sheets("start").AutoFilterMode = False 

Range(Cells(r_poc, c_poc), Cells(r_poc + 100000, c_poc)).SelectActiveWorkbook.Worksheets("start").Sort.SortFields.Add Key:=Range(Cells(r_poc, c_poc), Cells(r_poc + 10000, c_poc)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("start").Sort.SetRange Range(Cells(r_poc, 1), Cells(r_poc + 100000, 2500))
.Header = xlYes 
.MatchCase = False 
.Orientation = xlTopToBottom.SortMethod = xlPinYin 
.Apply

End With

 If r_poc < 7 Then Range(Rows(1), Rows(8 - r_poc)).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove: r_poc = 8If c_poc < 8 Then Range(Columns(1), Columns(9 - c_poc)).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove: c_poc = 9

 ' Columns("A:f").Select' Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove' c_poc = c_poc + 6

'přidává 6 popisySheets("start").Cells(1, 1).Value = "Database:"
Sheets("start").Cells(1, 2).Value = listSheets("start").Cells(2, 1).Value = "Range min: [Da]"
Sheets("start").Cells(2, 2).Value = rozp_lSheets("start").Cells(3, 1).Value = "Range max: [Da]"
Sheets("start").Cells(3, 2).Value = rozp_h 

Sheets("start").Cells(r_poc, 1).Value = "M + H"
Sheets("start").Cells(r_poc, 2).Value = "Species"
Sheets("start").Cells(r_poc, 3).Value = "Number of identified species in range"
Sheets("start").Cells(r_poc, 4).Value = "Number of identified species in 2 x range"
Sheets("start").Cells(r_poc, 5).Value = "Raw in database"
Sheets("start").Cells(r_poc, 6).Value = "Code of filter" 

Application.Calculation = xlManual'Searching the correct resultsr = 1 
Do Until Sheets("start").Cells(r_poc + r, c_poc).Value = ""

x_sub = 1

 For x = x_sub To UBound(aDB, 1)
If Sheets("start").Cells(r_poc + r, c_poc).Value > aDB(x, 1) + 2 * rozp_l And Sheets("start").Cells(r_poc + r, c_poc).Value < aDB(x, 1) + 2 * rozp_h Then

 Sheets("start").Cells(r_poc + r, 6).Value = 2aDB(x, 6) = aDB(x, 6) + 1

 'Borders 
Sheets("start").Cells(r_poc + r, 1).Borders().ColorIndex = 0Sheets("start").Cells(r_poc + r, 2).Borders().ColorIndex = 0Sheets("start").Cells(r_poc + r, 3).Borders().ColorIndex = 0Sheets("start").Cells(r_poc + r, 4).Borders().ColorIndex = 0Sheets("start").Cells(r_poc + r, 5).Borders().ColorIndex = 0

'Font and color 
Sheets("start").Cells(r_poc + r, 1).Interior.Color = 14540253 'greySheets("start").Cells(r_poc + r, 1).NumberFormat = "0.0"
Sheets("start").Cells(r_poc + r, 2).Interior.Color = 39423 'orangeSheets("start").Cells(r_poc + r, 2).Font.FontStyle = "Tučné" 
Sheets("start").Cells(r_poc + r, 3).Interior.Color = 13434879 'yellowSheets("start").Cells(r_poc + r, 5).Font.FontStyle = "Tučné" 

If Sheets("start").Cells(r_poc + r, c_poc).Value > aDB(x, 1) + rozp_l And Sheets("start").Cells(r_poc + r, c_poc).Value < aDB(x, 1) + rozp_h Then 


Module1 - 3

 Sheets("start").Cells(r_poc + r, 6).Value = 1Sheets("start").Cells(r_poc + r, 5).Interior.Color = 65535 ' yellowSheets("start").Cells(r_poc + r, 5).Value = x

aDB(x, 5) = aDB(x, 5) + 1 

If aDB(x, 5) = 0 ThenElseIf aDB(x, 5) = 1 Then Sheets("start").Cells(r_poc + r, 3).Interior.Color = 3407718 'green 
Else: Sheets("start").Cells(r_poc + r, 3).Interior.Color = 13311 'red 
Sheets("start").Cells(r_poc + r - 1, 3).Interior.Color = 13311 'redEnd If

 End If 
Sheets("start").Cells(r_poc + r, 1).Value = aDB(x, 1)
Sheets("start").Cells(r_poc + r, 2).Value = aDB_name(x)
Sheets("start").Cells(r_poc + r, 3).Value = aDB(x, 5)
Sheets("start").Cells(r_poc + r, 4).Value = aDB(x, 6) - aDB(x, 5)

 x_sub = x 
Exit For 
End If 
Next x 
r = r + 1

 Loopkon = r + r_pocRange(Sheets("Start").Cells(r_poc, 1), Sheets("Start").Cells(kon, 200)).AutoFilter Field:=6, Cr

iteria1:="1", Operator:=xlOr, Criteria2:="2" 

Application.Calculation = xlAutomatic 

End Sub 

Sub Start_re_start()
Dim rozp_l, rozp_h As Single 

Dim i, j, c, r, x, x_sub As Integer 

rozp_l = Sheets("start").Cells(2, 2).Valuerozp_h = Sheets("start").Cells(3, 2).Value 

Range(Sheets("start").Cells(r_poc + 1, 1), Sheets("start").Cells(kon, 6)).clearIf Sheets("start").AutoFilterMode = True Then Sheets("start").AutoFilterMode = False

 For x = 1 To UBound(aDB, 1)
aDB(x, 5) = 0aDB(x, 6) = 0

Next x 

Application.Calculation = xlManual'Find the correct results 
r = 1 
Do Until Sheets("start").Cells(r_poc + r, c_poc).Value = ""

x_sub = 1

 For x = x_sub To UBound(aDB, 1)
If Sheets("start").Cells(r_poc + r, c_poc).Value > aDB(x, 1) + 2 * rozp_l And Sheets("start").Cells(r_poc + r, c_poc).Value < aDB(x, 1) + 2 * rozp_h Then

 Sheets("start").Cells(r_poc + r, 6).Value = 2aDB(x, 6) = aDB(x, 6) + 1

 'Borders 
Sheets("start").Cells(r_poc + r, 1).Borders().ColorIndex = 0Sheets("start").Cells(r_poc + r, 2).Borders().ColorIndex = 0Sheets("start").Cells(r_poc + r, 3).Borders().ColorIndex = 0Sheets("start").Cells(r_poc + r, 4).Borders().ColorIndex = 0

'Font and color 
Sheets("start").Cells(r_poc + r, 1).Interior.Color = 14540253 'greySheets("start").Cells(r_poc + r, 1).NumberFormat = "0.0"
Sheets("start").Cells(r_poc + r, 2).Interior.Color = 39423 'orangeSheets("start").Cells(r_poc + r, 2).Font.FontStyle = "Tučné" 
Sheets("start").Cells(r_poc + r, 3).Interior.Color = 13434879 'yellow 


Module1 - 4 

Sheets("start").Cells(r_poc + r, 5).Font.FontStyle = "Tučné" 

If Sheets("start").Cells(r_poc + r, c_poc).Value > aDB(x, 1) + rozp_l And Sheets("s

tart").Cells(r_poc + r, c_poc).Value < aDB(x, 1) + rozp_h ThenSheets("start").Cells(r_poc + r, 6).Value = 1Sheets("start").Cells(r_poc + r, 5).Value = xSheets("start").Cells(r_poc + r, 5).Interior.Color = 65535 ' yellow

aDB(x, 5) = aDB(x, 5) + 1

 If aDB(x, 5) = 0 ThenElseIf aDB(x, 5) = 1 Then Sheets("start").Cells(r_poc + r, 3).Interior.Color = 3407

718 'green 
Else: Sheets("start").Cells(r_poc + r, 3).Interior.Color = 13311 'red 
End If

 End If 
Sheets("start").Cells(r_poc + r, 1).Value = aDB(x, 1)
Sheets("start").Cells(r_poc + r, 2).Value = aDB_name(x)
Sheets("start").Cells(r_poc + r, 3).Value = aDB(x, 5)
Sheets("start").Cells(r_poc + r, 4).Value = aDB(x, 6)

 x_sub = x 
Exit For 
End If 
Next x 
r = r + 1

 Loopkon = r + r_pocRange(Sheets("Start").Cells(r_poc, 1), Sheets("Start").Cells(kon, 200)).AutoFilter Field:=6, Cr

iteria1:="1", Operator:=xlOr, Criteria2:="2" 

Application.Calculation = xlAutomatic 

End Sub 

Sub Start_move()
Dim i, j, x, y, r As IntegerDim aMD_hod(), aMDI(), aMDC() As SingleDim r0, c0, r1 As IntegerDim kor_m1, kor_m22, kor_m21 As SingleDim aMD_name() As String 

Application.Calculation = xlManual 

y = 1'number of sample and replicate aSample_nameDo Until Sheets("Start").Cells(r_poc, c_poc + 3 + y) = ""
ReDim Preserve aMD_name(1 To y)
aMD_name(y) = Sheets("Start").Cells(r_poc, c_poc + 3 + y)
y = y + 1Loop 

ReDim aMD_hod(UBound(aDB, 1), UBound(aMD_name, 1) + 2)

 'creating of database of samples, rows (x) M+H find in BD using column 5) and column (y) arevalues from individual measurements

 '1st row intensity for 1. M+H v DB'2nd row intensity for 2. M+H v DB'the last but one for M+H in samples'last row m/z M+H number of row in column 5

 ' 1st column values for 1st measurement 
' 2nd column values for 2nd measurement 
r = 1: y = 1Do Until Sheets("start").Cells(r_poc + r, c_poc).Value = ""

 'loading of measurement resultsIf Sheets("start").Cells(r_poc + r, 5).Value <> "" Thenx = Sheets("start").Cells(r_poc + r, 5).ValueIf aMD_hod(x, 1) <> "" Then MsgBox "Hodnota M+H: " & aDB(x, 1) & " na pozici: " & x & " už

 byla načtena." 


Module1 - 5

 For y = 1 To UBound(aMD_name, 1)
aMD_hod(x, y) = Sheets("start").Cells(r_poc + r, c_poc + 3 + y).ValueNext yaMD_hod(x, UBound(aMD_name, 1) + 1) = Sheets("start").Cells(r_poc + r, c_poc).ValueaMD_hod(x, UBound(aMD_name, 1) + 2) = Sheets("start").Cells(r_poc + r, 5).Value

 End If 
r = r + 1 
Loop

 'creating database of samples, rows (x) M+H find in BD using column 5) and column (y) are v 
alues from individual measurements

 '1st row correlated intensity'2nd row correlated intensity'3rd row correlated intensity

 ' 1st column value from 1st measurement 
' 2nd column values for 2nd measurement

 ReDim aMDI(UBound(aDB, 1), UBound(aMD_name, 1))
' Calculation of intensities 
For x = 1 To UBound(aDB, 1)

kor_m21 = 0: kor_m22 = 0: kor_m1 = 0 
If x > 1 Then If Abs(aDB(x, 1) - aDB(x - 1, 1) - 1) < 0.4 Then kor_m1 = aDB(x, 3)
If x > 2 Then

 If Abs(aDB(x, 1) - aDB(x - 2, 1) - 2) < 0.04 Then kor_m22 = aDB(x, 2)
If Abs(aDB(x, 1) - aDB(x - 1, 1) - 2) < 0.04 Then kor_m21 = aDB(x, 2)
End If 
For y = 1 To UBound(aMD_name, 1)

If aMD_hod(x, y) <> 0 ThenIf x = 1 Then aMDI(x, y) = aMD_hod(x, y)
If x = 2 Then aMDI(x, y) = aMD_hod(x, y) - kor_m1 * aMD_hod(x - 1, y)
If x > 2 Then aMDI(x, y) = aMD_hod(x, y) - kor_m1 * aMDI(x - 1, y) - kor_m22 * aMDI(x 


2, y) - kor_m21 * aMDI(x - 1, y)

 Else: aMDI(x, y) = 0

End If 
Next yNext x

 'creating database of samples, rows (x) M+H find in BD using column 5) and column (y) are

values from individual measurements 
ReDim aMDC(UBound(aMDI, 1), UBound(aMDI, 2))
'Calculation of concentration 
For x = 1 To UBound(aMDI, 1)
For y = 1 To UBound(aMDI, 2)

If aMDI(x, y) <> 0 And aMDI(aIS(aDB(x, 4), 1), y) <> 0 ThenaMDC(x, y) = aMDI(x, y) / aMDI(aIS(aDB(x, 4), 1), y) * aIS(aDB(x, 4), 3)

 End If 
Next yNext x

 'Listing concentration resultsr0 = 9 ' 1st row of results 
c0 = 5 ' 1st column of results

 Range(Sheets(list).Cells(r0, c0 + 1), Sheets(list).Cells(r0 + 5 + UBound(aMDC, 1), c0 + 1 + UB

ound(aMD_name, 1))).clearFor y = 1 To UBound(aMD_name, 1)
Sheets(list).Cells(r0, y + c0).Value = aMD_name(y)
For x = 1 To UBound(aMDC, 1)

If aMDC(x, y) > 0 ThenSheets(list).Cells(r0 + x, y + c0).Value = aMDC(x, y)
Sheets(list).Cells(r0 + x, y + c0).NumberFormat = "0.00"

End If 
Next x

 Next y'Listing of control calculationsr1 = 300

 Range(Sheets(list).Cells(r1 - 4, 1), Sheets(list).Cells(r1 + UBound(aMDC, 1), 12 + UBound(aMD_name, 1))).clearSheets(list).Cells(r1 - 4, 10).Value = "IS" 


Module1 - 6

 Sheets(list).Cells(r1 - 3, 10).Value = aIS_name(1)
Sheets(list).Cells(r1 - 2, 10).Value = aIS_name(2)
Sheets(list).Cells(r1 - 1, 10).Value = aIS_name(3)
Sheets(list).Cells(r1 - 4, 11).Value = "m/z IS"
Sheets(list).Cells(r1 - 3, 11).Value = aIS(1, 2)
Sheets(list).Cells(r1 - 2, 11).Value = aIS(2, 2)
Sheets(list).Cells(r1 - 1, 11).Value = aIS(3, 2)
Sheets(list).Cells(r1 - 4, 12).Value = "c"
Sheets(list).Cells(r1 - 3, 12).Value = aIS(1, 3)
Sheets(list).Cells(r1 - 2, 12).Value = aIS(2, 3)
Sheets(list).Cells(r1 - 1, 12).Value = aIS(3, 3)
Sheets(list).Cells(r1, 1).Value = " m/z teor."
Sheets(list).Cells(r1, 2).Value = "Species"
Sheets(list).Cells(r1, 3).Value = "M+2"
Sheets(list).Cells(r1, 4).Value = "M+1"
Sheets(list).Cells(r1, 5).Value = "IS"
Sheets(list).Cells(r1, 6).Value = "m/z exp."
Sheets(list).Cells(r1, 7).Value = "I (first sample)"
Sheets(list).Cells(r1, 8).Value = "I cor (first sample) "
Sheets(list).Cells(r1, 9).Value = "c (first sample)"
Sheets(list).Cells(r1, 10).Value = "I (last sample)"
Sheets(list).Cells(r1, 11).Value = "I cor (last sample)"
Sheets(list).Cells(r1, 12).Value = "c (last sample)"

 For y = 1 To UBound(aMDC, 2)
Sheets(list).Cells(r1 - 3, 12 + y).Value = aMDI(aIS(1, 1), y)
Sheets(list).Cells(r1 - 3, 12 + y).NumberFormat = "0.000"
Sheets(list).Cells(r1 - 2, 12 + y).Value = aMDI(aIS(2, 1), y)
Sheets(list).Cells(r1 - 2, 12 + y).NumberFormat = "0.000"
Sheets(list).Cells(r1 - 1, 12 + y).Value = aMDI(aIS(3, 1), y)
Sheets(list).Cells(r1 - 1, 12 + y).NumberFormat = "0.000"
Sheets(list).Cells(r1, y + 12).Value = aMD_name(y)

Next y

For x = 1 To UBound(aMDC, 1)
Sheets(list).Cells(r1 + x, 1).Value = aDB(x, 1)
Sheets(list).Cells(r1 + x, 2).Value = aDB_name(x)
Sheets(list).Cells(r1 + x, 3).Value = aDB(x, 2)
Sheets(list).Cells(r1 + x, 4).Value = aDB(x, 3)
Sheets(list).Cells(r1 + x, 5).Value = aIS_name(aDB(x, 4))
Sheets(list).Cells(r1 + x, 6).Value = aMD_hod(x, UBound(aMD_name, 1) + 1)
Sheets(list).Cells(r1 + x, 7).Value = aMD_hod(x, 1)
Sheets(list).Cells(r1 + x, 8).Value = aMDI(x, 1)
Sheets(list).Cells(r1 + x, 9).Value = aMDC(x, 1)
Sheets(list).Cells(r1 + x, 10).Value = aMD_hod(x, UBound(aMD_name, 1))
Sheets(list).Cells(r1 + x, 11).Value = aMDI(x, UBound(aMD_name, 1))
Sheets(list).Cells(r1 + x, 12).Value = aMDC(x, UBound(aMD_name, 1))

For y = 1 To UBound(aMD_name, 1)
Sheets(list).Cells(r1 + x, y + 12).Value = aMDI(x, y)
If aMDI(x, y) <> aMD_hod(x, y) Then Sheets(list).Cells(r1 + x, y + 12).Interior.Color = 14540

253 ' 
Sheets(list).Cells(r1 + x, y + 12).NumberFormat = "0.000"
Next yNext x

 Application.Calculation = xlAutomatic

 MsgBox "Finish" 

End Sub 

Sub Start_Download_Change() 

End Sub 


Module2 - 1 

Sub start_clear()
If Sheets("start").AutoFilterMode = True Then Sheets("start").AutoFilterMode = FalseRange(Worksheets("start").Cells(1, 1), Worksheets("start").Cells(100000, 2500)).clearWorksheets("start").Columns("A:F").Delete Shift:=xlToLeftr = 0 
Do Until Sheets("support").Cells(4 + r, 2).Value = 0

Range(Sheets(Sheets("support").Cells(4 + r, 2).Value).Cells(280, 1), Sheets(Sheets("support").Cells(4 + r, 2).Value).Cells(800, 2500)).clear

 r = r + 1

 LoopActiveWorkbook.Save 
End Sub

 Sub clear_results()
Range(Sheets("results").Cells(3, 7), Sheets("results").Cells(1500, 2500)).Value = ""
Range(Sheets("results").Cells(5, 2), Sheets("results").Cells(1500, 6)).Value = ""
Range(Sheets("average").Cells(3, 7), Sheets("average").Cells(1500, 2500)).Value = ""
Range(Sheets("average").Cells(5, 2), Sheets("average").Cells(1500, 6)).Value = "" ' Deleting

of old values 
Range(Sheets("deviation").Cells(3, 7), Sheets("deviation").Cells(1500, 2500)).Value = ""
Range(Sheets("deviation").Cells(5, 2), Sheets("deviation").Cells(1500, 6)).Value = ""

End Sub 

Sub clear_concentration_all()
r = 0 
Do Until Sheets("support").Cells(4 + r, 2).Value = 0Range(Sheets(Sheets("support").Cells(4 + r, 2).Value).Cells(9, 6), Sheets(Sheets("support").Cells(4 + r, 2).Value).Cells(280, 2500)).Value = ""
Range(Sheets(Sheets("support").Cells(4 + r, 2).Value).Cells(280, 1), Sheets(Sheets("support").Cells(4 + r, 2).Value).Cells(800, 2500)).clear

 r = r + 1 
LoopEnd Sub

 Sub clear_concentration()

 Range(Cells(9, 6), Cells(280, 2500)).Value = ""
Range(Cells(280, 1), Cells(800, 2500)).clear

 End Sub 
Sub Download_Change()

 ' Selecting the sheetEnd Sub 


Module3 - 1 

Option ExplicitOption Base 1 

Sub results_insert_data()
Dim list As StringDim r_list, x, y, k, j As IntegerDim aMDC() As SingleDim aDB_name(), aMDC_name() As String

 Application.Calculation = xlManualApplication.Cursor = xlWait

 y = 1

 ReDim aMDC_name(1)
'Creating database name' Name of measurement

 Do Until Sheets("PC").Cells(9, 5 + y) = ""
ReDim Preserve aMDC_name(y + 2)
aMDC_name(y + 2) = Sheets("PC").Cells(9, 5 + y).Value

 y = y + 1Loop

' Deleting old valuesRange(Sheets("results").Cells(3, 7), Sheets("results").Cells(4, 300)).Value = ""
Range(Sheets("results").Cells(5, 7), Sheets("results").Cells(2000, 300)).clearRange(Sheets("results").Cells(5, 2), Sheets("results").Cells(2000, 6)).Value = ""
Range(Sheets("average").Cells(3, 7), Sheets("average").Cells(2000, 300)).Value = ""
Range(Sheets("average").Cells(5, 2), Sheets("average").Cells(2000, 6)).Value = ""
Range(Sheets("deviation").Cells(3, 7), Sheets("deviation").Cells(2000, 300)).Value = ""
Range(Sheets("deviation").Cells(5, 2), Sheets("deviation").Cells(1500, 6)).Value = ""

k = 0 ' Loading of individual lists

Do Until Sheets("support").Cells(4 + k, 2).Value = ""
list = Sheets("support").Cells(4 + k, 2).Valuelist = Replace(list, ",", ".")
r_list = Sheets("support").Cells(4 + k, 3).Valuex = 1 
ReDim aDB_name(1)
'Creating database nameDo Until Sheets(list).Cells(9 + x, 2) = ""

ReDim Preserve aDB_name(x)
aDB_name(x) = Sheets(list).Cells(9 + x, 2).Valuex = x + 1

 Loop

y = 1ReDim aMDC(UBound(aDB_name, 1), UBound(aMDC_name, 1))
'Creating of all database valuesFor x = 1 To UBound(aDB_name, 1)

aMDC(x, 1) = Sheets(list).Cells(9 + x, 1).ValueaMDC(x, 2) = Sheets(list).Cells(2 + Sheets(list).Cells(9 + x, 5).Value, 6).ValueFor y = 3 To UBound(aMDC_name, 1)
aMDC(x, y) = Sheets(list).Cells(9 + x, 3 + y).ValueNext y

Next x

 ' Calculation of means and average deviationsDim number_measuring, number_average, l, i As IntegerDim aMDC_ave(), aMDC_Dev() As SingleDim aMDC_ave_name() As StringDim aMDC_cal() As Single

 y = 3number_measuring = Sheets("support").Cells(1, 8).Value ' Number of replicatesnumber_average = Application.WorksheetFunction.RoundDown((UBound(aMDC_name, 1) - 2) / number_me

asuring, 0) ' number of calculation for means and standard deviationsIf number_average < 1 Then number_average = 1ReDim aMDC_ave(UBound(aDB_name, 1), number_average)
ReDim aMDC_Dev(UBound(aDB_name, 1), number_average)
ReDim aMDC_ave_name(2, number_average) 


Module3 - 2

 For j = 1 To number_averageaMDC_ave_name(1, j) = aMDC_name(y) ' sample name transferaMDC_ave_name(2, j) = aMDC_name(y) ' sample name transfer

For x = 1 To UBound(aDB_name, 1)
ReDim aMDC_cal(1) ' Auxiliary points for calculations' Creating parameters for auxiliary points

l = 1 
For i = 1 To number_measuringaMDC_cal(1) = aMDC(x, y + i - 1)
If aMDC_cal(1) > 0 Then

ReDim Preserve aMDC_cal(l + 1)
aMDC_cal(l + 1) = aMDC_cal(1)
l = l + 1

 End If 
Next i

 Select Case l 
Case 1 
aMDC_ave(x, j) = aMDC(x, 2) ' the limit of detection 
aMDC_Dev(x, j) = 0Case 2 
aMDC_ave(x, j) = aMDC(x, y)
aMDC_Dev(x, j) = 0Case 3 
aMDC_ave(x, j) = Application.WorksheetFunction.Average(aMDC_cal(2), aMDC_cal(3))
aMDC_Dev(x, j) = Application.WorksheetFunction.StDev_P(aMDC_cal(2), aMDC_cal(3))
Case 4 
aMDC_ave(x, j) = Application.WorksheetFunction.Average(aMDC_cal(2), aMDC_cal(3), aMDC_cal(4

)) 
aMDC_Dev(x, j) = Application.WorksheetFunction.StDev_P(aMDC_cal(2), aMDC_cal(3), aMDC_cal(4))

 Case 5 
aMDC_ave(x, j) = Application.WorksheetFunction.Average(aMDC_cal(2), aMDC_cal(3), aMDC_cal(4), aMDC_cal(5))
aMDC_Dev(x, j) = Application.WorksheetFunction.StDev(aMDC_cal(2), aMDC_cal(3), aMDC_cal(4),

aMDC_cal(5))
Case 6 
aMDC_ave(x, j) = Application.WorksheetFunction.Average(aMDC_cal(2), aMDC_cal(3), aMDC_cal(4

), aMDC_cal(5), aMDC_cal(6))
aMDC_Dev(x, j) = Application.WorksheetFunction.StDev(aMDC_cal(2), aMDC_cal(3), aMDC_cal(4),
aMDC_cal(5), aMDC_cal(6))

 End Select

 If aMDC_ave(x, j) < aMDC(x, 2) Then aMDC_ave(x, j) = aMDC(x, 2)

 Next x

 y = y + number_measuringNext jl = 0

 'Display the result sheet

 For y = 3 To UBound(aMDC_name, 1)

 Sheets("results").Cells(3, y + 4).Value = aMDC_name(y)

Sheets("results").Cells(4, y + 4).Value = aMDC_name(y)
If l = number_measuring Then l = 0l = l + 1

 Next y

 For x = 1 To UBound(aDB_name, 1)

 Sheets("results").Cells(x + r_list, 4).Value = listSheets("results").Cells(x + r_list, 5).Value = aMDC(x, 1)
Sheets("results").Cells(x + r_list, 6).Value = aDB_name(x)
'Sheets("results").Cells(x + r_list, 6).Select

For y = 3 To UBound(aMDC_name, 1)
If aMDC(x, y) <> 0 ThenSheets("results").Cells(x + r_list, y + 4).Value = aMDC(x, y)
Sheets("results").Cells(x + r_list, y + 4).NumberFormat = "0.00"
Else 
Sheets("results").Cells(x + r_list, 3).Value = Sheets("results").Cells(x + r_list, 3).Value 


Module3 - 3

 + 1 
End If 
If l = number_measuring Then Sheets("results").Cells(x + r_list, y + 4).Borders(xlEdgeRight

).LineStyle = xlContinuousNext yNext x

 'Display means and average deviationsSheets("average").Select

For j = 1 To UBound(aMDC_ave, 2)
Sheets("average").Cells(4, j + 6).Value = aMDC_ave_name(1, j)
Sheets("average").Cells(3, j + 6).Value = aMDC_ave_name(2, j)
Sheets("deviation").Cells(4, j + 6).Value = aMDC_ave_name(1, j)
Sheets("deviation").Cells(3, j + 6).Value = aMDC_ave_name(2, j)
Next j

 For x = 1 To UBound(aDB_name, 1)
Sheets("average").Cells(x + r_list, 4).Value = listSheets("average").Cells(x + r_list, 5).Value = aMDC(x, 1)
Sheets("average").Cells(x + r_list, 6).Value = aDB_name(x)
Sheets("average").Cells(x + r_list, 6).SelectSheets("deviation").Cells(x + r_list, 4).Value = listSheets("deviation").Cells(x + r_list, 5).Value = aMDC(x, 1)
Sheets("deviation").Cells(x + r_list, 6).Value = aDB_name(x)

 For j = 1 To UBound(aMDC_ave, 2)
Sheets("average").Cells(x + r_list, j + 6).Value = aMDC_ave(x, j)
If aMDC_ave(x, j) > 0.001 Then

Sheets("average").Cells(x + r_list, j + 6).NumberFormat = "0.00"
Else: Sheets("average").Cells(x + r_list, j + 6).NumberFormat = "General"
End If

 If aMDC_Dev(x, j) <> 0 Then Sheets("deviation").Cells(x + r_list, j + 6).Value = aMDC_Dev(x, j) 
Sheets("deviation").Cells(x + r_list, j + 6).NumberFormat = "0.00"
Next jNext x 

k = k + 1 

LoopApplication.Calculation = xlAutomaticApplication.Cursor = xlDefaultSheets("results").SelectSheets("results").Cells(1, 6).SelectEnd Sub