categories: spreadsheet   cardiovascular

This is an important custom function. It’s the 10-year atherosclerotic cardiovascular disease (ASCVD) risk calculator.

The Frankenstein spreadsheet had this originally as a cell-based calculation, but it seems better to share Visual Basic for Applications (VBA) custom functions, at least in regard to sharing and updating the formulas.

As I mentioned in a previous post, there is more information about Excel custom functions at this Microsoft support page.

To make a custom function you’ll need to open the visual basic editor in your excel program and then enter and save the code. More information about doing this is at this Microsoft support page

And again, standard Excel functions will guide you as you try to select the cells for the calculation. The bummer about custom Excel functions is that there is no automatically displaying guide. You can make one appear (in Windows) by pressing CTRL-A. More on this is at this stackoverflow page

10 Year ASCVD Risk Calculator

This calculator has itself survived for over 10 years.

Here are the references:
Goff DC Jr, Lloyd-Jones DM, Bennett G, et al. 2013 ACC/AHA Guideline on the Assessment of Cardiovascular Risk: A Report of the American College of Cardiology/American Heart Association Task Force on Practice Guidelines. J Am Coll Cardiol. 2013 Nov 12.
Lloyd-Jones DM, Leip EP, Larson MG, et al. Prediction of lifetime risk for cardiovascular disease by risk factor burden at 50 years of age. Circulation. 2006 Feb 14;113(6):791-8. Epub 2006 Feb 6.

You can find this in lots of places. There’s an official one at American College of Cardiology. There’s also a bunch of derivatives I made at soapnote.org. But at work you may be using an excel file for this type of stuff.

So today I updated the Frankenstein spreadsheet with the custom function listed below.

Function TENYEARASCVDRISK(gender, age, race, tobacco, diabetes, hypertension_medication, total_cholesterol, hdl_cholesterol, systolic_blood_pressure)

    If LCase(gender) = "male" Or LCase(gender) = "female" Then
        GoTo Line1
    Else
        MsgBox "gender needs to be Male or Female for this calculator"
        GoTo LineZilch
    End If

Line1:
    If age >= 40 And age <= 75 Then
        GoTo Line2
    Else
        MsgBox "age needs to be between 40 and 75"
        GoTo LineZilch
    End If

Line2:
    If LCase(race) = "white" Or LCase(race) = "african american" Then
        GoTo Line3
    Else
        MsgBox "race needs to be either White or African American for this calculator"
        GoTo LineZilch
    End If

Line3:
    If LCase(tobacco) = "yes" Or LCase(tobacco) = "no" Then
        GoTo Line4
    Else
        MsgBox "tobacco needs to be either Yes or No for this calculator"
        GoTo LineZilch
    End If
    
Line4:
    If LCase(tobacco) = "yes" Then
        tobacco = 1
    ElseIf LCase(tobacco) = "no" Then
        tobacco = 0
    End If

    If LCase(diabetes) = "yes" Or LCase(diabetes) = "no" Then
        GoTo Line5
    Else
        MsgBox "diabetes needs to be either Yes or No for this calculator"
        GoTo LineZilch
    End If
    
Line5:
    If LCase(diabetes) = "yes" Then
        diabetes = 1
    ElseIf LCase(diabetes) = "no" Then
        diabetes = 0
    End If
    
    If LCase(hypertension_medication) = "yes" Or LCase(hypertension_medication) = "no" Then
        GoTo Line6
    Else
        MsgBox "hypertension_medication needs to be either Yes or No for this calculator"
        GoTo LineZilch
    End If

Line6:
    If total_cholesterol > 0 Then
        GoTo Line7
    Else
        MsgBox "total_cholesterol needs to be above 0"
        GoTo LineZilch
    End If
    
Line7:
    If hdl_cholesterol > 0 Then
        GoTo Line8
    Else
        MsgBox "hdl_cholesterol needs to be above 0"
        GoTo LineZilch
    End If
    
Line8:
    If systolic_blood_pressure > 0 Then
        GoTo Line9
    Else
        MsgBox "systolic_blood_pressure needs to be above 0"
        GoTo LineZilch
    End If

Line9:
    If LCase(gender) = "female" And LCase(race) = "white" And LCase(hypertension_medication) = "no" Then
        TENYEARASCVDRISK = (1 - ((0.9665) ^ Exp(((-29.799 * Log(age)) + (4.884 * (Log(age)) ^ 2) + (13.54 * (Log(total_cholesterol))) + (-3.114 * Log(age) * Log(total_cholesterol)) + (-13.578 * Log(hdl_cholesterol)) + (3.149 * Log(age) * Log(hdl_cholesterol)) + (1.957 * Log(systolic_blood_pressure)) + (7.574 * tobacco) + (-1.665 * Log(age) * tobacco) + (0.661 * diabetes)) + 29.18)))
    
    ElseIf LCase(gender) = "female" And LCase(race) = "white" And LCase(hypertension_medication) = "yes" Then
        TENYEARASCVDRISK = (1 - ((0.9665) ^ Exp(((-29.799 * Log(age)) + (4.884 * (Log(age)) ^ 2) + (13.54 * Log(total_cholesterol)) + (-3.114 * Log(age) * Log(total_cholesterol)) + (-13.578 * Log(hdl_cholesterol)) + (3.149 * Log(age) * Log(hdl_cholesterol)) + (2.019 * Log(systolic_blood_pressure)) + (7.574 * tobacco) + (-1.665 * Log(age) * tobacco) + (0.661 * diabetes)) + 29.18)))

    ElseIf LCase(gender) = "female" And LCase(race) = "african american" And LCase(hypertension_medication) = "no" Then
        TENYEARASCVDRISK = (1 - ((0.9533) ^ Exp(((17.114 * Log(age)) + (0.94 * Log(total_cholesterol)) + (-18.92 * Log(hdl_cholesterol)) + (4.475 * Log(age) * Log(hdl_cholesterol)) + (27.82 * Log(systolic_blood_pressure)) + (-6.087 * Log(age) * Log(systolic_blood_pressure)) + (0.691 * tobacco) + (0.874 * diabetes)) - 86.61)))
    
    ElseIf LCase(gender) = "female" And LCase(race) = "african american" And LCase(hypertension_medication) = "yes" Then
        TENYEARASCVDRISK = (1 - ((0.9533) ^ Exp(((17.114 * Log(age)) + (0.94 * Log(total_cholesterol)) + (-18.92 * Log(hdl_cholesterol)) + (4.475 * Log(age) * Log(hdl_cholesterol)) + (29.291 * Log(systolic_blood_pressure)) + (-6.432 * Log(age) * Log(systolic_blood_pressure)) + (0.691 * tobacco) + (0.874 * diabetes)) - 86.61)))
        
    ElseIf LCase(gender) = "male" And LCase(race) = "white" And LCase(hypertension_medication) = "no" Then
        TENYEARASCVDRISK = (1 - ((0.9144) ^ Exp(((12.344 * Log(age)) + (11.853 * Log(total_cholesterol)) + (-2.664 * Log(age) * Log(total_cholesterol)) + (-7.99 * Log(hdl_cholesterol)) + (1.769 * Log(age) * Log(hdl_cholesterol)) + (1.764 * Log(systolic_blood_pressure)) + (7.837 * tobacco) + (-1.795 * Log(age) * tobacco) + (0.658 * diabetes)) - 61.18)))
        
    ElseIf LCase(gender) = "male" And LCase(race) = "white" And LCase(hypertension_medication) = "yes" Then
        TENYEARASCVDRISK = (1 - ((0.9144) ^ Exp(((12.344 * Log(age)) + (11.853 * Log(total_cholesterol)) + (-2.664 * Log(age) * Log(total_cholesterol)) + (-7.99 * Log(hdl_cholesterol)) + (1.769 * Log(age) * Log(hdl_cholesterol)) + (1.797 * Log(systolic_blood_pressure)) + (7.837 * tobacco) + (-1.795 * Log(age) * tobacco) + (0.658 * diabetes)) - 61.18)))

    ElseIf LCase(gender) = "male" And LCase(race) = "african american" And LCase(hypertension_medication) = "no" Then
        TENYEARASCVDRISK = (1 - ((0.8954) ^ Exp(((2.469 * Log(age)) + (0.302 * Log(total_cholesterol)) + (-0.307 * Log(hdl_cholesterol)) + (1.809 * Log(systolic_blood_pressure)) + (0.549 * tobacco) + (0.645 * diabetes)) - 19.54)))
            
    ElseIf LCase(gender) = "male" And LCase(race) = "african american" And LCase(hypertension_medication) = "yes" Then
        TENYEARASCVDRISK = (1 - ((0.8954) ^ Exp(((2.469 * Log(age)) + (0.302 * Log(total_cholesterol)) + (-0.307 * Log(hdl_cholesterol)) + (1.916 * Log(systolic_blood_pressure)) + (0.549 * tobacco) + (0.645 * diabetes)) - 19.54)))
    End If
LineZilch:
'nothing happens here

End Function

Thanks for reading!