Settings limits in VBA (1 Viewer)

Kuleesha

Member
Local time
Tomorrow, 00:34
Joined
Jul 18, 2021
Messages
50
Hi,
I'm new to vba on access.

I'm trying to set up a calculation for a score called MELD score where MELD Score = (0.957 * ln(Creatinine) + 0.378 * ln(Bilirubin) + 1.120 * ln(INR) + 0.643 ) * 10

I've set up the coding on a command button (MELD calc) as follows:

Private Sub Meld_calc_Click()
'Stop


Dim LnCrea
LnCrea = Log(Me.Creatinine)
Debug.Print LnCrea

Dim LnTbil
LnTbil = Log(Me.Bilirubin)
Debug.Print LnTbil

Dim LnINR
LnINR = Log(Me.INR)
Debug.Print LnINR


Me.MELD = ((0.957 * LnCrea) + (0.378 * LnTbil) + (1.12 * LnINR) + 0.643) * 10

Debug.Print Me.MELD

End Sub

I get good results within a certain range of values with this but there are several problems:
1. There is a cap for Creatinine value used in the MELD score (capped at 4)
2. The total for MELD is capped at 40
3. Lower limit for each of the 3 components are set at 1

I am unable to figure out the additional coding needed to get the above requirements fulfilled

I would be extremely grateful for any suggestions.

Thanks in advance

Kuleesha
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:04
Joined
Sep 21, 2011
Messages
14,046
So test the various items first, amend if needed.
Then calculate
Then test limit for MELD and amend if needed.
 

June7

AWF VIP
Local time
Today, 11:04
Joined
Mar 9, 2014
Messages
5,423
Is In() a custom VBA function? What exactly do you need? If Creatinine value is greater than 4, then use 4 in calculation? Similarly for MELD? And Also test for lower limit? Use IIf() or If Then Else conditionals to set variables.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 19:04
Joined
Jan 14, 2017
Messages
18,186
You could use IIf expressions to set the limits in each case.
For example, replace LnCrea with IIf(LnCrea>4,4,LnCrea).
Similarly for each of the other limits
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:04
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub Meld_calc_Click()
    'Stop
    Const MELD_CAP  As Integer = 40
    Const CREATINE_MAX As Integer = 4
    Const OTHER_COMPO_LOWER_LIMIT As Integer = 1
    
    Dim LnCrea
    Me.Creatine = fnMin(Nz(Me.Creatine, 0), CREATINE_MAX)
    LnCrea = Log(Me.Creatinine)
    Debug.Print LnCrea
    
    Dim LnTbil
    Me.Bilirubin = fnMax(Nz(Me!Bilirubin, 0), OTHER_COMPO_LOWER_LIMIT)
    LnTbil = Log(Me.Bilirubin)
    Debug.Print LnTbil
    
    Dim LnINR
    Me.INR = fnMax(Nz(Me!INR, 0), OTHER_COMPO_LOWER_LIMIT)
    LnINR = Log(Me.INR)
    Debug.Print LnINR
    
    
    Me.MELD = fnMin(((0.957 * LnCrea) + (0.378 * LnTbil) + (1.12 * LnINR) + 0.643) * 10, CEATINE_MAX)
    
    Debug.Print Me.MELD

End Sub

Public Function fnMin(ParamArray p() As Variant) As Variant
    Dim i As Integer
    Dim vMin As Variant
    vMin = p(i)
    For i = 1 To UBound(p)
        If p(i) < vMin Then
            vMin = p(i)
        End If
    Next
    fnMin = vMin
End Function

Public Function fnMax(ParamArray p() As Variant) As Variant
    Dim i As Integer
    Dim vMax As Variant
    vMax = p(i)
    For i = 1 To UBound(p)
        If p(i) > vMax Then
            vMax = p(i)
        End If
    Next
    fnMax = vMax
End Function
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:04
Joined
Feb 28, 2001
Messages
26,999
Is In() a custom VBA function? What exactly do you need? If Creatinine value is greater than 4, then use 4 in calculation? Similarly for MELD? And Also test for lower limit? Use IIf() conditionals to set variables.

Actually, I just tested it ... the LOG function in VBA is the natural logarithm, which in some languages is written as LN to differentiate it from the common logarithm written as LOG. Of course, if you need to use common log, you can use LOG(x)/LOG(10) which will convert the base for you.

The Excel library contains a LOG10() function, which is the other way that some languages differentiate between common and natural logarithms.

The Access VBA library does not appear to contain a separate function for common logarithms. Since a simple division is all you need, perhaps they decided to leave it to us to write the function in question if we needed it.
 

Kuleesha

Member
Local time
Tomorrow, 00:34
Joined
Jul 18, 2021
Messages
50
Thank you all for your help. Will work on it according to suggestions and try.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:04
Joined
Feb 19, 2002
Messages
42,971
In addition, when you define variables, you should include their type rather than leaving them as variants.
 

Users who are viewing this thread

Top Bottom