-
Notifications
You must be signed in to change notification settings - Fork 0
/
MSD_4.vb
72 lines (65 loc) · 3.22 KB
/
MSD_4.vb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
Sub MSD_4()
Dim track As Double
Dim row As Double
Dim frame As Double
'frames = # of frames per track
frames = Worksheets("Zwischenwerte").Cells(5, 11).Value
'tracks = # of tracks
tracks = Worksheets("Zwischenwerte").Cells(3, 11).Value
Worksheets("Zwischenwerte_4").Cells.Delete
For track = 1 To tracks
For frame = 1 To frames
'calculate rownumbers for x and y @ timepoint/frame t0 (termed rownumt0)
rownumt0 = frames * (track - 1) + 1 + 1
'calculate rownumber for x and y @ a given timepoint/frame t (termed rownumt)
rownumt = frames * (track - 1) + frame + 1
'get values of x and y @ t0 and t
x0 = Worksheets("MSD_4").Cells(rownumt0, 2).Value
xt = Worksheets("MSD_4").Cells(rownumt, 2).Value
y0 = Worksheets("MSD_4").Cells(rownumt0, 3).Value
yt = Worksheets("MSD_4").Cells(rownumt, 3).Value
'calculate (xt-x0)^2 and (yt-y0)^2
Worksheets("Zwischenwerte_4").Cells(rownumt, 2) = (xt - x0) ^ 2
Worksheets("Zwischenwerte_4").Cells(rownumt, 3) = (yt - y0) ^ 2
'add column labels and time
Worksheets("Zwischenwerte_4").Cells(1, 1) = "t"
Worksheets("Zwischenwerte_4").Cells(1, 2) = "x^2 [µm^2]"
Worksheets("Zwischenwerte_4").Cells(1, 3) = "y^2 [µm^2]"
Worksheets("Zwischenwerte_4").Cells(1, 4) = "x^2 + y^2"
Worksheets("Zwischenwerte_4").Cells(1, 5) = "MSD [µm^2]"
Worksheets("Zwischenwerte_4").Cells(rownumt, 1) = Worksheets("MSD_4").Cells(rownumt, 1).Value
'calculate sum of (xt-x0)^2 and (yt-y0)^2 @ timepoint t
Worksheets("Zwischenwerte_4").Cells(rownumt, 4) = (xt - x0) ^ 2 + (yt - y0) ^ 2
'calculate MSD for given cell @ given timepoint
Sum = WorksheetFunction.Sum(Range(Worksheets("Zwischenwerte_4").Cells(frames * (track - 1) + 1 + 1, 4), Worksheets("Zwischenwerte_4").Cells(rownumt, 4)))
MSD = Sum / frame
Worksheets("Zwischenwerte_4").Cells(rownumt, 5) = MSD
Next frame
Next track
'sort calculated MSD by time
For frame = 1 To frames
Worksheets("Zwischenwerte_4").Cells(1, frame + 6) = frame
For track = 1 To tracks
Worksheets("Zwischenwerte_4").Cells(track + 1, frame + 6) = Worksheets("Zwischenwerte_4").Cells(frames * (track - 1) + frame - 1 + 2, 5)
Next track
Next frame
For frame = 1 To frames
'calculate average and sd of MSD at each timepoint
mean = WorksheetFunction.Average(Range(Worksheets("Zwischenwerte_4").Cells(2, frame + 6), Worksheets("Zwischenwerte_4").Cells(tracks + 1, frame + 6)))
sd = WorksheetFunction.StDev(Range(Worksheets("Zwischenwerte_4").Cells(2, frame + 6), Worksheets("Zwischenwerte_4").Cells(tracks + 1, frame + 6)))
'round mean
mean_r = Round(mean, 3)
'calculate and round S.E.M.
sem = sd / (tracks ^ (1 / 2))
sem_r = Round(sem, 3)
'add time and data to MSD worksheet
timeint = Worksheets("Zwischenwerte").Cells(4, 11)
Worksheets("MSD_4").Cells(frame + 1, 7) = timeint * (frame - 1)
Worksheets("MSD_4").Cells(frame + 1, 8) = mean_r
Worksheets("MSD_4").Cells(frame + 1, 9) = sem_r
'add column labels to MSD worksheet
Worksheets("MSD_4").Cells(1, 7) = "t [ ]"
Worksheets("MSD_4").Cells(1, 8) = "MSD"
Worksheets("MSD_4").Cells(1, 9) = "S.E.M."
Next frame
End Sub