InputBox函数和MsgBox函数综合示例

程序说明：
在工作表中选择一列数据，然后选取列表中选区的任一单元格后，消息对话框显示出该单元格数值在选区中的排序位置。按输入框提示进行操作，默认为当前选中的列。

程序清单如下：
Option Explicit ‘进行变量声明
Dim MyCell As Range
Dim r As Integer
Dim MyRange As Range
Dim Ans

Sub rankalist()
  Dim m As Integer
  Set MyRange = Selection
 
  On Error Resume Next

  m = Selection.Count
  MsgBox "Selection has " & m & " cells.", vbInformation, "Selection Count"

  Call rankprocess  ‘调用子过程
 
  While Ans = vbYes
    Call rankprocess
  Wend
 
  While Ans = vbNo
    Exit Sub
  Wend
End Sub

Sub rankprocess()
  Set MyCell = Application.InputBox(prompt:="Please select a cell:", Title:="Cell", Type:=8) ‘用输入框返回一个单元格对象给MyCell对象变量
 
  If Union(MyCell, MyRange).Address = MyRange.Address Then  ‘判断单元格是否在选区内
    r = 1 + MyRange.Cells.Count - Application.WorksheetFunction.rank(MyCell.Value, MyRange, 0) ‘使用Excel的rank函数进行排序
    Ans = MsgBox("  the present cell is ranked " & r & " in the list " & vbNewLine & "Continue?", vbYesNo) ‘显示排序结果并询问是否继续查看其它单元格排序，还是退出
  Else
    MsgBox "Please select a cell in selection."
  End If
End Sub
UploadFiles/2006-5/516295031.rar