编写返回客户实体类型的函数

我有一组数据。它包含客户名称和债务。如果债务超过250，则被认为是“沉重”，从200到249为“中等”，0-190为“轻”。

数据如下：

Name Debt
John $160
Alex $210
Mike $260


如果用户在InputBox中输入“ John”，我想编写一个在MsgBox中返回“ light”的函数。我为客户创建了一个课程。

到目前为止，我所拥有的如下：

Function ReadDebtType(amount As Integer) As Collection
InputBox ("Enter Customer Name (must exist)")
Dim Name As String
Name = InputBox.value
Dim rg As Range
Set rg = Worksheets("Data").Range("A3").CurrentRegion
Dim coll As New Collection
Dim c As CustomerPurchase
Dim DebtType As String
Dim i As Long,amount As Integer
For i = 4 To rg.Rows.Count
amount = rg.Cells(i,9)
If amount <"$200" Then
Set c = New CustomerPurchase
c.CustomerName = rg.Cells(i,1)
c.CustomerName = rg.Cells(i,2)
c.CustomerName = rg.Cells(i,3)
c.CustomerName = rg.Cells(i,4)
c.CustomerName = rg.Cells(i,5)
If pAmount <"$200" Then DebtType = "light"
ElseIf pAmount > "$249" Then DebtType = "heavy"
Else: DebtType = "medium"
End If
End Function


但是我很失落。任何帮助。此功能还需要在客户类模块中还是可以在普通模块中？

为什么要重新发明轮子？使用查找功能可以更有效地完成此操作。

=LOOKUP(B1,{0,200,250},{"light","medium","heavy"})


如果您需要VBA用户表单中的表单，则可以使用Application.WorksheetFunction.Lookup

这是使用@teylyn的响应的VBA实现。如果找不到人名，则会引发错误。假设您的数据在A和B列中。

Option Explicit
Public Sub CheckName()
Dim vName As String
Dim vSheet As Worksheet
Dim vDebtAmount As Double
Dim vDebtType As String
Dim vDebtLimit As Variant
Dim vDebtDesc As Variant
Set vSheet = Application.ActiveSheet
vDebtLimit = Array(0,250)
vDebtDesc = Array("Light","Medium","Heavy")
vName = Application.InputBox("Enter Name","Debt Assessment")
vDebtAmount = Application.WorksheetFunction.VLookup(vName,vSheet.Range("A:B"),2,False)
vDebtType = Application.WorksheetFunction.Lookup(vDebtAmount,vDebtLimit,vDebtDesc)
MsgBox vDebtType
End Sub