# Settings limits in VBA (1 Viewer)

#### Kuleesha

##### Member
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.

Kuleesha

#### Gasman

##### Enthusiastic Amateur
So test the various items first, amend if needed.
Then calculate
Then test limit for MELD and amend if needed.

#### June7

##### AWF VIP
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:

#### Gasman

##### CID VIP
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!
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
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
Thank you all for your help. Will work on it according to suggestions and try.

#### Pat Hartman

##### Super Moderator
Staff member
In addition, when you define variables, you should include their type rather than leaving them as variants.