lifetime atherosclerotic cardiovascular disease (ASCVD) risk calculator spreadsheet custom function

•

categories: spreadsheet   cardiology

Continuing on with ASCVD risk calculators. This is the lifetime 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

Lifetime (30-year) ASCVD Risk Calculator

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 LIFETIMEASCVDRISK(gender, age, tobacco, diabetes, total_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 LCase(gender) = "male" then
		gender = 1000
	Elseif LCase(gender) = "female" then
		gender = 0
	End if
	
    If age >= 20 And age <= 59 Then
        GoTo Line2
    Else
        MsgBox "age needs to be between 20 and 59"
        GoTo LineZilch
    End If

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

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

    If total_cholesterol > 0 Then
        GoTo Line5
    Else
        MsgBox "total_cholesterol needs to be above 0"
        GoTo LineZilch
    End If
    
Line5:
	If total_cholesterol >= 240 Then
		Goto Line6
	Elseif total_cholesterol >= 200 then
		Goto Line7
	Elseif total_cholesterol >= 180 then
		Goto Line8
	Else
		Goto Line9
	End if
	
Line6:
	total_cholesterol = 100
	goto Line10

Line7:
	total_cholesterol = 10
	goto Line10

Line8:
	total_cholesterol = 1
	goto Line10

Line9:
	total_cholesterol = 0
	goto Line10
	
Line10:
    If systolic_blood_pressure > 0 Then
        GoTo Line11
    Else
        MsgBox "systolic_blood_pressure needs to be above 0"
        GoTo LineZilch
    End If

Line11:
	If systolic_blood_pressure >= 160 then
		Goto Line12
	Elseif systolic_blood_pressure >= 140 then
		Goto Line13
	Elseif systolic_blood_pressure >= 120 then
		Goto Line14
	Else
		Goto Line15
	End if
	
Line12:
	systolic_blood_pressure = 100
	Goto Line16

Line13:
	systolic_blood_pressure = 10
	Goto Line16
	
Line14:
	systolic_blood_pressure = 1
	Goto Line16
	
Line15:
	systolic_blood_pressure = 0
	Goto Line16
	
Line16:
	If (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 1199 then
		LIFETIMEASCVDRISK = "2 or more MAJOR risk factors - 68.9%"
	
	Elseif (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 1099 then
		LIFETIMEASCVDRISK = "1 MAJOR risk factor - 50.4%"
	
	Elseif (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 1009 then
		LIFETIMEASCVDRISK = "1 or more ELEVATED risk factors - 45.5%"

	Elseif (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 1000 then
		LIFETIMEASCVDRISK = "1 or more NOT OPTIMAL risk factors - 36.4%"
	
	Elseif (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 999 then
		LIFETIMEASCVDRISK = "All OPTIMAL risk factors - 5.2%"

	Elseif (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 199 then
		LIFETIMEASCVDRISK = "2 or more MAJOR risk factors - 50.2%"
	
	Elseif (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 99 then
		LIFETIMEASCVDRISK = "1 MAJOR risk factor - 38.8%"
	
	Elseif (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 9 then
		LIFETIMEASCVDRISK = "1 or more ELEVATED risk factors - 39.1%"

	Elseif (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 0 then
		LIFETIMEASCVDRISK = "1 or more NOT OPTIMAL risk factors - 26.9%"	
	Else
		LIFETIMEASCVDRISK = "All OPTIMAL risk factors - 8.2%"
	End if

LineZilch:
'nothing happens here

End Function

Thanks for reading!