Macros For Data Analysis in Excel

The macro below can be pasted directly into the Visual Basic Editor of Excel - any problems contact me - jbradley@gladstone.ucsf.edu

Trapezoid Method for Integrated Area                        Dissociation Curve Analysis

Trapezoid Method for Integrated Area

This macro was written to determine the approximate area and peak response of  the curve for agonist-induced Ca2+ responses.

Sub aaa_trapezoid()

'written 07-30-02
' by John Bradley

setpoint = 10 'this point is included in calculating the baseline not the integration

bline = 8  ' number of points in the baseline calculation

undercurve = 8  '  number of points to be used in trapezoid area calculation

output = 30  '  output row on sheet

numbcells = 5  '  number of columns across sheet to be calculated

For across = 1 To numbcells

    adder = 0: adder2 = 0

    sumx = 0: sumy = 0: sumprodxy = 0: sumxsquare = 0

    a = 0: b = 0: diff = 0: trap_area = 0: base_av = 0

    'regression fit for baseline

    For up = (setpoint - bline + 1) To setpoint

        sumy = sumy + ActiveCell.Cells(up, 1 + across)

        sumx = sumx + ActiveCell.Cells(up, 1)

        sumprodxy = sumprodxy + (ActiveCell.Cells(up, 1 + across) * ActiveCell.Cells(up, 1))

        sumxsquare = sumxsquare + (ActiveCell.Cells(up, 1) * ActiveCell.Cells(up, 1))

    Next up

    atop = (sumy * sumxsquare) - (sumx * sumprodxy)

    abot = (bline * sumxsquare) - (sumx * sumx)

    a = atop / abot

    btop = (bline * sumprodxy) - (sumx * sumy)

    bbot = (bline * sumxsquare) - (sumx * sumx)

    b = btop / bbot

   base_av = sumy / bline

 '    ActiveCell.Cells(39, 1) = sumx

 '   ActiveCell.Cells(40, 1) = sumy

 '       ActiveCell.Cells(41, 1) = sumprodxy

 '       ActiveCell.Cells(41, 3) = sumxsquare

  '  ActiveCell.Cells(42, 3) = a

  '  ActiveCell.Cells(43, 3) = b

    'calculate area under curve by subtracting expected baseline from actual value

    max_peak_ratio = 0

    For down = setpoint To setpoint + undercurve

  '      diff = ActiveCell.Cells(down, across + 1) - base

  '      adder = adder + diff

        peakratio = (ActiveCell.Cells(down, across + 1) / base_av)

        base = (b * ActiveCell.Cells(down, 1)) + a

        base2 = (b * ActiveCell.Cells(down + 1, 1)) + a

        leftside = ActiveCell.Cells(down, across + 1) - base

        rightside = ActiveCell.Cells(down + 1, across + 1) - base2

        trap_area = ((leftside + rightside) / 2) * (ActiveCell.Cells(down + 1, 1) - ActiveCell.Cells(down, 1))

        adder = adder + trap_area

    '    diff = ActiveCell.Cells(down, across + 1) - base

    '    adder2 = adder2 + diff

    If peakratio > max_peak_ratio Then

        max_peak_ratio = peakratio

    End If

    Next down

            ActiveCell.Cells(output, across + 1) = base_av

        ActiveCell.Cells(output + 1, across + 1) = adder

        ActiveCell.Cells(output + 2, across + 1) = max_peak_ratio

Next across

    ActiveCell.Cells(output, 1) = "average bseline"

        ActiveCell.Cells(output + 1, 1) = "trapezoid area"

        ActiveCell.Cells(output + 2, 1) = "peak response"

        ActiveCell.Cells(output + 4, 1) = "baseline number ="

        ActiveCell.Cells(output + 4, 3) = bline

        ActiveCell.Cells(output + 5, 1) = "undercurve number ="

        ActiveCell.Cells(output + 5, 3) = undercurve

End Sub

Dissociation Curve Analysis

This macro is used to sort out dissocitation curve data from the real-time pcr machine - following the dissociation curve analysis to check for primer dimers.

Sub dissociation_curve_analysis()

cwt = 0: wells = 0

y = 1

adder = -1

go = True

Dim lots(96) As Integer

Dim wellname(100) As Variant

Dim differential(100, 500) As String

Do Until cwt > 6

    a = ActiveCell.Cells(y, 1).Value

    sybr2 = ActiveCell.Cells(y + 2, 7).Value

    sybr = ActiveCell.Cells(y + 1, 7).Value

    timed2 = ActiveCell.Cells(y + 2, 2).Value

    timed = ActiveCell.Cells(y + 1, 2).Value

    If go = True Then

        adder = adder + 1

        differential(wells, adder) = ((sybr2 - sybr) / (timed2 - timed)) * -1

    End If

    If a = "" And go = True Then

        lots(wells) = adder - 1

    End If

    If a = "" Then

        cwt = cwt + 1

        go = False

        adder = 0

    End If

    If a = "Well" Then

     cwt = 0

     wells = wells + 1

     go = True

     wellname(wells) = ActiveCell.Cells(y + 1, 1).Value

    End If

    y = y + 1

'ActiveCell.Cells(y, 15).Value = adder

Loop

Sheets.Add

For toot = 1 To 96

ActiveCell.Cells(1, toot).Value = "Well " & wellname(toot)

    For hh = 1 To lots(toot)

        ActiveCell.Cells(hh + 3, toot).Value = differential(toot, hh)

    Next hh

Next toot

End Sub