Returning values from array separately

Question

I am looking for a way to return the type of bill rate with the highest reimbursement rate.

Of the three variables that I am searching against, I'd like to store the name of the variable returned to the table so that users can determine which billing type was used.

This code pulls the billing rates from the database.

CenterAllRate = DLookup("CenterBillRateAllVendors", "Centers", "CenterID = " & Chr(34) & CenterID & Chr(34))
CenterHospitalRate = DLookup("CenterBillRateHospitalOnly", "Centers", "CenterID = " & Chr(34) & CenterID & Chr(34))
VendorRate = DLookup("VendorReimbRate", "Vendors", "VendorID = " & Chr(34) & Me.Parent.VendorID & Chr(34))
ReimbursementRate = MaxOfList(CenterAllRate, CenterHospitalRate, VendorRate)

I am using this prebuilt function that I found online to find the highest reimbursement rate of the three options.

Function MaxOfList(ParamArray varValues()) As Variant
    Dim i As Integer        'Loop controller.
    Dim varMax As Variant   'Largest value found so far.

    varMax = Null           'Initialize to null

    For i = LBound(varValues) To UBound(varValues)
        If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
            If varMax >= varValues(i) Then
                'do nothing
            Else
                varMax = varValues(i)
            End If
        End If
    Next

    MaxOfList = varMax
End Function

How can I return either the name of the variable with the highest value, or give them aliases to use when creating the record?

Kostas K. · Accepted Answer · 2018-02-23 23:59:30Z

1

Since you are a coding newbie, it's a good opportunity to dig a bit deeper. :)

I would create an object to wrap the Name and Rate properties and pass this to my function.

An example:

A simple Reimbursement Class:

Option Explicit

Private name_ As String
Private rate_ As Variant

Public Property Get Name() As String
    Name = name_
End Property
Public Property Let Name(Value As String)
    name_ = Value
End Property

Public Property Get Rate() As Variant
    Rate = rate_
End Property
Public Property Let Rate(Value As Variant)
    rate_ = Value
End Property

Your method with a few minor changes:

Function MaxOfList(varValues As Variant) As Reimbursement
    Dim i As Integer                    'Loop controller.
    Dim varMax As Reimbursement         'Largest value found so far.
    Set varMax = New Reimbursement

    For i = LBound(varValues) To UBound(varValues)
        If IsNumeric(varValues(i).Rate) Or IsDate(varValues(i).Rate) Then
            If varMax.Rate < varValues(i).Rate Then
                With varMax
                    .Name = varValues(i).Name
                    .Rate = varValues(i).Rate
                End With
            End If
        End If
    Next

    Set MaxOfList = varMax
End Function

Lastly, to call and test it:

Sub T()

    Dim centerAll As New Reimbursement
    Dim centerHospital As New Reimbursement
    Dim vendor As New Reimbursement
    Dim maxReimbursement As New Reimbursement

    With centerAll
        .Name = "centerAll"
        '.Rate = DLookup("CenterBillRateAllVendors", "Centers", "CenterID = " & Chr(34) & CenterID & Chr(34))
        .Rate = 3
    End With
    With centerHospital
        .Name = "centerHospital"
        '.Rate = DLookup("CenterBillRateHospitalOnly", "Centers", "CenterID = " & Chr(34) & CenterID & Chr(34))
        .Rate = 8
    End With
    With vendor
        .Name = "vendor"
        '.Rate = DLookup("VendorReimbRate", "Vendors", "VendorID = " & Chr(34) & Me.Parent.VendorID & Chr(34))
        .Rate = 5
    End With

    Set maxReimbursement = MaxOfList(Array(centerAll, centerHospital, vendor))

    Debug.Print maxReimbursement.Name, maxReimbursement.Rate
End Sub

'Output
'centerHospital           8

answered Feb 23, 2018 at 23:59

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonny Thomas Over a year ago

Thank you! This looks like a good solution. I found from other sources that I would likely need to create a class, but wasn't sure how to do so. I'll give it a shot and reply back with my results

Jonny Thomas Over a year ago

Worked perfectly. Thanks again!

Foxfire And Burns And Burns · Accepted Answer · 2018-02-23 23:15:53Z

Welcome to SO.

I designed a poor solution that may work fork you, but it works under some specific conditions:

You must call always your function using same order as your example, this means you must invoke always typing MaxOfList(CenterAllRate, CenterHospitalRate, VendorRate)
My solution just stores in a variable the name of your variable used when you call your function. If you use it with another variables, it will not update. This means that, if your example, you invoke the function with other variables, like MaxOfList(AnotherAllRate, DifferentAllRate, ExampleRate) my solution won't return the right name. It just works with CenterAllRate, CenterHospitalRate, VendorRate

Ok, after this here is my poor solution. I declared a Public String var to store the name of the Highest of the list called HighestVar. This var updates every time you call function MaxofList

Option Explicit
Public HighestVar As String 'We declare the public Var to store name of max rate

Function MaxOfList(ParamArray varValues()) As Variant
Dim i As Integer        'Loop controller.
Dim varMax As Variant   'Largest value found so far.
HighestVar = "" 'We Reset the var

varMax = Null           'Initialize to null

For i = LBound(varValues) To UBound(varValues)
    If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
        If varMax >= varValues(i) Then
            'do nothing
        Else
            varMax = varValues(i)
            Select Case i 'We use select Case to store in HighestVar the name of highest rate
                Case 0
                    HighestVar = "CenterAllRate"
                Case 1
                    HighestVar = "CenterHospitalRate"
                Case 2
                    HighestVar = "VendorRate"
            End Select
        End If
    End If
Next

MaxOfList = varMax
End Function

Every time you call function MaxofList then you'll have in var HighestVar the name of the highest rate. Just use it where you want it. For example:

CenterAllRate = DLookup("CenterBillRateAllVendors", "Centers", "CenterID = " & Chr(34) & CenterID & Chr(34))
CenterHospitalRate = DLookup("CenterBillRateHospitalOnly", "Centers", "CenterID = " & Chr(34) & CenterID & Chr(34))
VendorRate = DLookup("VendorReimbRate", "Vendors", "VendorID = " & Chr(34) & Me.Parent.VendorID & Chr(34))
ReimbursementRate = MaxOfList(CenterAllRate, CenterHospitalRate, VendorRate)

Msgbox HighestVar & " is the highest rate" 'We show in Msgbox highest rate

Hope this can help you. Let me know.

Collectives™ on Stack Overflow

Returning values from array separately

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related