ten year atherosclerotic cardiovascular disease (ASCVD) risk calculator spreadsheet custom function
categories:
Spreadsheet
Cardiovascular
This is an important custom function. It's the 10-year atherosclerotic cardiovascular disease (ASCVD) risk calculator.
The [Frankenstein spreadsheet](/frankenstein) 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](https://support.microsoft.com/en-us/office/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f).
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](https://support.microsoft.com/en-us/office/find-help-on-using-the-visual-basic-editor-61404b99-84af-4aa3-b1ca-465bc4f45432)
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](https://stackoverflow.com/questions/4262421/how-to-put-a-tooltip-on-a-user-defined-function)
## 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.](https://pubmed.ncbi.nlm.nih.gov/24239921/)
[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.](https://pubmed.ncbi.nlm.nih.gov/16461820/)
You can find this in lots of places. There's an official one at [American College of Cardiology](https://tools.acc.org/ascvd-risk-estimator-plus/#!/calculate/estimate/). There's also a bunch of derivatives I made at [cliniborg.org](https://www.cliniborg.org/tag/ascvd/). But at work you may be using an excel file for this type of stuff.
So today I updated [the Frankenstein spreadsheet](/frankenstein) 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!